未加星标

Finding the Worst Performing TSQL Statement

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Have you ever wondered how to find the worst performing TSQL queries on your instance? If you have, you are not alone.

SQL Server maintains query statistics as TSQL commands are run. These query statistics can be returned by using the Dynamic Management Objects provided in SQL Server.

The Dynamic Management Objects sys_dm.exec_query_stats returns a view of the query statistics that SQL Server is currently maintaining. The statistics that this view returned are stored in memory with the cached plan for each query. As long a TSQL execution plan is stored in the plan cache you can return statistics for the TSQL command. Once the command is removed from the procedure cache, then the statistics are also dropped.

Below is the code for a stored procedure that will return the TSQL query statistics that are stored in the procedure cache. This procedure can be passed a number of different parameters. Those parameters are @DBNAME, @COUNT, and @ORDERBY. These parameters are used to determine which statistics are returned, or how the returned data will be sorted.

By using this stored procedure you will be able to return the worst performing TSQL statements.

--CREATE PROC [dbo].[usp_Worst_TSQL] /* Written by: Gregory A. Larsen Copyright 2008 Gregory A. Larsen. All rights reserved. Name: usp_Worst_TSQL Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count, I/O and Elapsed_Time as identified using DMO information. This can be display the worst performing queries from an instance, or database perspective. The number of records shown, the database, and the sort order are identified by passing pararmeters. Parameters: There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT and @ORDERBY. The @DBNAME is used to constraint the output to a specific database. If when calling this SP this parameter is set to a specific database name then only statements that are associated with that database will be displayed. If the @DBNAME parameter is not set then this SP will return rows associated with any database. The @COUNT parameter allows you to control the number of rows returned by this SP. If this parameter is used then only the TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter. The @ORDERBY parameter identifies the sort order of the rows returned in descending order. This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR where "ACPU" represents Average CPU Usage "TCPU" represents Total CPU usage "AE" represents Average Elapsed Time "TE" represents Total Elapsed Time "EC" represents Execution Count "AIO" represents Average IOs "TIO" represents Total IOs "ALR" represents Average Logical Reads "TLR" represents Total Logical Reads "ALW" represents Average Logical Writes "TLW" represents Total Logical Writes "APR" represents Average Physical Reads "TPR" represents Total Physical Read Typical execution calls Top 6 statements in the AdventureWorks database base on Average CPU Usage: EXEC usp_Worst_TSQL @DBNAME='AdventureWorks',@COUNT=6,@ORDERBY='ACPU'; Top 100 statements order by Average IO EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY= 'AE'; Show top all statements by Average IO EXEC usp_Worst_TSQL; */ DECLARE @DBNAME VARCHAR(128) = '<not supplied>' ,@COUNT INT = 999999999 ,@ORDERBY VARCHAR(4) = 'TCPU' -- Check for valid @ORDERBY parameter IF ((SELECT CASE WHEN @ORDERBY in ('ACPU','TCPU','AE','TE','EC','AIO','TIO','ALR','TLR','ALW','TLW','APR','TPR') THEN 1 ELSE 0 END) = 0) BEGIN -- abort if invalid @ORDERBY parameter entered RAISERROR([email protected] parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR',11,1) RETURN END SELECT TOP (@COUNT) COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT))+'*', 'Resource') AS [Database Name] -- find the offset of the actual statement being executed ,SUBSTRING(text, CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset/2 + 1 END, CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1 OR statement_end_offset IS NULL THEN LEN(text) ELSE statement_end_offset/2 END - CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset/2 END + 1 ) AS [Statement] ,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name] ,OBJECT_NAME(st.objectid,dbid) [Object Name] ,objtype [Cached Plan objtype] ,total_elapsed_time / execution_count [Avg Elapsed Time] ,execution_count [Execution Count] ,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs] ,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs] ,total_logical_reads/execution_count [Avg Logical Reads] ,total_logical_reads [Total Logical Reads] ,total_logical_writes/execution_count [Avg Logical Writes] ,total_logical_writes [Total Logical Writes] ,total_physical_reads/execution_count [Avg Physical Reads] ,total_physical_reads [Total Physical Reads] ,total_worker_time / execution_count [Avg CPU] ,total_worker_time [Total CPU] ,total_elapsed_time [Total Elasped Time] ,last_execution_time [Last Execution Time] FROM sys.dm_exec_query_stats qs JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa WHERE attribute = 'dbid' AND CASE when @DBNAME = '<not supplied>' THEN '<not supplied>' ELSE COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT)) + '*', 'Resource') END IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*') ORDER BY CASE WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count WHEN @ORDERBY = 'TCPU' THEN total_worker_time WHEN @ORDERBY = 'AE' THEN total_elapsed_time / execution_count WHEN @ORDERBY = 'TE' THEN total_elapsed_time WHEN @ORDERBY = 'EC' THEN execution_count WHEN @ORDERBY = 'AIO' THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count WHEN @ORDERBY = 'TIO' THEN total_logical_reads + total_logical_writes + total_physical_reads WHEN @ORDERBY = 'ALR' THEN total_logical_reads / execution_count WHEN @ORDERBY = 'TLR' THEN total_logical_reads WHEN @ORDERBY = 'ALW' THEN total_logical_writes / execution_count WHEN @ORDERBY = 'TLW' THEN total_logical_writes WHEN @ORDERBY = 'APR' THEN total_physical_reads / execution_count WHEN @ORDERBY =

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLCPUTISQL ServerRIMCUUTSUUBAPC
分页:12
转载请注明
本文标题:Finding the Worst Performing TSQL Statement
本站链接:http://www.codesec.net/view/530312.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(51)