SQL Server Performance Forum – Threads Archive

ASP with SQL Server backend

I work for a small company and we have a web application that has been running well on a single server using ASP and SQL Server. We recently bought two server and split the Web server and SQL server to two different machines. It started out performing beautifully but now it is running way too slow. I have been researching all kinds of possibilities from network congestion between the servers to code changes in the ASP pages to see if I can determine the cause of the problems. I’m afraid I just no longer know what to check, what to monitor, who to ask, etc. If anyone could just point me in some directions that would be wonderful. Thanks.

Srart withhttp://www.sql-server-performance.com/sql_server_performance_audit.asp Has there been a significant change in size of database? Have you run the database maintainence tasks like reorganisation lately? Try using perfmon to analyze bottlenecks and capture sql trace and run index tuning wizard if it shows some recommendations. Gaurav

Have u changed any Fillfactor settings in the DB Maintenance plan by mistake. Because
this may effect ur performance. Sometimes missing statistics on a table also cause problem. For this u check with
Estimated execution plan in Sql query analyzer and take measures by recreating indexes
with update statistics. Also Execute the queries in the query analyzer and check the time , as well u notice
the time taking the same query by launching ASP page. If the query is executing
correctly in the sql server and if it taking time via ASP page then u check the
Server where IIS was installed. Thanks
Rushendra

A few questions for you: -Were the performance symptoms sudden or gradual?
-Is the performance issue intermittent (on/off) or permanent?
-How long after your hardware changes did the performance start deteriorating?
-What is ‘rate’ of deterioration ? Is it almost proportional to the database growth or is it time-based?
-Did the hardware specifications for the SQL Server machine change when you did your split?
-Any SQL Server configuration changes prior to this problem?
-What are the database and log file growth parameters ?
Logan: Take time, be systematic – start from the symptoms and move out slowly towards the differentials (they are many). Nathan H.O.

This web applications is used by several clients of ours to operating predominantly during normal business hours. That means that 8-5ish is heavy usage and after that is light usage. Some clients reported slow preformance almost immediately after the changeover to new hardware. Other clients started reporting things slowly over the next couple of weeks. A few questions for you: -Were the performance symptoms sudden or gradual? This web applications is used by several clients of ours to operating predominantly during normal business hours. That means that 8-5ish is heavy usage and after that is light usage. Some clients reported slow preformance almost immediately after the changeover to new hardware. Other clients started reporting things slowly over the next couple of weeks.
-Is the performance issue intermittent (on/off) or permanent? Now it seems that early in the morning, when few users have logged on, that performance is at least acceptable. Throughout the day it gets worse and worse. I’m watching various connection parameters to see if there is a problems with too many connections. A strange thing is that processor utilization on both machines is really low all of the time. Also, we are running crystal reports for reporting and it seems to run lightening fast at all times of the day while some ASP pages have the potential to time out. That should be a big indicator but I can’t seem to pinpoint the issue. I’m watching some other parameters for that issue. -What is ‘rate’ of deterioration ? Is it almost proportional to the database growth or is it time-based? Yes, it seems time-based in that in the afternoons, when more users are doing more stuff, that is when performance really slows. -Did the hardware specifications for the SQL Server machine change when you did your split? We went from one server hosting IIS and SQL Server and a couple of clients to two servers hosting about 8 clients. Each client has its own database. The old server was embarrasingly inadequate hardwarewise. The new servers both have dual Xeons (1.8 and 2.4), 1GB and 2GB RAM, and a gigabit connection between the two. Processors show very light usage and there is still plenty of memory free. I’m having a little trouble determining if there are any network bottlenecks but it doesn’t seem the case. Although, in the afternoons I sometimes having trouble connecting through Enterprise Manager from my office to the server at a colocation site. I have those servers on the same domain as our main office. Could there be issues with DNS or other domain related problems?
If anyone suspects that this is no longer a SQL Server issue is there some other place that I can look for solutions? Many thanks.

Can you explain what do you mean by "Each client has its own database?" I suspect this is a database contention issue. In this case we start having performance problems as the user load increases. An low CPU, Physical memory utilization indicate that the hardware does not seem to be a bottleneck. However ou haven’t mentioned about the Disk. How does the disk counters look? I would recommend running a trace on the database and look for locking issues. Gaurav

