Measuring Ad-hoc workload on a per database level

• simon_s

  Ten Centuries

  Points: 1362

  More actions

  April 5, 2022 at 10:57 am

  #4010964

  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

  • This topic was modified 3 years, 3 months ago by simon_s.

• Johan Bijnens

  SSC Guru

  Points: 135536

  More actions

  April 5, 2022 at 1:59 pm

  #4011047

  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

• simon_s

  Ten Centuries

  Points: 1362

  More actions

  April 5, 2022 at 2:55 pm

  #4011087

  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 !

  • This reply was modified 3 years, 3 months ago by simon_s.

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  April 5, 2022 at 5:35 pm

  #4011172

  (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".