BI SQL # 178 : SQL Server DBA Scripts : List expensive queries MAIN
Hi Folks,
In this article we are going to cover How to List expensive queries MAIN.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
Get List of expensive queries.
Description of SQL Script:
This Transact-SQL script will help in find mainly expensive queries
In terms of Average CPU execution time or Average logical operations
SQL Script Output Column
SQL Script Code
DECLARE @MinExecutions INT; SET @MinExecutions = 5 SELECT EQS.total_worker_time AS TotalWorkerTime ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO ,EQS.execution_count AS ExeCnt ,EQS.last_execution_time AS LastUsage ,EQS.total_worker_time / EQS.execution_count AS AvgCPUTimeMiS ,(EQS.total_logical_reads + EQS.total_logical_writes ) / EQS.execution_count AS AvgLogicalIO ,DB.NAME AS DatabaseName ,SUBSTRING(EST.TEXT, 1 + EQS.statement_start_offset / 2, ( CASE WHEN EQS.statement_end_offset = - 1 THEN LEN(convert(NVARCHAR(max), EST.TEXT)) * 2 ELSE EQS.statement_end_offset END - EQS.statement_start_offset ) / 2) AS SqlStatement -- Optional with Query plan; remove comment to show, but then the query
takes !!much longer time!! --,EQP.[query_plan] AS [QueryPlan] FROM sys.dm_exec_query_stats AS EQS CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP LEFT JOIN sys.databases AS DB ON EST.dbid = DB.database_id WHERE EQS.execution_count > @MinExecutions AND EQS.last_execution_time > DATEDIFF(MONTH, - 1, GETDATE()) ORDER BY AvgLogicalIo DESC ,AvgCPUTimeMiS DESC
SQL Script Output Screenshot
User Level to execute
200
Hope you will like How to List expensive queries MAIN.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
