phxvigo
asked on
SQL Server 2005 runs slow after a couple hours
We are having some trouble with our SQL server 2005 database. Here's the scenario...
We are running a .NET front and application which talks to the SQL customer tracking database. Both SQL server and the application run on the same box under Windows Server 2003 Standard. Hardware is a Dell 2950, single dual core CPU, 4gb ram, 4disk raid 1+0.
Here's the problem. Everything runs fine for 2-3 hours, moderate CPU usage, no memory paging, only occasional disk reads... Then performance drops off. Queries generate large disk reads, disk queues shoot up and everything grinds to a stop. Performance continues to drop off for another hour or so until finally users are getting timeout errors.
I jump into management studio and restart the SQL server, then everything runs fine for another 2-3 hours.
What is causing SQL server to suddenly need so many disk reads, and how do I resolve it?
According to perfmon, there is still memory free, and there is no paging going on, just large disk reads that are slowing everything down.
We are running a .NET front and application which talks to the SQL customer tracking database. Both SQL server and the application run on the same box under Windows Server 2003 Standard. Hardware is a Dell 2950, single dual core CPU, 4gb ram, 4disk raid 1+0.
Here's the problem. Everything runs fine for 2-3 hours, moderate CPU usage, no memory paging, only occasional disk reads... Then performance drops off. Queries generate large disk reads, disk queues shoot up and everything grinds to a stop. Performance continues to drop off for another hour or so until finally users are getting timeout errors.
I jump into management studio and restart the SQL server, then everything runs fine for another 2-3 hours.
What is causing SQL server to suddenly need so many disk reads, and how do I resolve it?
According to perfmon, there is still memory free, and there is no paging going on, just large disk reads that are slowing everything down.
SQL_SERVER_DBA
update service pack
ASKER
I went here: http://support.microsoft.com/kb/321185 and confirmed I am not running SP2.
I have downloaded the update. I will install it tonight and report back tomorrow.
Thanks.
I have downloaded the update. I will install it tonight and report back tomorrow.
Thanks.
Also try running the profiler with condition to check for the scripts which takes more than 5 min to complete. That way you can identify the scripts which are taking more time and then make sure that the script is not doing something unwanted as dumping the whole table to a temp table or so.
Run the profiler for a limited period only.
Run the profiler for a limited period only.
ASKER
I am now running version 9.00.3042.00 SP2 Standard Edition. Alas, the problem persists.
assyst: I have looked at the SQL Server Profiler a bit. I'm not sure how to set it to return queries that take over 5 minutes. But even so, I don't think there would be any as they would time out before that. There are a couple queries that take longer to run than others and they could no doubt be optimized more, but that doesn't directly address the issue. Even the longer queries will complete in a second or less when the server is running well under normal load. After the server decides to read everything from disk, they take in the area of 6-8 seconds (waiting on disk reads) when there is no one else on the server.
assyst: I have looked at the SQL Server Profiler a bit. I'm not sure how to set it to return queries that take over 5 minutes. But even so, I don't think there would be any as they would time out before that. There are a couple queries that take longer to run than others and they could no doubt be optimized more, but that doesn't directly address the issue. Even the longer queries will complete in a second or less when the server is running well under normal load. After the server decides to read everything from disk, they take in the area of 6-8 seconds (waiting on disk reads) when there is no one else on the server.
ASKER
I've also noticed... Once the server starts reading from disk, even after everyone goes home and I'm the only one left, it still does a big disk read when I hit the refresh button on the front end app. Presumably refreshing the same page, the query should be cached to memory... but it is still reading from disk.
There are several things that jump out at me. First, whenever possible run SQL Server on its' own box.
Second, check your queries to optimize performance. You want queries to SEEK not SCAN which is done via Indexing, and providing numerical search criteria as often as you can.
Have you set up Maintenance jobs?
Reducing fragmentation or in database "speak" lack of contiguous space allocation is done with the Maintenance Plans you set up.
These are vital DBA tasks.
Second, check your queries to optimize performance. You want queries to SEEK not SCAN which is done via Indexing, and providing numerical search criteria as often as you can.
Have you set up Maintenance jobs?
Reducing fragmentation or in database "speak" lack of contiguous space allocation is done with the Maintenance Plans you set up.
These are vital DBA tasks.
ASKER
I know for sure there are at least two queries that run frequently that have to scan the table to return results. I know I need to add some indexes to resolve that. The tuning advisor says something like 97% faster if I added the index.
However for the first two hours or so, those queries run plenty fast. There is little disk access. Later though, the same queries have to do the table scan from disk. So, what changes during those two hours? What resource is being exausted? Why doesn't the resource get released?
I have no Maintainence Plans set up. Is this something that would need to be run every couple hours or less? Can you elaborate or point to some information? I will research also.
However for the first two hours or so, those queries run plenty fast. There is little disk access. Later though, the same queries have to do the table scan from disk. So, what changes during those two hours? What resource is being exausted? Why doesn't the resource get released?
I have no Maintainence Plans set up. Is this something that would need to be run every couple hours or less? Can you elaborate or point to some information? I will research also.
Register and enjoy: http://www.sqlservercentral.com/articles/SQLServerCentral.com/2562/
This is what Microsoft recommends for performance increasing.
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
ASKER
The previous version of this application runs fine on a server with similar specs only using SQL Server 2000. There were some modifications made to the application that involve receiving data at regular intervals from a remote machine via MSMQ put in place when it was moved over to the new machine. I was invistagating the database on the old machine, and the indexes and queries that I looked at were identical. I'm thinking it's something else related either to the configuration on this server, SQL server 2005 itsself, or the modifications made to the application.
after migration from 2000 to 2005, make sure that you ran the following
DBCC UPDATEUSAGE ()
DBCC CHECKDB()
ALTER DATABASE <dbName> SET PAGE_VERIFY CHECKSUM
EXEC sp_UpdateStats
DBCC UPDATEUSAGE ()
DBCC CHECKDB()
ALTER DATABASE <dbName> SET PAGE_VERIFY CHECKSUM
EXEC sp_UpdateStats
Can you check the results of the following command:
DBCC MEMORYSTATUS
DBCC MEMORYSTATUS
ASKER
aneeshattingal: I will check with the person that migrated the database, however I don't think they did anything like that. If not, I will run later this evening after the workload dies down.
Crag: I can indeed. Is there a specific field or set of fields you're looking for?
Per Craig's previous suggestion, I have tried different settings for the min and max memory settings. I tried 2gb min and max and got the same behaviour as before. I currently have set it to 3gb min and max with awe enabled. At this point is has run about 1.5 hours longer then it normally would with no problems. I would love for it to be fixed, but I suspect the extra memory just gives it more time between restarts. I will continue to monitor and let you know what happens.
Crag: I can indeed. Is there a specific field or set of fields you're looking for?
Per Craig's previous suggestion, I have tried different settings for the min and max memory settings. I tried 2gb min and max and got the same behaviour as before. I currently have set it to 3gb min and max with awe enabled. At this point is has run about 1.5 hours longer then it normally would with no problems. I would love for it to be fixed, but I suspect the extra memory just gives it more time between restarts. I will continue to monitor and let you know what happens.
ASKER
Here are the dbcc memorystatus results as the server is running right now, with no problems so far, set to 3gb min/max and awe enabled:
http://www1.freewebs.com/phxvigo/memorystatusresults.htm
http://www1.freewebs.com/phxvigo/memorystatusresults.htm
Which edition of Windows & SQL Server is installed?
Do you have either the /3gb or the /pae switch in the boot.ini
What size is the TempDB and is it set to autogrow?
Do you have either the /3gb or the /pae switch in the boot.ini
What size is the TempDB and is it set to autogrow?
ASKER
Windows Server 2003 Standard SP 2, no /3gb or /pae. SQL Server 2005 Standard SP 2.
Temp DB info:
Data initial size is 8mb, set to autogrow by 10%. It looks like there have been 50 to 60 auto growth events, it's currently using 57.69 mb.
Log initial size is 1mb set to autogrow by 10%. Looks like only 1 autogrowth event, it's currently at .75 mb
Temp DB info:
Data initial size is 8mb, set to autogrow by 10%. It looks like there have been 50 to 60 auto growth events, it's currently using 57.69 mb.
Log initial size is 1mb set to autogrow by 10%. Looks like only 1 autogrowth event, it's currently at .75 mb
I'm puzzled as to why SQL Server lets you set 3GB min/max when you don't have the /3gb switch set. Check in the Task Manager and see how much the sqlservr.exe is using, I'll bet no more than 2GB.
Check out the following link:
http://support.microsoft.com/kb/283037
Check out the following link:
http://support.microsoft.com/kb/283037
ASKER
I believe it's AWE that allows SQL Server to use more than the normal 2gb of memory. See reference here: http://msdn2.microsoft.com/en-us/library/aa366527.aspx.
Indeed, when I first raised the max limit to 3gb, the perfmon counter for SQL target server memory still showed 2gb. It wasn't until after I enabled AWE that the target system memory went up to 3gb.
I also found this quote "Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information." in the Memory Bottleneck section of this document: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx (There's also some links to additional reading there)
That indicates that the Task manager wouldn't show the correct memory usage using AWE. In my case, Task manager shows sqlservr.exe using about 74 MB, but perfmon SQL Total Server Memory currently shows about 2.3 GB.
Indeed, when I first raised the max limit to 3gb, the perfmon counter for SQL target server memory still showed 2gb. It wasn't until after I enabled AWE that the target system memory went up to 3gb.
I also found this quote "Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information." in the Memory Bottleneck section of this document: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx (There's also some links to additional reading there)
That indicates that the Task manager wouldn't show the correct memory usage using AWE. In my case, Task manager shows sqlservr.exe using about 74 MB, but perfmon SQL Total Server Memory currently shows about 2.3 GB.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Changing the max memory to 3072MB and enableing AWE seems to have done the trick. The server is purring along for two days under normal weekday loads. At the end of two days, memory usage was around 2.5GB, CPU usage was averaging around 20%, almost 0 page swaps, and best of all, an average of .3 disk queues.
I went through the steps outlined in the "memory bottleneck" section of this document: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYIAC
which indicated an "internal memory pressure" issue. Since AWE wasn't enabled, the server was limited to 2GB which apparently wasn't enough. Enableing the AWE seems to have resolved the issue.
I'm giving credit for the solution to Craig who set me on the path to changing memory settings in a pointer to this question here: https://www.experts-exchange.com/questions/22879342/pointer-SQL-Server-slows-down-after-a-couple-hours.html
Many thanks to everyone for your help.
I went through the steps outlined in the "memory bottleneck" section of this document: http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYIAC
which indicated an "internal memory pressure" issue. Since AWE wasn't enabled, the server was limited to 2GB which apparently wasn't enough. Enableing the AWE seems to have resolved the issue.
I'm giving credit for the solution to Craig who set me on the path to changing memory settings in a pointer to this question here: https://www.experts-exchange.com/questions/22879342/pointer-SQL-Server-slows-down-after-a-couple-hours.html
Many thanks to everyone for your help.