Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-11-25 : 10:21:51
|
| Hi Team,I am running a performance test for a web application. A load test is run for 15 minutes on a fresh database that has migrated data. The values from SQL Performance Counters are listed belowAvg. Batch Requests/Sec = 66 Avg. SQL Compilations/Sec = 21 (Allowed < 10% of the number of Batch Requests / sec )Avg. SQL Re-Compilations/Sec = 0.85 (Allowed < 10% of the number of Batch Requests / sec )I am getting a very high “Compilations/Sec” while “Re-Compilations/Sec” pretty much under the limit. So it is not because of dynamic query, I think. <<Please note that ReCompliation/Sec it is too low.>>1) What could be the possibilities of this high value for “Compilations/Sec”?2) How this can be reduced?3) How can I see the SPs that are complied and their compilation time?Please advise.Other Exceptional Values are:Avg. Logins/sec - 56 (Allowed <2)Avg. Lock Requests/sec -640901 (Allowed <1000)ThanksLijo Cheeran Joseph |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-25 : 10:26:27
|
| Does the application run sql or call stored procedures?I would guess ad hoc queries so the plan is never cached.Could be that the SPs have dynamic sql?A recompilation is when there is a plan but it's no longer valid.Not sure whether an SP that is set to recompile on every run or contains a reference to an existing temp table would be a compile or recompile - I suspect a recompile.Does it cause an issue?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-11-25 : 10:46:05
|
quote:
Originally posted by nigelrivettI suspect a recompile.
Thanks for the reply. But when you look at the ReCompliation/Sec it is too low. Why is it? Any idea?Anyone has faced such a scenario? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-25 : 11:07:50
|
| If the app is sending ad hoc sql then it will always be considered new and so will never get a plan to recompile.This was in my previous post.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-11-25 : 11:17:25
|
| Thanks for the clarifications.The application has stored procedures.The folowing query results in 38SELECT COUNT(*)FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%sp_executesql%' OR ROUTINE_DEFINITION LIKE '% exec%'AND ROUTINE_TYPE='PROCEDURE'The following query results in 1180SELECT COUNT(*)FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'Out of 1180 SPs only 38 of them use '%sp_executesql%' or '% exec%'. Will it be causing this much difference?Avg. SQL Compilations/Sec = 21 (Allowed < 10% of the number of Batch Requests / sec )Avg. SQL Re-Compilations/Sec = 0.85 (Allowed < 10% of the number of Batch Requests / sec )ThanksLijo |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-25 : 11:23:22
|
| The low recompilations probably means that the SPs are reusing plans which is a good thing.The compilatins probably means new sql being executed for the first time (or something is happening to clear cache).If it's not causing an issue then I wouldn't worry about it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-11-25 : 11:37:02
|
quote:
Originally posted by nigelrivettIf it's not causing an issue then I wouldn't worry about it.
But the client worries about it Okay...Lets hear from others also...Everyone has the same opinion? Is it those 38 SPs (out of 1180) that has 'sp_executesql' causing Avg. SQL Compilations/Sec = 21 (around 32 % of batch requests/ sec) ?ThanksLijo |
|
|
|
|
|
|
High Value for “Compilations/Sec”