March 27, 2012 at 6:31 am
There is no script for determining dynamic SQL. You need to ask the developer if he is using it in the system.
If he is, then that will explain some of your Ad Hoc numbers. And unless he can turn the dynamic SQL into a stored proc or a regular query (sometimes you can't), then you're just going to have to live with the Ad Hoc numbers.
March 27, 2012 at 5:29 pm
Brandie Tarvin (3/27/2012)
There is no script for determining dynamic SQL. You need to ask the developer if he is using it in the system.If he is, then that will explain some of your Ad Hoc numbers. And unless he can turn the dynamic SQL into a stored proc or a regular query (sometimes you can't), then you're just going to have to live with the Ad Hoc numbers.
Even if there isn't any dynamic SQL, rarely used but fixed queries whether or not parametrised) and rarely used SPs can have the same effect on plan cache hit rate - if calls to SPs that are called only once per day or once per week account for half the plan executions and there isn't enough stote to cache them all plus all the frequently called procs, you might as well have lots of ad-hoc queries.
On the numbers, I would say that telling SQLS to optimise for ad-hoc queries be a good thing to try, even if there is no dynamic SQL (ad hoc queries actually cover a much wider range of sins than just that: queries typed into a query window of SSMS are ad hoc queries, as are queries constructed by a C++, JScript, or VBScript program; almost everyone has hordes of ad hoc queries, because even in those cases where using ad hoc queries is utterly inappropriate almost no-one pays more than lip-service to modularity so that utterly inappropriate code is written by developers who haven't a clue).
Tom
Viewing 2 posts - 46 through 46 (of 46 total)
You must be logged in to reply to this topic. Login to reply