November 4, 2011 at 1:47 pm
Dear All,
I would like to share your experience and views to the following problem:
1. SQL 2005 is running on Intel Xeon E5430 @ 2.67 Ghz (4 CPUs), 3.00 GB of RAM with Microsoft Windows Server 2003 R2 std edition sp2
2. There are dailly batch jobs and around 20 on line users
3. Every night we re-create and update index statistics
4. The DB is around 12 Gbytes to drive D:
5. Only SQL server is running to the machine
Several days the users are complaining because the system is slow. The performance is not as usual. The tasks everyday are approximately the same. For example, they use barcode scanners to enter data. When the server is running as expected it takes about 1 second to complete the task. When its slow it takes more than 15 seconds !
If we restart the SQL Server sercive everything is fine. The server is running normally for 2 or 3 days and then gradually becomes slow.
We run SQL monitor software and we found out that the memory used by SQL is around 2.5 - 2.8 Gbytes. Network utilization and disk activities seem Ok.
Has anybody any suggestions how to identify what bothers the machine and occasionally is slow ?
November 7, 2011 at 5:55 am
Before we talk monitoring, let's validate your nightly process. You recreate your indexes and then update the stats? Is that right? If so, you might be hurting performance right there. If the update on the statistics is a sampled update, you could be modifying more accurate statistics created by rebuilding the indexes, which will cause a full scan on statistics as part of the build process.
In terms of monitoring, Memory usage is likely to forever, and always, be maxed. Network utilization is only an indication of load and not of performance. To drill down you need to start looking at wait states and queues. I'd also suggest taking a look at the page life expectancy in your memory (it's not a perfect measure, but it's useful). When the queries start to run slowly, capture the execution plan and then compare that to when the queries are running fast. Make sure you have a method for monitoring for blocking because resource contention is a common performance bottleneck. That's all just getting started. You can check out my performance tuning book (below in my signature) for more details on things to monitor. It will be applicable to 2005.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 7, 2011 at 6:13 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2011 at 8:05 am
Dear Grant,
regarding your first issue. Every night we run a maintenance plan where we rebuild all indexes. By the way, since we rebulid is it necessary to Update statistics ?
Regarding your second issues. We monitor server, using SQL MONITOR from Red Gate, so far we didnt find anything strange. We know the available memory for SQL server is low but we do not understand why memory is not released when SQL does not need it.
November 7, 2011 at 8:35 am
ionas (11/7/2011)
Dear Grant,regarding your first issue. Every night we run a maintenance plan where we rebuild all indexes. By the way, since we rebulid is it necessary to Update statistics ?
Regarding your second issues. We monitor server, using SQL MONITOR from Red Gate, so far we didnt find anything strange. We know the available memory for SQL server is low but we do not understand why memory is not released when SQL does not need it.
If you rebuild the indexes completely, no, I wouldn't update stats too.
SQL Monitor from Red Gate will show you the stuff that could be causing the issues. Go to the Analysis tab and look at the appropriate date & time to see the information gathered. Just looking at available memory doesn't mean much. SQL Server allocates memory and then hangs onto it, managing it internally. So when you look at a system, the memory is going to be maxed per what's allocated to SQL Server, all the time. That's normal, expected, behavior.
Look at stuff like Average CPU Queue Length, Memory Pages/Sec, Avg. Disk Queue Length, Compilations/Sec, Recompilations/Sec, Latch Wait Time, Lock Wait Time, Page Life Expectancy. Compare these values during a bad time and a good time (but preferably, both times should be under full load). That will give you indications as to what's happening in the system. Remember, a number by itself is just a number, you need to compare it to something to understand whether that number is good, bad or irrelevant.
Also, check the longest running queries (and most frequently called) from a period when the system is slow and when it's fast. Are they the same ones? If so, look at the execution plans for those queries and see if you have tuning opportunities. If not, look at the longest running ones during the slow period and look for tuning opportunities there. If they are different, why are they different? Are there aspects of the application that are more problematic than others.
Basically, you have to build up a set of information so you understand what's happening and why. Rebooting the system suggests that something is holding on to resources, possibly blocking other sessions, causing contention, and in general being a nuisance. You need to identify what that is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 7, 2011 at 9:03 am
ionas (11/7/2011)
regarding your first issue. Every night we run a maintenance plan where we rebuild all indexes. By the way, since we rebulid is it necessary to Update statistics ?
Index statistics, no. Column statistics, probably yes. Those are stats not associated with an index, they don't get updated in a rebuild.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2011 at 11:09 pm
Dear Grant,
thank you very much for your suggestions. What I found very interesting is to compare the execution plan of the query we suspect before and after performance degrade. The point is, its not a simple query but a procedure.
November 8, 2011 at 9:17 am
1) do you have max memory for sql server set?
2) Is TCP Chimney on?
3) you need to do a file IO stall and wait stats analysis to find out where your bottleneck is
4) also monitor routinely for blocking. use sp_whoisactive for this - freebie from sqlblog.com
5) you should get a professional tuning consultant on board for a quick review and some mentoring so you can a) get stuff fixed quickly and b) learn how to do good perf tuning yourself
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 8, 2011 at 12:05 pm
Dear Gevin,
my replies below:
1) do you have max memory for sql server set?
No, because memory is only 3 Gbytes. No min or max values.
2) Is TCP Chimney on?
No, because we do not have much network traffic.
3) you need to do a file IO stall and wait stats analysis to find out where your bottleneck is
See below
4) also monitor routinely for blocking. use sp_whoisactive for this - freebie from sqlblog.com
Currently, we use SQL monitor from Red Gate but I will try this also.
5) you should get a professional tuning consultant on board for a quick review and some mentoring so you can a) get stuff fixed quickly and b) learn how to do good perf tuning yourself
November 8, 2011 at 12:51 pm
ionas (11/8/2011)
Dear Gevin,my replies below:
1) do you have max memory for sql server set?
No, because memory is only 3 Gbytes. No min or max values.
2) Is TCP Chimney on?
No, because we do not have much network traffic.
3) you need to do a file IO stall and wait stats analysis to find out where your bottleneck is
See below
4) also monitor routinely for blocking. use sp_whoisactive for this - freebie from sqlblog.com
Currently, we use SQL monitor from Red Gate but I will try this also.
5) you should get a professional tuning consultant on board for a quick review and some mentoring so you can a) get stuff fixed quickly and b) learn how to do good perf tuning yourself
A) ALWAYS set max memory on EVERY SQL Server you have. if you only have 3GB of RAM, set max memory to 2GB and monitor for paging.
B) One of the service packs for Windows Server 2003 enabled TCP Chimney when applied. Your statement makes me think that you assumed it wasn't on - please verify if you didn't. It can really choke the box and can be one of those "over time" issues IIRC.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 8, 2011 at 11:19 pm
Dear Kevin,
1. Ok. I will set max value to 2GB and monitor how it goes
2. I checked (netstat -t) TCP Chimney and is off
3. Meantime, I asked from customer to add more memory......!!!!
Thanks for your help
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply