SQL getting slower and slower
-
We are running SQL Server 2000 SP 3 and have been experiencing performace problems as of late. It seems that the sever gets exponentially slower - ie, the more we run on it (not more at one time, but the longer it continues to run), the slower it seems to get. We have all of our tables indexed and none of the stored procedures have changed for at least 6 months - and even then, any changes were minimal. We have also looked at the stored procedures and they seem to be pretty efficient.
Has anyone else experienced anything like this?
Another point to add is that Task Manager shows 4 processors when we actually have 2 physical processors in the server. We have been told that it shows 4 processors because of hyperthreading due to the fact that we are running Xeon processors?
Any info?
Thanks!
-
Are statistics being updated? This could cause things to run slower. Has the data changed much?
Does a recompile of a sproc make it run faster?
-
Auto update and Auto create were checked. I will remove those and see if they help.
Thanks!
-
Can you check the overall system performance with performance monitor, such as CPUs, memory, disk I/O and network?
Is this server a dedicated database server or shareed with applications?
-
I second Allen's questions. And yes, the hyperthreading is the reason for the 4 cpu's in task manager.
When I first took this job I had a server do the same thing. They were restarting it to get it going again. You probably have a bottleneck somewhere. It could be caused by hitting the knee in an exponential curb. The database growth may have triggered this.
I fixed our problem by fixing a batch job that ran every hour. It needed more than this, but it was enough to keep it going.
Here are some potential, but not limited too probs you may be having
- Locking
- excessive and/or poor indexing
- too many objects in tempdb
- i/o bottleneck
- cpu bottleneck
"Keep Your Stick On the Ice" ..Red Green -
BTW, you want Auto Create Statistics and Auto Update Statistics on unless they are causing a problem. Now that I'm thinking of it, I'm not sure how to monitor that. Profiler maybe.
-
In my travels among different clients, I often see this. The problem most often is fragmentation of the data (mdf) file(s). Check the specific file fragmentation by using a tool like Contig from http://www.systernals.com. To defrag it, you will need to detach the database and allow plenty of time (might want to consider doing it on a weekend or evening).
To prevent the problem, make sure Autoshrink is not set, set the primary file size to handle most growth, and set the growth to reasonably large size to grab larger chunks of disk (Windows server will try to allocate contiguous space when requested from SQL Server). Note that you should defrag again after resetting sizes.
Mark -
File fragmentation is a good thing to look into 🙂 What would the difference be between a tool like Contig and the defrag tool that comes with the operating system.
Steve, I can't find any way to monitor the Create and Update Stats via profiler. I was curious and didn't find anything. Let me know if you do. The only thing I can think of is to look at the stats date and count the number of statistics in sysindexes.
Jeff
"Keep Your Stick On the Ice" ..Red Green -
Contig uses same builtin defrag service in Windows 2000/XP/2003, but does it for one named file. So instead of waiting for thousands of files to be checked and defragged, you are getting the one you are concerned with (your database file) . Also (if memory serves correctly), there was some problem using the Defrag and large mdf files, something about t would not move it since it could not (or would likely not) find one contiguous space for a large mdf file. Note that Contig is free to use.
Mark -
We have the same problem. Our server runs great for a month or so then we start getting timeouts and the queries start taking forever to complete. As soon as we bounce SQL the problem clears up for another month. If our problem was with the indexes or a fragmented DB file then wouldn't you expect the problem to exist even after bouncing the SQL service?
-
If you reboot and it gets faster I would think the issue might be in your client applaction. It could be keeping resources open when it should be closing them.
Do you use any cursors? This could be cursors not being closed out.
One more option that could be slowing it down is cheap RAM.
-
Food for thought..........
- tempdb is recreated every time the db server is restarted.
- Make sure you have good stats updates
- Make sure you have a good reindexing plan.
- Check if freeing the buffer cache and procedure cache has any effect
- Watch/monitor the number of connections
- Beware of updateable views
- Watch for specific processes with blocking or excessive locks
"Keep Your Stick On the Ice" ..Red Green -
I don't think indexes and fragmented DB files contributing to the slowness in this particular symptom.
If other applications also run in same server, The applications may compete system resources such as CPUs, Memory etc. That is why we always check the overall system performance before going into one particular application like SQL Server.
Try to find out what resources SQL Server is waitting and most likely there are the bottleneck for slow performance. Sysprocesses should give you the information about wait resources and wait time.
Perform statistics update manually regularlly to prevent out-of-dated index statistics which could led Query optimizer to choose inefficient execution plan.
We also seen memory leak caused by application when users start to complain application performance and we have to restart server. Once applied service pack from vendor for that application, I don't receive any complains.
-
Man, great replies guys, thanks!
I have run performace monitors and we are not pegging out the processor or memory. Occasionally we will get spikes, but those spikes do not run up to 100%. The server is used strictly for SQL and no other users have files on this machine, nor do any other services or applications run. This is strictly for SQL use only.
I did look at our drives and they are 99% fragmented. We are going to archive off some databases and set the data, log and indexes each on their own physical drive. At that time, I will defrag and see if that helps out too. It can't hurt.
We have autoupdate statistics set to on - so we might look into this as well - but I think we are ok with that configuration.
Overall, it seems that it is NOT a systems resources problem - we ahve plenty of memory and processor power - but of course we always monitor it to make sure nothing is or has changed.
-
2 other things to look at, there is a store procedure that you have to run that updates the statistics that stored procedures use. I'm not sure of the name.
The other thing that we've noticed is if you keep you log file smaller the database appears to run faster.
Every night we backup the log file then trim it down. We also update the table, index and stored procedure statistics. After we did this we notice improved response times.
-Rick
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply