March 16, 2016 at 4:00 am
Hi,
I am recording the counter values for Batch Requests/sec and Compilations/sec, to try and understand the workload and activity.
I have read that as a benchmark we should have about 1 compile per 10 batches. (or there about..)
We on the other hand have a 1:1 to 1:2 ratio.
This either means the server cannot keep the plan in cache long enough or it is seeing loads of first time queries all the time.
I am almost certain it is not because of new queries because 99% of the code is in existing stored procs being called over and over again.
Yes there are some individual CRUD statements and some stored procs using Dynamic SQL.
So then it must be that the server can't keep it in memory long enough, correct?
I am also seeing the majority of plans in cache is Adhoc; and that 70% of those plans have a UseCount of 1.
Can you tell me WHY the server can't keep it in memory? Or why I have so many first time compilations or single use plans?
We are running SQL 2014, have 120 GB RAM, 24 CPU server with an average of 1000 Batch Requests per second.
March 16, 2016 at 4:09 am
Casper101 (3/16/2016)
I am almost certain it is not because of new queries because 99% of the code is in existing stored procs being called over and over again.I am also seeing the majority of plans in cache is Adhoc; and that 70% of those plans have a UseCount of 1.
Those two statements are contradictory.
Adhoc plans are not stored procedures. They're adhoc queries from the application. A usecount of 1 means that the plan got used once only. Hence you do have lots and lots of new queries coming in all the time.
If that shouldn't be the case, then you may want to do some investigation as to why there's so many adhoc queries when 99% of calls are supposed to be stored procedures.
If you investigate and find out that the ad-hoc queries are correct and should be there (and not the result of some bug or unwanted app), then consider setting optimise for ad-hoc workloads on.
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
March 16, 2016 at 8:26 am
Hi Gail,
Thanks for your reply.
I have investigated the Adhoc queries and it is in fact all valid. I have traced most of them down to Dynamic SQL within stored procs. I was surprised at the number of procs with dynamic sql and how often they are being executed... The other adhoc queries which are not in procs are also valid code being executed by the developers in SSMS but majority I have tracked to a integration process which does not use procs at all.
Optimise for ad-hoc workloads is set already.
Is there any improvement I can do here with regards to the dynamic SQL in the procs?
Would you advise I run DBCC FREESYSTEMCACHE('SQL Plans') to clear these adhoc plans?
March 16, 2016 at 8:32 am
Casper101 (3/16/2016)
Would you advise I run DBCC FREESYSTEMCACHE('SQL Plans') to clear these adhoc plans?
No.
I'd advise that the code be changed so that parameterised queries (which generate reusable plans) are what get sent to the DB.
For stored procs, that means changing from EXEC with a string, to sp_executesql with a parameterised statement and with parameters passed to it
If the integration app is in-house, then changes to that will probably need the app devs to look at how they're calling the DB. If not, and there's a pattern of lots of the 'same' query only with different parameters look at plan guides that force the option 'forced parameterisation' on them.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply