November 16, 2016 at 7:20 am
Hi All,
I noticed recently that one servers SQL compilations per batch requests was rather high around 45%. And under load the CPU flatlines 100% but no blocking or deadlocks occur but the SQL compilations go up with the batch requests.
We are relying on ORM functionality a lot more lately and have read this can increase adhoc querying.
We also have web code which queries the DB via concatenating params into the SQL, I know not great...
After further digging i spotted that the SQL cache is full of Adhoc queries (attached)
So after seeing the amount of Adhoc queries in the cache I enabled force parameterization , this did nothing. Still the same
,a load of 1 hit plans and adhoc queries, I then also enabled Optimise For adhoc workload to to keep cache bloat at bay.
Again nothing has really changed (didnt expect this to help compilations anyway) It has made the cache smaller due to the stubs
Wierdly when i inspect the cache i see similar queries with the same plan when i run them in SSMS? so why are they cached multiple times?
Also spotted quite a few IF NOT EXISTS...do this... do these fall under Force parameterization limitations ?
Is they anything else someone can recommend or me to look at to get the SQL compilations down?
Thanks
November 16, 2016 at 9:49 am
Kimberly Tripp has an excellent article about dealing with this kind of problem here:
I have some third party software systems that needed this kind of cleanup to run periodically, and this has helped a lot.
November 16, 2016 at 9:55 am
to find out what's hurting your CPU is a different problem, you'll probably want to examine the dynamic management views to see what queries are causing the most CPU, such as the worker columns in sys.dm_exec_query_stats
https://msdn.microsoft.com/en-us/library/ms189741(v=sql.110).aspx
November 16, 2016 at 11:54 am
Hi Chris, doesn't a high volume of adhoc queries with high compilations with batch requests cause CPU issues?
November 16, 2016 at 1:07 pm
Chris Harshman (11/16/2016)
to find out what's hurting your CPU is a different problem, you'll probably want to examine the dynamic management views to see what queries are causing the most CPU, such as the worker columns in sys.dm_exec_query_statshttps://msdn.microsoft.com/en-us/library/ms189741(v=sql.110).aspx
I have been looking through the dmvs but there is not one specific thing im pretty sure its the number of adhoc requests and compilations required due to no existing plans thats driving up the CPU.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply