by
2
6
3,220
5
Top 1% !
Popular
Famous
Easy-to-find
Specified
OpenSource
Popularity: 3912nd place
DBMSMS SQL Server
LanguageT-SQL
LicenseMIT_X11
OSWindows
SQLServerVersion2008

Detect the worst SQL queries in avg CPU time in SQL Server

Detect worst performing sql queries which is slowing down Microsoft SQL Server, this script return top queries taxing sql server CPUs.
Thanks to Patrick Akhamie: http://www.sqlservercentral.com/scripts/TOP+CPU+Queries/110012/
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/62560/Detect-the-worst-SQL-queries-in-avg-CPU-?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
use tempdb go IF object_id('tempdb..##FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set1] GO declare @ServerTime datetime = getdate() , @ConvertMiliSeconds bigint = 1000 , @FilterMoreThanMiliSeconds bigint = 1 , @FilterHours bigint = 2 , @execution_count bigint = 2 , @debugFlg bit = 0 if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds , @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours , @execution_count as execution_count select TOP 300 @@servername as servername,@ServerTime as runtime ,isnull(db_name(QueryText.dbid),'PreparedSQL') as DBName ,SUBSTRING(QueryText.text, (QueryStats.statement_start_offset/2)+1, (isnull(( CASE QueryStats.statement_end_offset WHEN -1 THEN DATALENGTH(QueryText.text) WHEN 0 THEN DATALENGTH(QueryText.text) ELSE QueryStats.statement_end_offset END - QueryStats.statement_start_offset),0)/2) + 1) AS QueryExecuted ,total_worker_time AS total_worker_time ,QueryStats.execution_count as execution_count ,statement_start_offset,statement_end_offset ,(case when QueryText.dbid is null then OBJECT_NAME(QueryText.objectid) else OBJECT_NAME(QueryText.objectid, QueryText.dbid) end) as ObjectName ,query_hash ,plan_handle ,sql_handle into ##FindTopCPUQueries_set1 from sys.dm_exec_query_stats as QueryStats cross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryText where QueryStats.query_hash IN ( select QueryStatsBaseTable.query_hash from sys.dm_exec_query_stats QueryStatsBaseTable where last_execution_time > DATEADD(hh,-@FilterHours,GETDATE()) group by query_hash having (sum(total_worker_time)/sum(execution_count))>@ConvertMiliSeconds and sum(execution_count)>@execution_count ) ORDER BY total_worker_time/execution_count DESC; if @debugFlg=1 select * from ##FindTopCPUQueries_set1 order by QueryExecuted IF object_id('tempdb..##FindTopCPUQueries_set2') is not null DROP TABLE [dbo].[##FindTopCPUQueries_set2] select servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime ,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName into ##FindTopCPUQueries_set2 from ##FindTopCPUQueries_set1 group by query_hash,servername,runtime order by AvgCPUTime desc select * from ##FindTopCPUQueries_set2 --where QueryExecuted like 'select TOP 300%' order by AvgCPUTime desc
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus