?
Solved

SQL Server 2005 runs slow after a couple hours

Posted on 2007-10-03
21
Medium Priority
?
511 Views
Last Modified: 2007-10-12
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.
0
Comment
Question by:phxvigo
  • 10
  • 4
  • 3
  • +3
21 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20009594
update service pack
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20009605
0
 

Author Comment

by:phxvigo
ID: 20010368
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 7

Expert Comment

by:assyst
ID: 20010604
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.
0
 

Author Comment

by:phxvigo
ID: 20015924
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.
0
 

Author Comment

by:phxvigo
ID: 20035537
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.
0
 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20035840
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.
0
 

Author Comment

by:phxvigo
ID: 20036035
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.
0
 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20036346
0
 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20036364
This is what Microsoft recommends for performance increasing.

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
0
 

Author Comment

by:phxvigo
ID: 20044711
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.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20046002
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
0
 
LVL 5

Expert Comment

by:Crag
ID: 20048830
Can you check the results of the following command:
DBCC MEMORYSTATUS
0
 

Author Comment

by:phxvigo
ID: 20051033
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.

0
 

Author Comment

by:phxvigo
ID: 20051133
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
0
 
LVL 5

Expert Comment

by:Crag
ID: 20051229
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?
0
 

Author Comment

by:phxvigo
ID: 20051327
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
0
 
LVL 5

Expert Comment

by:Crag
ID: 20051726
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
0
 

Author Comment

by:phxvigo
ID: 20052147
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.
0
 
LVL 5

Accepted Solution

by:
Crag earned 1500 total points
ID: 20056712
Mmm. Now I'm confused. From my experience using AWE it only made a difference when a system has more than 4GB of physical memory as it is designed to work around the physical addressing limit of 4GB. From the initial text your system only has 4Gb total so this doesn't make sense.
Not only that but AWE doesn't work unless the /pae boot switch is used - automatic if using hot add memory.

This is all academic anyway as it doesn't solve your performance issue.
SQL Server should cope with reasonable amounts of memory use - which it would appear to do for the first couple of hours. What causes it to lose track after that can only be identified by close monitoring of the Windows & SQL perfmon counters and analysis of the Profiler data. There is either gradual decline due to one type of transaction or a sudden switch due a specific transaction that causes it to start requesting more pages from the disk. Sorry I can't help more
0
 

Author Comment

by:phxvigo
ID: 20070140
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: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22879342.html

Many thanks to everyone for your help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question