April 5, 2022 at 10:57 am
Ok so I need to see how much workload on a server (per database) is ad-hoc. Gut feel its high for one db in particular . is the following a valid way to determine this or is there a better approach ?
SELECT Convert(INT,Sum
(
CASE a.objtype
WHEN 'Adhoc'
THEN 1
ELSE 0
END)
* 1.00/ Count(*) * 100
) as 'Ad-hoc query %'
FROM sys.dm_exec_cached_plans AS a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) AS QueryText
where dbid = 5
thanks Simon
April 5, 2022 at 1:59 pm
That will only get you the currently cached plans.
You may want to activate QueryStore to have a better view over a given time frame.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 5, 2022 at 2:55 pm
Thanks for the reply. Query store might be a double edged sword in this instance . Id need to run it in capture ALL to get accurate results and QS really suffers when trying to cope with adhoc work loads I believe. Part of the reason I want to measure the size/type of the workload is to know whether query store would be an option for that database !
April 5, 2022 at 5:35 pm
(1) Did you set on the "optimize for ad-hoc workloads" SQL Server setting?
(2) What is the "threshold for parallelism setting"? In particular, did you leave it at the default of 5 (IIRC), which is notoriously too low.
(3) What general index tuning have you done for the critical db(s)?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply