June 30, 2009 at 8:45 am
Working with PerfMon, using
SQL Server: SQL Statistics: Batch Requests/sec
SQL Server: SQL Statistics: SQL Compilations/sec
SQL Server: SQL Statistics: SQL Recompilations/sec
I need to know roughly what ratios there should be, particularly between the first two which look a little high to me running at 1000, 500, 5 respectively.
June 30, 2009 at 9:37 am
5 recompiles for 1000 batches is pretty good. 50% compile rate on your batches suggests that you've got lots of ad hoc queries. If it's a reporting system, that ratio could be even higher. If it's an OLTP system, you probably have tuning opportunities around using stored procedures or parameterized queries.
It's hard to say beyond those general statements not knowing what's occuring in more detail on your system.
"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
June 30, 2009 at 9:47 am
The batch requests per second seems a bit high, but without know more about the application, it's hard to say.
A batch request rate that high might be an indication that the application is using server side cursors, something that is amost always a bad idea. You should be able to identify that by running a trace to see what is actually in the batch requests. If you see a lot of calls to sp_fetchnext, then they are using server side cursors.
You can read more about server side cursors here:
http://www.sqlservercentral.com/Forums/Topic715941-145-1.aspx
June 30, 2009 at 10:08 am
Many thanks for your comments, it confirms many of my suspicions about this box which isn't very well :sick:
It's not a reporting system and I have a feeling there's been far too much lax development hitting this server with people customising their own queries to suit their particualar needs. Hence my question about the ratio of batches per compiles - I've been told this ideally should be about 10% for a well tuned production system. Is this roughly where I should aim?
Cursors: Yes, there are cursors, heavily used in some places. Watching it trawl through a 1,000,000 row table line by agonizing line isn't helping.
June 30, 2009 at 11:57 am
I was never aware of a particular sweet spot in terms of compiles to batches. The fewer the better was the only rule I operated under.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply