SQLSERVER数据库调优

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],‘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‘,‘METADATA‘ )
             THEN DTL.[resource_type]
             WHEN DTL.[resource_type] = ‘OBJECT‘
             THEN OBJECT_NAME(DTL.resource_associated_entity_id)
             WHEN DTL.[resource_type] IN ( ‘KEY‘,‘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) 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) 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) 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,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) 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],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,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],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) 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,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],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(),‘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.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,((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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!