–Can you explain what do you mean by "Each client has its own database?" There is one SQL Server with only one instance. Each of our clients logs in through a web interface to their own database. XYZ Company has XYZ database and PQR has it own database PQR. But they are all on the same server and SQL instance. There is a Chief database that contains stored procs and common information that is used throughout all clients. It appears to me through testing that stored procs execute fine throughout the day. –ou haven’t mentioned about the Disk. How does the disk counters look? I’m looking into these. For the last few hours % disk time has average about 3.5 and avg disk queue length has been something like .03. But you mentioned locks and we have encountered times when users have gotten messages that there was a lock and their process has been selected for destruction.

Disk counters also look good. One more thing. What do you mean by "a gigabit connection between the two"? Is there any kind of communication like replication going on between the two? If CPU, mem and disk counters look goopd even during the peak load, then connecting to SQL server via enterprise manager can be due to slow network only. If the network is shared by the office users as well, there could be a case of Network Congestion due to other users starting some big downloads at afternoons. I still suspect there are locking issues on the server. IS there any other application on the server? What about the batch jobs. In addition to the OLTP application, what are the other activities on SQL Server? Gaurav

What do you mean by "a gigabit connection between the two"? These servers have two gigabit ethernet cards each. They are hosted at a colocation site away from our offices. Server A serves web pages. Server B is DB. Server A NIC 1 is connected to the world. Serv B NIC 1 is connected to the world but only used by us to do manual db work. These both operate at 100Mb connected to a switch.
ServA NIC 2 is connected directly to ServB NIC 2 with crossover cable and so this connection operates at 1 Gb speed. However, I still don’t think all traffic goes that route. Some may go through NIC 1 through the switch and then NIC 1 on Serv B. I want it to always go the NIC 2 route but don’t know how. Connections strings use IP addresses through OLEDB but lots of traffic still seems to want to go through the slower, less-direct route. Is there any kind of communication like replication going on between the two? No, Serv A runs website, ftp, smtp processes. Serv B runs SQL Server only and stores documents that users can upload. This document uploading, through SA-FileUp on Serv A should not be so much as to degrade performance.
If the network is shared by the office users as well, there could be a case of Network Congestion due to other users starting some big downloads at afternoons.
These servers are hosted so I assume I do share bandwidth with other servers. However, there has never been any problem in the past with bandwidth. And as mentioned before, even when some ASP pages are at a crawl I can run Crystal Report reports at blazing speeds. I still suspect there are locking issues on the server. I can’t dispute the locking possiblity. I do need to research it and understand what I can find and how I can fix it.
IS there any other application on the server? What about the batch jobs. In addition to the OLTP application, what are the other activities on SQL Server? For the most part Serv B is solely SQL Server. One job runs every hour and checks an ftp site for new files. A new file is present only once or twice a day and takes seconds to process. All other jobs run starting at 11:00 p.m. through 2:00 a.m. and to some routine maintenance. Other than that it just fulfills db requests from the front end server.
Thank you for all your time.
Cliff

For locking see this – http://www.sql-server-performance.com/reducing_locks.asp
http://www.sql-server-performance.com/sf_block_prevention.asp
http://www.sql-server-performance.com/lock_contention_tamed_article.asp HTH. Gaurav

One question you did not answer concerns the settingsn for the data and log file growth of the databases. Please check that also.
-Locking issues With the same code running before and after the hardware changeover locking issues would be consistent. Each user mainly uses their own database and does not interfere (SELECT,DELETE,INSERT,UPDATE) with objects in the other databases. The locking is a small matter and is not one of your main worries. -Hardware Your hardware looks fine and is performing almost as if there is no SQL Server activity. Forget about it for now. -Connectivity issues ? TCP/IP counters, % bandwidth utilization on network
? DNS/NetBIOS configuration – Remember broadcasts!
? Domain Controller (I hope the SQL Server is not the DC) issues
Nathan H.O.

Nathan, there has been increase in the no. of users and the application runfs fine in the morning when there are less users in the system. This is what led me to locking problem. Looking at Data and Log file is definately a good point. Gaurav

logan1998 – Check this article.http://www.devarticles.com/art/1/489
I think you owuld find some answers there. Bambola.

