-- 查看数据库中表的语句
SELECT s2.dbid ,
DB_NAME(s2.dbid) AS [数据库名] ,
- --s1.sql_handle ,
- ( SELECT TOP 1
- SUBSTRING(s2.text, statement_start_offset / 2 + 1,
- ( ( CASE WHEN statement_end_offset = -1
- THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
- * 2 )
- ELSE statement_end_offset
- END ) - statement_start_offset ) / 2 + 1)
) AS [语句] ,
execution_count AS [执行次数] ,
last_execution_time AS [上次开始执行计划的时间] ,
total_worker_time AS [自编译以来执行所用的 CPU 时间总量 (微秒)] ,
last_worker_time AS [上次执行计划所用的 CPU 时间 (微秒)] ,
min_worker_time AS [单次执行期间曾占用的最小 CPU 时间 (微秒)] ,
max_worker_time AS [单次执行期间曾占用的最大 CPU 时间 (微秒)] ,
total_logical_reads AS [总逻辑读] ,
last_logical_reads AS [上次逻辑读] ,
min_logical_reads AS [最少逻辑读] ,
max_logical_reads AS [最大逻辑读] ,
total_logical_writes AS [总逻辑写] ,
last_logical_writes AS [上次逻辑写] ,
min_logical_writes AS [最小逻辑写] ,
max_logical_writes AS [最大逻辑写]
- FROM sys.dm_exec_query_stats AS s1
- CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
- WHERE s2.objectid IS NULL
- ORDER BY last_worker_time DESC
-- 查看存储过程执行的语句
SELECT
DB_NAME(ISNULL(EPS.database_id,'')) [数据库名称]
--ISNULL(DBS.name, '') AS DatabaseName
,OBJECT_NAME(EPS.object_id, EPS.database_id) [存储过程名称] --AS ObjectName
,EPS.cached_time [添加到缓存的时间]--AS CachedTime
- ,EPS.last_elapsed_time '最近执行所耗费时间 (微秒)'--AS LastElapsedTime
- ,EPS.last_worker_time '上次执行存储过程所用的 CPU 时间 (微秒)'
,EPS.execution_count [上次编译以来所执行的次数]--AS ExecutionCount
,EPS.total_worker_time / EPS.execution_count [平均每次执行所用的 CPU 时间总量 (微秒)]--AS AvgWorkerTime
,EPS.total_elapsed_time / EPS.execution_count [平均每次执行所用的时间 (微秒)]--AS AvgElapsedTime
- ,(EPS.total_logical_reads + EPS.total_logical_writes)
- / EPS.execution_count AS AvgLogicalIO
,b.text [存储过程内容]
- FROM sys.dm_exec_procedure_stats AS EPS
- CROSS APPLY sys.dm_exec_sql_text(EPS.sql_handle) b
- ORDER BY EPS.last_elapsed_time DESC;
-- 展示耗时的查询
- 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
-- 当前进程及其语句:
- SELECT PRO.loginame AS LoginName
- ,DB.name AS DatabaseName
- ,PRO.[status] as ProcessStatus
- ,PRO.cmd AS Command
- ,PRO.last_batch AS LastBatch
- ,PRO.CPU AS CPU
- ,PRO.physical_io AS PhysicalIo
- ,SES.row_count AS [RowCount]
- ,STM.[text] AS SQLStatement
- FROM sys.sysprocesses AS PRO
- INNER JOIN sys.databases AS DB
- ON PRO.dbid = DB.database_id
- INNER JOIN sys.dm_exec_sessions AS SES
- ON PRO.spid = SES.session_id
- CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM
- WHERE PRO.spid>= 50 -- Exclude system processes
- ORDER BY PRO.physical_io DESC ,PRO.CPU DESC;
--5, 开销较大的查询:
- SELECT ss.SUM_execution_count ,
- t.text ,
- ss.SUM_total_elapsed_time ,
- ss.sum_total_worker_time ,
- ss.sum_total_logical_reads ,
- ss.sum_total_logical_writes
- FROM ( SELECT S.plan_handle ,
- SUM(s.execution_count) SUM_Execution_count ,
- SUM(s.total_elapsed_time) SUM_total_elapsed_time ,
- SUM(s.total_worker_time) SUM_total_worker_time ,
- SUM(s.total_logical_reads) SUM_total_logical_reads ,
- SUM(s.total_logical_writes) SUM_total_logical_writes
- FROM sys.dm_exec_query_stats s
- GROUP BY S.plan_handle
- ) AS ss
- CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
- ORDER BY sum_total_logical_reads DESC
来源: http://www.bubuko.com/infodetail-3350417.html