May 9, 2019 at 7:11 pm
One of my clients has requested me to gather the information on top 5 cpu consuming queries. I can find several of them online that pulls it from cache and dump the result set into the table but my main concern is that I don't want to insert the same queries back again if it has already been dumped into the table...unless there is a change in the time the plan was created. I am sure someone might have some kind of a similar solution in place...appreciate your help.
Thanks
May 9, 2019 at 8:47 pm
Can't help but wonder if the Top 5 change during the day/week. If so, it would make sense to include the execution date/time with the query name in the table. Otherwise, how do you know which queries are consistently in the top 5?
May 9, 2019 at 8:54 pm
MS has a built-in standard report for Top 10 Avg or Total CPU queries, but that is a point-and-click, and I don't know of any way to store the results.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 9, 2019 at 9:29 pm
Any reason you couldn't take this and turn it into an INSERT statement into a table somewhere?
SELECT TOP 10
GETDATE() AS EventDate,
qs.total_worker_time/(qs.execution_count*60000000) as [Minutes Avg CPU Time],
qs.execution_count as [Times Run],
qs.min_worker_time/60000000 as [CPU Time in Mins],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2) as [Query Text],
db_name(qt.dbid) as [Database],
object_name(qt.objectid) as [Object Name]
FROM sys.dm_exec_query_stats qs cross apply
sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Minutes Avg CPU Time] DESC;
May 10, 2019 at 1:32 pm
You're on 2016. Use the Query Store. Nice and easy way to gather that information. Otherwise, some of the suggestions are good, query the cache, use extended events and aggregate the data, all pretty standard stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2019 at 6:44 am
Any reason you couldn't take this and turn it into an INSERT statement into a table somewhere?
SELECT TOP 10
GETDATE() AS EventDate,
qs.total_worker_time/(qs.execution_count*60000000) as [Minutes Avg CPU Time],
qs.execution_count as [Times Run],
qs.min_worker_time/60000000 as [CPU Time in Mins],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2) as [Query Text],
db_name(qt.dbid) as [Database],
object_name(qt.objectid) as [Object Name]
FROM sys.dm_exec_query_stats qs cross apply
sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Minutes Avg CPU Time] DESC;I can turn that into an insert but again the goal is to have the table only append the records that already doesn't exist in the cache or if a new plan was created for that stored proc etc. and the old plan was evicted. I am not sure if I am making sense but I don't want repetition instead the record should be appended only if the plan has changed.
May 15, 2019 at 7:05 am
@Grant: Can you provide me an example on how to do that using Query store or extended events or any article that you can point out that would help me get started.
Hmmm.... Good question. I have a ton of information on this in my book on query tuning. Certainly I show several ways to get this done. However, I don't have a specific example showing CPU on my blog or YouTube channel. However, for Query Store, here's an example that shows how to compare two sets of aggregations within Query Store. Just substitute the CPU columns that are there. Here's a video of how to use the Live Data window in Extended Events. I show how to aggregate by queries. You could also aggregate queries by CPU using the same approach.
Again, these things are just built right in to the available tooling.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply