1.锁表查询
--查看锁住的表 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' --哪个会话引起阻塞并且它们在运行什么 SELECT DTL.[request_session_id] AS [session_id] , DB_NAME(DTL.[resource_database_id]) AS [Database] , DTL.resource_type , CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' ) THEN DTL.resource_type WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, DTL.[resource_database_id]) WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' ) THEN ( SELECT OBJECT_NAME([object_id]) FROM sys.partitions WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id ) ELSE 'Unidentified' END AS [Parent Object] , DTL.request_mode AS [Lock Type] , DTL.request_status AS [Request Status] , DER.[blocking_session_id] , DES.[login_name] , CASE DTL.request_lifetime WHEN 0 THEN DEST_R.TEXT ELSE DEST_C.TEXT END AS [Statement] FROM sys.dm_tran_locks DTL LEFT JOIN sys.[dm_exec_requests] DER ON DTL.[request_session_id] = DER.[session_id] INNER JOIN sys.dm_exec_sessions DES ON DTL.request_session_id = DES.[session_id] INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id] OUTER APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_C OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_R WHERE DTL.[resource_database_id] = DB_ID() AND DTL.[resource_type] NOT IN ( 'DATABASE', 'METADATA' ) ORDER BY DTL.[request_session_id]; --查看因为单条UPDATE语句锁住的用户表 SELECT [resource_type] , DB_NAME([resource_database_id]) AS [Database Name] , CASE WHEN DTL.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' ) THEN DTL.resource_type WHEN DTL.resource_type = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id, DTL.[resource_database_id]) WHEN DTL.resource_type IN ( 'KEY', 'PAGE', 'RID' ) THEN ( SELECT OBJECT_NAME([object_id]) FROM sys.partitions WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id ) ELSE 'Unidentified' END AS requested_object_name , [request_mode] , [resource_description] FROM sys.dm_tran_locks DTL WHERE DTL.[resource_type] <> 'DATABASE'; --单库中的锁定和阻塞 SELECT DTL.[resource_type] AS [resource type] , CASE WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' ) THEN DTL.[resource_type] WHEN DTL.[resource_type] = 'OBJECT' THEN OBJECT_NAME(DTL.resource_associated_entity_id) WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' ) THEN ( SELECT OBJECT_NAME([object_id]) FROM sys.partitions WHERE sys.partitions.[hobt_id] = DTL.[resource_associated_entity_id] ) ELSE 'Unidentified' END AS [Parent Object] , DTL.[request_mode] AS [Lock Type] , DTL.[request_status] AS [Request Status] , DOWT.[wait_duration_ms] AS [wait duration ms] , DOWT.[wait_type] AS [wait type] , DOWT.[session_id] AS [blocked session id] , DES_blocked.[login_name] AS [blocked_user] , SUBSTRING(dest_blocked.text, der.statement_start_offset / 2, ( CASE WHEN der.statement_end_offset = -1 THEN DATALENGTH(dest_blocked.text) ELSE der.statement_end_offset END - der.statement_start_offset ) / 2) AS [blocked_command] , DOWT.[blocking_session_id] AS [blocking session id] , DES_blocking.[login_name] AS [blocking user] , DEST_blocking.[text] AS [blocking command] , DOWT.resource_description AS [blocking resource detail] FROM sys.dm_tran_locks DTL INNER JOIN sys.dm_os_waiting_tasks DOWT ON DTL.lock_owner_address = DOWT.resource_address INNER JOIN sys.[dm_exec_requests] DER ON DOWT.[session_id] = DER.[session_id] INNER JOIN sys.dm_exec_sessions DES_blocked ON DOWT.[session_id] = DES_Blocked.[session_id] INNER JOIN sys.dm_exec_sessions DES_blocking ON DOWT.[blocking_session_id] = DES_Blocking.[session_id] INNER JOIN sys.dm_exec_connections DEC ON DTL.[request_session_id] = DEC.[most_recent_session_id] CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_Blocking CROSS APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked WHERE DTL.[resource_database_id] = DB_ID() --识别在行级的锁定和阻塞 SELECT '[' + DB_NAME(ddios.[database_id]) + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] , i.[name] AS 'index_name' , ddios.[partition_number] , ddios.[row_lock_count] , ddios.[row_lock_wait_count] , CAST (100.0 * ddios.[row_lock_wait_count] / ( ddios.[row_lock_count] ) AS DECIMAL(5, 2)) AS [%_times_blocked] , ddios.[row_lock_wait_in_ms] , CAST (1.0 * ddios.[row_lock_wait_in_ms] / ddios.[row_lock_wait_count] AS DECIMAL(15, 2)) AS [avg_row_lock_wait_in_ms] FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id] AND i.[index_id] = ddios.[index_id] INNER JOIN sys.objects o ON ddios.[object_id] = o.[object_id] INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID] WHERE ddios.row_lock_wait_count > 0 AND OBJECTPROPERTY(ddios.[object_id], 'IsUserTable') = 1 AND i.[index_id] > 0 ORDER BY ddios.[row_lock_wait_count] DESC , su.[name] , o.[name] , i.[name] --识别闩锁等待 SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddios.[object_id]) + '].[' + OBJECT_NAME(ddios.[object_id]) + ']' AS [object_name] , i.[name] AS index_name , ddios.page_io_latch_wait_count , ddios.page_io_latch_wait_in_ms , ( ddios.page_io_latch_wait_in_ms / ddios.page_io_latch_wait_count ) AS avg_page_io_latch_wait_in_ms FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios INNER JOIN sys.indexes i ON ddios.[object_id] = i.[object_id] AND i.index_id = ddios.index_id WHERE ddios.page_io_latch_wait_count > 0 AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 ORDER BY ddios.page_io_latch_wait_count DESC , avg_page_io_latch_wait_in_ms DESC --识别锁升级 SELECT OBJECT_NAME(ddios.[object_id], ddios.database_id) AS [object_name] , i.name AS index_name , ddios.index_id , ddios.partition_number , ddios.index_lock_promotion_attempt_count , ddios.index_lock_promotion_count , ( ddios.index_lock_promotion_attempt_count / ddios.index_lock_promotion_count ) AS percent_success FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios INNER JOIN sys.indexes i ON ddios.object_id = i.object_id AND ddios.index_id = i.index_id WHERE ddios.index_lock_promotion_count > 0 ORDER BY index_lock_promotion_count DESC; --与锁争用有关的索引 SELECT OBJECT_NAME(ddios.object_id, ddios.database_id) AS object_name , i.name AS index_name , ddios.index_id , ddios.partition_number , ddios.page_lock_wait_count , ddios.page_lock_wait_in_ms , CASE WHEN DDMID.database_id IS NULL THEN 'N' ELSE 'Y' END AS missing_index_identified FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios INNER JOIN sys.indexes i ON ddios.object_id = i.object_id AND ddios.index_id = i.index_id LEFT OUTER JOIN ( SELECT DISTINCT database_id , object_id FROM sys.dm_db_missing_index_details ) AS DDMID ON DDMID.database_id = ddios.database_id AND DDMID.object_id = ddios.object_id WHERE ddios.page_lock_wait_in_ms > 0 ORDER BY ddios.page_lock_wait_count DESC;
2.索引优化
-- 未被使用的索引 SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i.name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE i.index_id NOT IN ( SELECT ddius.index_id FROM sys.dm_db_index_usage_stats AS ddius WHERE ddius.[object_id] = i.[object_id] AND i.index_id = ddius.index_id AND database_id = DB_ID() ) AND o.[type] = 'U' ORDER BY OBJECT_NAME(i.[object_id]) ASC; --需要维护但是未被用过的索引 SELECT '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] , i.[name] AS [index_name] , ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] , ddius.[user_updates] AS [user_writes] , SUM(SP.rows) AS [total_rows] FROM sys.dm_db_index_usage_stats ddius INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id] INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id] INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id] INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID] WHERE ddius.[database_id] = DB_ID() -- current database only AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1 AND ddius.[index_id] > 0 GROUP BY su.[name] , o.[name] , i.[name] , ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] , ddius.[user_updates] HAVING ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0 ORDER BY ddius.[user_updates] DESC , su.[name] , o.[name] , i.[name] -- 可能不高效的非聚集索引 (writes > reads) SELECT OBJECT_NAME(ddius.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] FROM sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id] AND i.index_id = ddius.index_id WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1 AND ddius.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY [Difference] DESC , [Total Writes] DESC , [Total Reads] ASC; --没有用于用户查询的索引 SELECT '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [statement] , i.[name] AS [index_name] , ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] , ddius.[user_updates] AS [user_writes] , ddios.[leaf_insert_count] , ddios.[leaf_delete_count] , ddios.[leaf_update_count] , ddios.[nonleaf_insert_count] , ddios.[nonleaf_delete_count] , ddios.[nonleaf_update_count] FROM sys.dm_db_index_usage_stats ddius INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id] AND i.[index_id] = ddius.[index_id] INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id] AND SP.[index_id] = ddius.[index_id] INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id] INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID] INNER JOIN sys.[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) AS ddios ON ddius.[index_id] = ddios.[index_id] AND ddius.[object_id] = ddios.[object_id] AND SP.[partition_number] = ddios.[partition_number] AND ddius.[database_id] = ddios.[database_id] WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1 AND ddius.[index_id] > 0 AND ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0 ORDER BY ddius.[user_updates] DESC , su.[name] , o.[name] , i.[name] --查找丢失索引 SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] , dbmigs.last_user_seek , dbmid.[statement] AS [Database.Schema.Table] , dbmid.equality_columns , dbmid.inequality_columns , dbmid.included_columns , dbmigs.unique_compiles , dbmigs.user_seeks , dbmigs.avg_total_user_cost , dbmigs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle WHERE dbmid.[database_id] = DB_ID() ORDER BY index_advantage DESC; --索引上的碎片超过15%并且索引体积较大（超过500页）的索引。 SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id], DB_ID()) + '].[' + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] , i.[name] AS [index_name] , ddips.[index_type_desc] , ddips.[partition_number] , ddips.[alloc_unit_type_desc] , ddips.[index_depth] , ddips.[index_level] , CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%] , CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT) AS [avg_frag_size_in_pages] , ddips.[fragment_count] , ddips.[page_count] FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id] AND ddips.[index_id] = i.[index_id] WHERE ddips.[avg_fragmentation_in_percent] > 15 AND ddips.[page_count] > 500 ORDER BY ddips.[avg_fragmentation_in_percent] , OBJECT_NAME(ddips.[object_id], DB_ID()) , i.[name] --缺失索引 SELECT migs.group_handle, mid.* FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON (migs.group_handle = mig.index_group_handle) INNER JOIN sys.dm_db_missing_index_details AS mid ON (mig.index_handle = mid.index_handle) WHERE migs.group_handle = 2 --无用索引 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(O.Schema_ID) AS SchemaName , OBJECT_NAME(I.object_id) AS TableName , I.name AS IndexName INTO #TempNeverUsedIndexes FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempNeverUsedIndexes SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(O.Schema_ID) AS SchemaName , OBJECT_NAME(I.object_id) AS TableName , I.NAME AS IndexName FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id AND I.index_id = S.index_id AND DATABASE_ID = DB_ID() WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 AND I.name IS NOT NULL AND S.object_id IS NULL' SELECT * FROM #TempNeverUsedIndexes ORDER BY DatbaseName, SchemaName, TableName, IndexName DROP TABLE #TempNeverUsedIndexes --经常被大量更新，但是却基本不适用的索引项- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] INTO #TempUnusedIndexes FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC' SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC DROP TABLE #TempUnusedIndexes
3.数据库环境
--查询当前数据库的配置信息 Select configuration_id ConfigurationId, name Name, description Description, Cast(value as int) value, Cast(minimum as int) Minimum, Cast(maximum as int) Maximum, Cast(value_in_use as int) ValueInUse, is_dynamic IsDynamic, is_advanced IsAdvanced From sys.configurations Order By is_advanced, name --检查SQL SERVER 当前已创建的线程数 select count(*) from sys.dm_os_workers --查询当前连接到数据库的用户信息 Select s.login_name LoginName, s.host_name HostName, s.transaction_isolation_level TransactionIsolationLevel, Max(c.connect_time) LastConnectTime, Count(*) ConnectionCount, Sum(Cast(c.num_reads as BigInt)) TotalReads, Sum(Cast(c.num_writes as BigInt)) TotalWrites From sys.dm_exec_connections c Join sys.dm_exec_sessions s On c.most_recent_session_id = s.session_id Group By s.login_name, s.host_name, s.transaction_isolation_level --查询CPU和内存利用率 Select DateAdd(s, (timestamp - (osi.cpu_ticks / Convert(Float, (osi.cpu_ticks / osi.ms_ticks)))) / 1000, GETDATE()) AS EventTime, Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle, Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as ProcessUtilization, Record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'int') as MemoryUtilization From (Select timestamp, convert(xml, record) As Record From sys.dm_os_ring_buffers Where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' And record Like '%<SystemHealth>%') x Cross Join sys.dm_os_sys_info osi Order By timestamp --查看每个数据库缓存大小 SELECT COUNT(*) * 8 / 1024 AS 'Cached Size (MB)' , CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE DB_NAME(database_id) END AS 'Database' FROM sys.dm_os_buffer_descriptors GROUP BY DB_NAME(database_id) , database_id ORDER BY 'Cached Size (MB)' DESC --SQL SERVER 统计IO活动信息 SET STATISTICS IO ON select top 10* from Table SET STATISTICS IO OFF --SQL SERVER 清除缓存SQL语句 CHECKPOINT; GO DBCC FREEPROCCACHE ---清空执行计划缓存 DBCC DROPCLEANBUFFERS; --清空数据缓存 GO --查看当前进程的信息 DBCC INPUTBUFFER(51) --查看当前数据是否启用了快照隔离 DBCC USEROPTIONS; --查看摸个数据库数据表中的数据页类型 --In_Row_Data: 分别为存储行内数据的 --LOB_Data: 存储Lob对象,Lob对象用于存储存在数据库的二进制文件 --当这个类型的列出现时,原有的列会存储一个24字节的指针，而将具体的二进制数据存在LOB页中 --Row_Overflow_data:存储溢出数据的,使用Varchar,nvarchar等数据类型时，当行的大小不超过8060字节时，全部存在行内In-row data --当varchar中存储的数据过多使得整行超过8060字节时，会将额外的部分存于Row-overflow data页中， --如果update这列使得行大小减少到小于8060字节，则这行又会全部回到in-row data页 --text,ntext和image类型来说，每一列只要不为null,即使占用很小的数据，也需要额外分配一个LOB页 DBCC IND ( Lawyer, [dbo.tb_Contract], -1)
4.sql语句
--查询CPU最高的10条SQL SELECT TOP 10 TEXT AS 'SQL Statement' ,last_execution_time AS 'Last Execution Time' ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO] ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)] ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)] ,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes ,qp.query_plan AS "Query Plan" FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_elapsed_time / execution_count DESC --找出执行频繁的语句的SQL语句 with aa as ( SELECT --执行次数 QS.execution_count, --查询语句 SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, ((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 ) AS statement_text, --执行文本 ST.text, --执行计划 qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, QS.total_worker_time, QS.last_worker_time, QS.max_worker_time, QS.min_worker_time FROM sys.dm_exec_query_stats QS --关键字 CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST WHERE QS.last_execution_time > '2016-02-14 00:00:00' and execution_count > 500 -- AND ST.text LIKE '%%' --ORDER BY --QS.execution_count DESC ) select text,max(execution_count) execution_count --,last_elapsed_time,min_elapsed_time,max_elapsed_time from aa where [text] not like '%sp_MSupd_%' and [text] not like '%sp_MSins_%' and [text] not like '%sp_MSdel_%' group by text order by 2 desc -- 查找逻辑读取最高的查询(存储过程) SELECT TOP ( 25 ) P.name AS [SP Name] , Deps.total_logical_reads AS [TotalLogicalReads] , deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] , deps.execution_count , ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time, GETDATE()), 0) AS [Calls/Second] , deps.total_elapsed_time , deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] , deps.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id] WHERE deps.Database_id = DB_ID() ORDER BY deps.total_logical_reads DESC;
PS:快速SQL性能查询
SELECT creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' , execution_count N'执行次数' , total_worker_time/1000 N'所用的CPU总时间ms' , total_elapsed_time/1000 N'总花费时间ms' , (total_elapsed_time / execution_count)/1000 N'平均时间ms' ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) not like '%fetch%' ORDER BY total_elapsed_time / execution_count DESC;