For this ASP-based app to cause such serious locks with only 8 or so clients then the architecture must be pretty shaky. In fact, unless the business logic mandates this, I think apart from solving any other problems related to the performance of the app Logan should seriously rethink a few design elements like the the necessity (justification) of multiple databases, the current T-SQL code within the Chief database,the ASP code accessing the SQL Server data etc. The problem to me now looks more and more likely to be MSSQL Server and less with the hardware itself or connectivity.
Nathan H.O.

If I am not mistaken, 8 Clients does not mean 8 Users. Each client will have multiple users and each client has his own database. I think the word client was taken in SQL Server terms which is actually a business client. Gaurav

Wow, thanks all for the great discussion starting. Thanks Bambola for the link. It was actually it one of the earlier posts and I have started doing the audit. Thus far the load on the SQL Server machine itself seems very minimal. The current connections is at about 70. Gaurav is right that we have about 10 business clients with each client having multiple users. Our old single server handled the load, just not in an acceptable manor. I have also been watching the front end and its load looks minimal. So I’m focusing on network issues now. I’m going to check some TCP counters and whatever else I may be able to find out. Nathan, no the SQL Server is NOT a domain controller. I’ve read about that bad move a lot. But I am concerned about DNS/NetBIOS and TCP/IP issues that I don’t have lots of knowledge about. I think I may have some consulting help coming in soon. On the data and log file growth issues. I have most set to automatically grow. Some of the lighter used databases are set by MB growth at a few MBs. Others are set at like 5 to 10% growth. I look for pointers on setting with those. Can anyone tell how new I am at this? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thank you all for your input.

Look for ALTER DATABASE startement in Books Online. Gaurav

…and of course on this site: http://www.sql-server-performance.com/database_settings.asp] Sorry for misreading the load/population issue (8 clients vs. 8 users). Nathan H.O.

Hi all. I have made a small observation that may mean this isn’t the right forum. As the Monday progressed things from the website frontend slowly got slower in responding. In the evening, after all users had logged off and noone was left using the system it still took 5-10 seconds for me to log in through the website. This action was taking milliseconds earlier in the day. the ASP Request Execution Time was enormous (many many seconds) for a single event. I restarted IIS and there was no effect. I restarted the whole computer and things seemed to speed up again. So, it seems that I’m having some kind of ASP problem. This is not to say that issues may not still exist in other areas and I will continue to explore all routes you have recommended. I thank you very much. I may still find that it is db related. But if anyone knows of some good ASP gurus that might be able to shed some light on the situation I be thankful for a push in the right direction. Cliff

I have always found the messageboard at http://www.4guysfromrolla.com/] useful for ASP stuff. Nathan H.O.

Another excelent asp site ishttp://www.learnasp.com/learn/. Full of examples and explenations as to why a certain approach is better than another. Bambola.

quote:
But you mentioned locks and we have encountered times when users have gotten messages that there was a lock and their process has been selected for destruction.

I’m back to the locks problems because I am under the impression that the problem is there. I have to admit that I’m not very familiar with hardware or network issues, so I might be wrong. What you are saying here is that there are deadlock problem. I would try to look into the queries that are causing the deadlock and re-writing them. You can find them using profiler.
Look for queries that access the same tables within a transaction but in a different order, and order them the same way
For example:
First query
begin transaction
update table A updae table B
commit transaction Second query
begin transaction
update table B updae table A
commit transaction
If you are still having deadlocks you can decide (if it within your requeriments) which process will be selected as deadlock victim.
Some other suggestions that will help you avoid other lock problem. Try to minimize what you are doing within a transaction so locks will be held for shorter time.
Whenever you can efford it, use WITH (NOLOCK) hint in SELECT statments.
When updating a record, consider using ROWLOCK hint. It will place a lock only on the updated row and not on PAGE/TABLE. Bambola.

I want to thank you all for your input. I learned quite a bit even though the suggestions were not solving the immediate problem. What has ultimately solved the issue right now is that the two servers in question were located away from our main office but were still within out domain. I believe that domain resolution requests or something about this setup was gradually causing the server to wait longer and longer while trying to resolve name information about other computers. I put them on their own workgroup and things have been running nicely ever since. I still continue to look into all of the suggestions that I got from here and appreciate the help. Cliff

I believe we mentioned something to do with DNS and the network infrastructure in your setup. Take your time and give us some feedback if something else turns out to be helpful (or not). Regards,
Nathan H.O.

]]>