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 "%% ") 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统计IO活动信息：
SET STATISTICS IO ON
select top 10* from Table
SET STATISTICS IO OFF清除缓存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;查看某个SQL的执行计划：
SET STATISTICS PROFILE ON
SELECT * FROM Demo
SET STATISTICS PROFILE OFF查询某个SQL的执行时间：
SET STATISTICS Time ON
SELECT * FROM Demo
SET STATISTICS TIME OFF查询某个SQL的IO信息：
SET STATISTICS IO ON
SELECT * FROM Demo
SET STATISTICS IO OFF
5、开源监控脚本，监控数据库锁定情况
USE master
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = "sp_WhoIsActive")
EXEC ("CREATE PROC dbo.sp_WhoIsActive AS SELECT ""stub version, to be replaced""")
GO
ALTER PROC dbo.sp_WhoIsActive
(
--~
--Filters--Both inclusive and exclusive
--Set either filter to "" to disable
--Valid filter types are: session, program, database, login, and host
--Session is a session ID, and either 0 or "" can be used to indicate "all" sessions
--All other filter types support % or _ as wildcards
@filter sysname = "",
@filter_type VARCHAR(10) = "session",
@not_filter sysname = "",
@not_filter_type VARCHAR(10) = "session",
--Retrieve data about the calling session?
@show_own_spid BIT = 0,
--Retrieve data about system sessions?
@show_system_spids BIT = 0,
--Controls how sleeping SPIDs are handled, based on the idea of levels of interest
--0 does not pull any sleeping SPIDs
--1 pulls only those sleeping SPIDs that also have an open transaction
--2 pulls all sleeping SPIDs
@show_sleeping_spids TINYINT = 1,
--If 1, gets the full stored procedure or running batch, when available
--If 0, gets only the actual statement that is currently running in the batch or procedure
@get_full_inner_text BIT = 0,
--Get associated query plans for running tasks, if available
--If @get_plans = 1, gets the plan based on the request"s statement offset
--If @get_plans = 2, gets the entire plan based on the request"s plan_handle
@get_plans TINYINT = 0,
--Get the associated outer ad hoc query or stored procedure call, if available
@get_outer_command BIT = 0,
--Enables pulling transaction log write info and transaction duration
@get_transaction_info BIT = 0,
--Get information on active tasks, based on three interest levels
--Level 0 does not pull any task-related information
--Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
--Level 2 pulls all available task-based metrics, including:
--number of active tasks, current wait stats, physical I/O, context switches, and blocker information
@get_task_info TINYINT = 1,
--Gets associated locks for each request, aggregated in an XML format
@get_locks BIT = 0,
--Get average time for past runs of an active query
--(based on the combination of plan handle, sql handle, and offset)
@get_avg_time BIT = 0,
--Get additional non-performance-related information about the session or request
--text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,
--ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,
--transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type
--
--If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of
--the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)
--
--If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be
--populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,
--applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
@get_additional_info BIT = 0,
--Walk the blocking chain and count the number of
--total SPIDs blocked all the way down by a given session
--Also enables task_info Level 1, if @get_task_info is set to 0
@find_block_leaders BIT = 0,
--Pull deltas on various metrics
--Interval in seconds to wait before doing the second data pull
@delta_interval TINYINT = 0,
--List of desired output columns, in desired order
--Note that the final output will be the intersection of all enabled features and all
--columns in the list. Therefore, only columns associated with enabled features will
--actually appear in the output. Likewise, removing columns from this list may effectively
--disable features, even if they are turned on
--
--Each element in this list must be one of the valid output column names. Names must be
--delimited by square brackets. White space, formatting, and additional characters are
--allowed, as long as the list contains exact matches of delimited valid column names.
@output_column_list VARCHAR(8000) = "[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]",
--Column(s) by which to sort output, optionally with sort directions.
--Valid column choices:
--session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
--tempdb_current, CPU, context_switches, used_memory, physical_io_delta,
--reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta,
--tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta,
--tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,
--percent_complete, host_name, login_name, database_name, start_time, login_time
--
--Note that column names in the list must be bracket-delimited. Commas and/or white
--space are not required.
@sort_order VARCHAR(500) = "[start_time] ASC",
--Formats some of the output columns in a more "human readable" form
--0 disables outfput format
--1 formats the output for variable-width fonts
--2 formats the output for fixed-width fonts
@format_output TINYINT = 1,
--If set to a non-blank value, the script will attempt to insert into the specified
--destination table. Please note that the script will not verify that the table exists,
--or that it has the correct schema, before doing the insert.
--Table can be specified in one, two, or three-part format
@destination_table VARCHAR(4000) = "",
--If set to 1, no data collection will happen and no result set will be returned; instead,
--a CREATE TABLE statement will be returned via the @schema parameter, which will match
--the schema of the result set that would be returned by using the same collection of the
--rest of the parameters. The CREATE TABLE statement will have a placeholder token of
-- in place of an actual table name.
@return_schema BIT = 0,
@schema VARCHAR(MAX) = NULL OUTPUT,
--Help! What do I do?
@help BIT = 0
--~
)
AS
BEGIN;
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON;
SET NUMERIC_ROUNDABORT OFF;
SET ARITHABORT ON;
IF
@filter IS NULL
OR @filter_type IS NULL
OR @not_filter IS NULL
OR @not_filter_type IS NULL
OR @show_own_spid IS NULL
OR @show_system_spids IS NULL
OR @show_sleeping_spids IS NULL
OR @get_full_inner_text IS NULL
OR @get_plans IS NULL
OR @get_outer_command IS NULL
OR @get_transaction_info IS NULL
OR @get_task_info IS NULL
OR @get_locks IS NULL
OR @get_avg_time IS NULL
OR @get_additional_info IS NULL
OR @find_block_leaders IS NULL
OR @delta_interval IS NULL
OR @format_output IS NULL
OR @output_column_list IS NULL
OR @sort_order IS NULL
OR @return_schema IS NULL
OR @destination_table IS NULL
OR @help IS NULL
BEGIN;
RAISERROR("Input parameters cannot be NULL", 16, 1);
RETURN;
END;
IF @filter_type NOT IN ("session", "program", "database", "login", "host")
BEGIN;
RAISERROR("Valid filter types are: session, program, database, login, host", 16, 1);
RETURN;
END;
IF @filter_type = "session" AND @filter LIKE "%[^0123456789]%"
BEGIN;
RAISERROR("Session filters must be valid integers", 16, 1);
RETURN;
END;
IF @not_filter_type NOT IN ("session", "program", "database", "login", "host")
BEGIN;
RAISERROR("Valid filter types are: session, program, database, login, host", 16, 1);
RETURN;
END;
IF @not_filter_type = "session" AND @not_filter LIKE "%[^0123456789]%"
BEGIN;
RAISERROR("Session filters must be valid integers", 16, 1);
RETURN;
END;
IF @show_sleeping_spids NOT IN (0, 1, 2)
BEGIN;
RAISERROR("Valid values for @show_sleeping_spids are: 0, 1, or 2", 16, 1);
RETURN;
END;
IF @get_plans NOT IN (0, 1, 2)
BEGIN;
RAISERROR("Valid values for @get_plans are: 0, 1, or 2", 16, 1);
RETURN;
END;
IF @get_task_info NOT IN (0, 1, 2)
BEGIN;
RAISERROR("Valid values for @get_task_info are: 0, 1, or 2", 16, 1);
RETURN;
END;
IF @format_output NOT IN (0, 1, 2)
BEGIN;
RAISERROR("Valid values for @format_output are: 0, 1, or 2", 16, 1);
RETURN;
END;
IF @help = 1
BEGIN;
DECLARE
@header VARCHAR(MAX),
@params VARCHAR(MAX),
@outputs VARCHAR(MAX);
SELECT
@header =
REPLACE
(
REPLACE
(
CONVERT
(
VARCHAR(MAX),
SUBSTRING
(
t.text,
CHARINDEX("/" + REPLICATE("*", 93), t.text) + 94,
CHARINDEX(REPLICATE("*", 93) + "/", t.text) - (CHARINDEX("/" + REPLICATE("*", 93), t.text) + 94)
)
),
CHAR(13)+CHAR(10),
CHAR(13)
),
" ",
""
),
@params =
CHAR(13) +
REPLACE
(
REPLACE
(
CONVERT
(
VARCHAR(MAX),
SUBSTRING
(
t.text,
CHARINDEX("--~", t.text) + 5,
CHARINDEX("--~", t.text, CHARINDEX("--~", t.text) + 5) - (CHARINDEX("--~", t.text) + 5)
)
),
CHAR(13)+CHAR(10),
CHAR(13)
),
" ",
""
),
@outputs =
CHAR(13) +
REPLACE
(
REPLACE
(
REPLACE
(
CONVERT
(
VARCHAR(MAX),
SUBSTRING
(
t.text,
CHARINDEX("OUTPUT COLUMNS"+CHAR(13)+CHAR(10)+"--------------", t.text) + 32,
CHARINDEX("*/", t.text, CHARINDEX("OUTPUT COLUMNS"+CHAR(13)+CHAR(10)+"--------------", t.text) + 32) - (CHARINDEX("OUTPUT COLUMNS"+CHAR(13)+CHAR(10)+"--------------", t.text) + 32)
)
),
CHAR(9),
CHAR(255)
),
CHAR(13)+CHAR(10),
CHAR(13)
),
" ",
""
) +
CHAR(13)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE
r.session_id = @@SPID;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@header) - 1)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
)
SELECT
RTRIM(LTRIM(
SUBSTRING
(
@header,
number + 1,
CHARINDEX(CHAR(13), @header, number + 1) - number - 1
)
)) AS [------header---------------------------------------------------------------------------------------------------------------]
FROM numbers
WHERE
SUBSTRING(@header, number, 1) = CHAR(13);
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@params) - 1)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
RTRIM(LTRIM(
SUBSTRING
(
@params,
number + 1,
CHARINDEX(CHAR(13), @params, number + 1) - number - 1
)
)) AS token,
number,
CASE
WHEN SUBSTRING(@params, number + 1, 1) = CHAR(13) THEN number
ELSE COALESCE(NULLIF(CHARINDEX("," + CHAR(13) + CHAR(13), @params, number), 0), LEN(@params))
END AS param_group,
ROW_NUMBER() OVER
(
PARTITION BY
CHARINDEX("," + CHAR(13) + CHAR(13), @params, number),
SUBSTRING(@params, number+1, 1)
ORDER BY
number
) AS group_order
FROM numbers
WHERE
SUBSTRING(@params, number, 1) = CHAR(13)
),
parsed_tokens AS
(
SELECT
MIN
(
CASE
WHEN token LIKE "@%" THEN token
ELSE NULL
END
) AS parameter,
MIN
(
CASE
WHEN token LIKE "--%" THEN RIGHT(token, LEN(token) - 2)
ELSE NULL
END
) AS description,
param_group,
group_order
FROM tokens
WHERE
NOT
(
token = ""
AND group_order > 1
)
GROUP BY
param_group,
group_order
)
SELECT
CASE
WHEN description IS NULL AND parameter IS NULL THEN "-------------------------------------------------------------------------"
WHEN param_group = MAX(param_group) OVER() THEN parameter
ELSE COALESCE(LEFT(parameter, LEN(parameter) - 1), "")
END AS [------parameter----------------------------------------------------------],
CASE
WHEN description IS NULL AND parameter IS NULL THEN "----------------------------------------------------------------------------------------------------------------------"
ELSE COALESCE(description, "")
END AS [------description-----------------------------------------------------------------------------------------------------]
FROM parsed_tokens
ORDER BY
param_group,
group_order;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@outputs) - 1)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
RTRIM(LTRIM(
SUBSTRING
(
@outputs,
number + 1,
CASE
WHEN
COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs)) <
COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs))
THEN COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs)) - number - 1
ELSE
COALESCE(NULLIF(CHARINDEX(CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2, @outputs, number + 1), 0), LEN(@outputs)) - number - 1
END
)
)) AS token,
number,
COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs)) AS output_group,
ROW_NUMBER() OVER
(
PARTITION BY
COALESCE(NULLIF(CHARINDEX(CHAR(13) + "Formatted", @outputs, number + 1), 0), LEN(@outputs))
ORDER BY
number
) AS output_group_order
FROM numbers
WHERE
SUBSTRING(@outputs, number, 10) = CHAR(13) + "Formatted"
OR SUBSTRING(@outputs, number, 2) = CHAR(13) + CHAR(255) COLLATE Latin1_General_Bin2
),
output_tokens AS
(
SELECT
*,
CASE output_group_order
WHEN 2 THEN MAX(CASE output_group_order WHEN 1 THEN token ELSE NULL END) OVER (PARTITION BY output_group)
ELSE ""
END COLLATE Latin1_General_Bin2 AS column_info
FROM tokens
)
SELECT
CASE output_group_order
WHEN 1 THEN "-----------------------------------"
WHEN 2 THEN
CASE
WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+1, CHARINDEX("]", column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info))
ELSE
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2, CHARINDEX("]", column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)+2) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info)-1)
END
ELSE ""
END AS formatted_column_name,
CASE output_group_order
WHEN 1 THEN "-----------------------------------"
WHEN 2 THEN
CASE
WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN
SUBSTRING(column_info, CHARINDEX("]", column_info)+2, LEN(column_info))
ELSE
SUBSTRING(column_info, CHARINDEX("]", column_info)+2, CHARINDEX("Non-Formatted:", column_info, CHARINDEX("]", column_info)+2) - CHARINDEX("]", column_info)-3)
END
ELSE ""
END AS formatted_column_type,
CASE output_group_order
WHEN 1 THEN "---------------------------------------"
WHEN 2 THEN
CASE
WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN ""
ELSE
CASE
WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1, 1) = "<" THEN
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1, CHARINDEX(">", column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info)))
ELSE
SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1, CHARINDEX("]", column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1) - CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info)))
END
END
ELSE ""
END AS unformatted_column_name,
CASE output_group_order
WHEN 1 THEN "---------------------------------------"
WHEN 2 THEN
CASE
WHEN CHARINDEX("Formatted/Non:", column_info) = 1 THEN ""
ELSE
CASE
WHEN SUBSTRING(column_info, CHARINDEX(CHAR(255) COLLATE Latin1_General_Bin2, column_info, CHARINDEX("Non-Formatted:", column_info))+1, 1) = "<" THEN ""
ELSE
SUBSTRING(column_info, CHARINDEX("]", column_info, CHARINDEX("Non-Formatted:", column_info))+2, CHARINDEX("Non-Formatted:", column_info, CHARINDEX("]", column_info)+2) - CHARINDEX("]", column_info)-3)
END
END
ELSE ""
END AS unformatted_column_type,
CASE output_group_order
WHEN 1 THEN "----------------------------------------------------------------------------------------------------------------------"
ELSE REPLACE(token, CHAR(255) COLLATE Latin1_General_Bin2, "")
END AS [------description-----------------------------------------------------------------------------------------------------]
FROM output_tokens
WHERE
NOT
(
output_group_order = 1
AND output_group = LEN(@outputs)
)
ORDER BY
output_group,
CASE output_group_order
WHEN 1 THEN 99
ELSE output_group_order
END;
RETURN;
END;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@output_column_list))
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
"|[" +
SUBSTRING
(
@output_column_list,
number + 1,
CHARINDEX("]", @output_column_list, number) - number - 1
) + "|]" AS token,
number
FROM numbers
WHERE
SUBSTRING(@output_column_list, number, 1) = "["
),
ordered_columns AS
(
SELECT
x.column_name,
ROW_NUMBER() OVER
(
PARTITION BY
x.column_name
ORDER BY
tokens.number,
x.default_order
) AS r,
ROW_NUMBER() OVER
(
ORDER BY
tokens.number,
x.default_order
) AS s
FROM tokens
JOIN
(
SELECT "[session_id]" AS column_name, 1 AS default_order
UNION ALL
SELECT "[dd hh:mm:ss.mss]", 2
WHERE
@format_output IN (1, 2)
UNION ALL
SELECT "[dd hh:mm:ss.mss (avg)]", 3
WHERE
@format_output IN (1, 2)
AND @get_avg_time = 1
UNION ALL
SELECT "[avg_elapsed_time]", 4
WHERE
@format_output = 0
AND @get_avg_time = 1
UNION ALL
SELECT "[physical_io]", 5
WHERE
@get_task_info = 2
UNION ALL
SELECT "[reads]", 6
UNION ALL
SELECT "[physical_reads]", 7
UNION ALL
SELECT "[writes]", 8
UNION ALL
SELECT "[tempdb_allocations]", 9
UNION ALL
SELECT "[tempdb_current]", 10
UNION ALL
SELECT "[CPU]", 11
UNION ALL
SELECT "[context_switches]", 12
WHERE
@get_task_info = 2
UNION ALL
SELECT "[used_memory]", 13
UNION ALL
SELECT "[physical_io_delta]", 14
WHERE
@delta_interval > 0
AND @get_task_info = 2
UNION ALL
SELECT "[reads_delta]", 15
WHERE
@delta_interval > 0
UNION ALL
SELECT "[physical_reads_delta]", 16
WHERE
@delta_interval > 0
UNION ALL
SELECT "[writes_delta]", 17
WHERE
@delta_interval > 0
UNION ALL
SELECT "[tempdb_allocations_delta]", 18
WHERE
@delta_interval > 0
UNION ALL
SELECT "[tempdb_current_delta]", 19
WHERE
@delta_interval > 0
UNION ALL
SELECT "[CPU_delta]", 20
WHERE
@delta_interval > 0
UNION ALL
SELECT "[context_switches_delta]", 21
WHERE
@delta_interval > 0
AND @get_task_info = 2
UNION ALL
SELECT "[used_memory_delta]", 22
WHERE
@delta_interval > 0
UNION ALL
SELECT "[tasks]", 23
WHERE
@get_task_info = 2
UNION ALL
SELECT "[status]", 24
UNION ALL
SELECT "[wait_info]", 25
WHERE
@get_task_info > 0
OR @find_block_leaders = 1
UNION ALL
SELECT "[locks]", 26
WHERE
@get_locks = 1
UNION ALL
SELECT "[tran_start_time]", 27
WHERE
@get_transaction_info = 1
UNION ALL
SELECT "[tran_log_writes]", 28
WHERE
@get_transaction_info = 1
UNION ALL
SELECT "[open_tran_count]", 29
UNION ALL
SELECT "[sql_command]", 30
WHERE
@get_outer_command = 1
UNION ALL
SELECT "[sql_text]", 31
UNION ALL
SELECT "[query_plan]", 32
WHERE
@get_plans >= 1
UNION ALL
SELECT "[blocking_session_id]", 33
WHERE
@get_task_info > 0
OR @find_block_leaders = 1
UNION ALL
SELECT "[blocked_session_count]", 34
WHERE
@find_block_leaders = 1
UNION ALL
SELECT "[percent_complete]", 35
UNION ALL
SELECT "[host_name]", 36
UNION ALL
SELECT "[login_name]", 37
UNION ALL
SELECT "[database_name]", 38
UNION ALL
SELECT "[program_name]", 39
UNION ALL
SELECT "[additional_info]", 40
WHERE
@get_additional_info = 1
UNION ALL
SELECT "[start_time]", 41
UNION ALL
SELECT "[login_time]", 42
UNION ALL
SELECT "[request_id]", 43
UNION ALL
SELECT "[collection_time]", 44
) AS x ON
x.column_name LIKE token ESCAPE "|"
)
SELECT
@output_column_list =
STUFF
(
(
SELECT
"," + column_name as [text()]
FROM ordered_columns
WHERE
r = 1
ORDER BY
s
FOR XML
PATH("")
),
1,
1,
""
);
IF COALESCE(RTRIM(@output_column_list), "") = ""
BEGIN;
RAISERROR("No valid column matches found in @output_column_list or no columns remain due to selected options.", 16, 1);
RETURN;
END;
IF @destination_table <> ""
BEGIN;
SET @destination_table =
--database
COALESCE(QUOTENAME(PARSENAME(@destination_table, 3)) + ".", "") +
--schema
COALESCE(QUOTENAME(PARSENAME(@destination_table, 2)) + ".", "") +
--table
COALESCE(QUOTENAME(PARSENAME(@destination_table, 1)), "");
IF COALESCE(RTRIM(@destination_table), "") = ""
BEGIN;
RAISERROR("Destination table not properly formatted.", 16, 1);
RETURN;
END;
END;
WITH
a0 AS
(SELECT 1 AS n UNION ALL SELECT 1),
a1 AS
(SELECT 1 AS n FROM a0 AS a, a0 AS b),
a2 AS
(SELECT 1 AS n FROM a1 AS a, a1 AS b),
a3 AS
(SELECT 1 AS n FROM a2 AS a, a2 AS b),
a4 AS
(SELECT 1 AS n FROM a3 AS a, a3 AS b),
numbers AS
(
SELECT TOP(LEN(@sort_order))
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS number
FROM a4
ORDER BY
number
),
tokens AS
(
SELECT
"|[" +
SUBSTRING
(
@sort_order,
number + 1,
CHARINDEX("]", @sort_order, number) - number - 1
) + "|]" AS token,
SUBSTRING
(
@sort_order,
CHARINDEX("]", @sort_order, number) + 1,
COALESCE(NULLIF(CHARINDEX("[", @sort_order, CHARINDEX("]", @sort_order, number)), 0), LEN(@sort_order)) - CHARINDEX("]", @sort_order, number)
) AS next_chunk,
number
FROM numbers
WHERE
SUBSTRING(@sort_order, number, 1) = "["
),
ordered_columns AS
(
SELECT
x.column_name +
CASE
WHEN tokens.next_chunk LIKE "%asc%" THEN " ASC"
WHEN tokens.next_chunk LIKE "%desc%" THEN " DESC"
ELSE ""
END AS column_name,
ROW_NUMBER() OVER
(
PARTITION BY
x.column_name
ORDER BY
tokens.number
) AS r,
tokens.number
FROM tokens
JOIN
(
SELECT "[session_id]" AS column_name
UNION ALL
SELECT "[physical_io]"
UNION ALL
SELECT "[reads]"
UNION ALL
SELECT "[physical_reads]"
UNION ALL
SELECT "[writes]"
UNION ALL
SELECT "[tempdb_allocations]"
UNION ALL
SELECT "[tempdb_current]"
UNION ALL
SELECT "[CPU]"
UNION ALL
SELECT "[context_switches]"
UNION ALL
SELECT "[used_memory]"
UNION ALL
SELECT "[physical_io_delta]"
UNION ALL
SELECT "[reads_delta]"
UNION ALL
SELECT "[physical_reads_delta]"
UNION ALL
SELECT "[writes_delta]"
UNION ALL
SELECT "[tempdb_allocations_delta]"
UNION ALL
SELECT "[tempdb_current_delta]"
UNION ALL
SELECT "[CPU_delta]"
UNION ALL
SELECT "[context_switches_delta]"
UNION ALL
SELECT "[used_memory_delta]"
UNION ALL
SELECT "[tasks]"
UNION ALL
SELECT "[tran_start_time]"
UNION ALL
SELECT "[open_tran_count]"
UNION ALL
SELECT "[blocking_session_id]"
UNION ALL
SELECT "[blocked_session_count]"
UNION ALL
SELECT "[percent_complete]"
UNION ALL
SELECT "[host_name]"
UNION ALL
SELECT "[login_name]"
UNION ALL
SELECT "[database_name]"
UNION ALL
SELECT "[start_time]"
UNION ALL
SELECT "[login_time]"
) AS x ON
x.column_name LIKE token ESCAPE "|"
)
SELECT
@sort_order = COALESCE(z.sort_order, "")
FROM
(
SELECT
STUFF
(
(
SELECT
"," + column_name as [text()]
FROM ordered_columns
WHERE
r = 1
ORDER BY
number
FOR XML
PATH("")
),
1,
1,
""
) AS sort_order
) AS z;
CREATE TABLE #sessions
(
recursion SMALLINT NOT NULL,
session_id SMALLINT NOT NULL,
request_id INT NOT NULL,
session_number INT NOT NULL,
elapsed_time INT NOT NULL,
avg_elapsed_time INT NULL,
physical_io BIGINT NULL,
reads BIGINT NULL,
physical_reads BIGINT NULL,
writes BIGINT NULL,
tempdb_allocations BIGINT NULL,
tempdb_current BIGINT NULL,
CPU INT NULL,
thread_CPU_snapshot BIGINT NULL,
context_switches BIGINT NULL,
used_memory BIGINT NOT NULL,
tasks SMALLINT NULL,
status VARCHAR(30) NOT NULL,
wait_info NVARCHAR(4000) NULL,
locks XML NULL,
transaction_id BIGINT NULL,
tran_start_time DATETIME NULL,
tran_log_writes NVARCHAR(4000) NULL,
open_tran_count SMALLINT NULL,
sql_command XML NULL,
sql_handle VARBINARY(64) NULL,
statement_start_offset INT NULL,
statement_end_offset INT NULL,
sql_text XML NULL,
plan_handle VARBINARY(64) NULL,
query_plan XML NULL,
blocking_session_id SMALLINT NULL,
blocked_session_count SMALLINT NULL,
percent_complete REAL NULL,
host_name sysname NULL,
login_name sysname NOT NULL,
database_name sysname NULL,
program_name sysname NULL,
additional_info XML NULL,
start_time DATETIME NOT NULL,
login_time DATETIME NULL,
last_request_start_time DATETIME NULL,
PRIMARY KEY CLUSTERED (session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON),
UNIQUE NONCLUSTERED (transaction_id, session_id, request_id, recursion) WITH (IGNORE_DUP_KEY = ON)
);
IF @return_schema = 0
BEGIN;
--Disable unnecessary autostats on the table
CREATE STATISTICS s_session_id ON #sessions (session_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_id ON #sessions (request_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_transaction_id ON #sessions (transaction_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_session_number ON #sessions (session_number)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_status ON #sessions (status)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_start_time ON #sessions (start_time)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_last_request_start_time ON #sessions (last_request_start_time)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_recursion ON #sessions (recursion)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
DECLARE @recursion SMALLINT;
SET @recursion =
CASE @delta_interval
WHEN 0 THEN 1
ELSE -1
END;
DECLARE @first_collection_ms_ticks BIGINT;
DECLARE @last_collection_start DATETIME;
--Used for the delta pull
REDO:;
IF
@get_locks = 1
AND @recursion = 1
AND @output_column_list LIKE "%|[locks|]%" ESCAPE "|"
BEGIN;
SELECT
y.resource_type,
y.database_name,
y.object_id,
y.file_id,
y.page_type,
y.hobt_id,
y.allocation_unit_id,
y.index_id,
y.schema_id,
y.principal_id,
y.request_mode,
y.request_status,
y.session_id,
y.resource_description,
y.request_count,
s.request_id,
s.start_time,
CONVERT(sysname, NULL) AS object_name,
CONVERT(sysname, NULL) AS index_name,
CONVERT(sysname, NULL) AS schema_name,
CONVERT(sysname, NULL) AS principal_name,
CONVERT(NVARCHAR(2048), NULL) AS query_error
INTO #locks
FROM
(
SELECT
sp.spid AS session_id,
CASE sp.status
WHEN "sleeping" THEN CONVERT(INT, 0)
ELSE sp.request_id
END AS request_id,
CASE sp.status
WHEN "sleeping" THEN sp.last_batch
ELSE COALESCE(req.start_time, sp.last_batch)
END AS start_time,
sp.dbid
FROM sys.sysprocesses AS sp
OUTER APPLY
(
SELECT TOP(1)
CASE
WHEN
(
sp.hostprocess > ""
OR r.total_elapsed_time < 0
) THEN
r.start_time
ELSE
DATEADD
(
ms,
1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
)
END AS start_time
FROM sys.dm_exec_requests AS r
WHERE
r.session_id = sp.spid
AND r.request_id = sp.request_id
) AS req
WHERE
--Process inclusive filter
1 =
CASE
WHEN @filter <> "" THEN
CASE @filter_type
WHEN "session" THEN
CASE
WHEN
CONVERT(SMALLINT, @filter) = 0
OR sp.spid = CONVERT(SMALLINT, @filter)
THEN 1
ELSE 0
END
WHEN "program" THEN
CASE
WHEN sp.program_name LIKE @filter THEN 1
ELSE 0
END
WHEN "login" THEN
CASE
WHEN sp.loginame LIKE @filter THEN 1
ELSE 0
END
WHEN "host" THEN
CASE
WHEN sp.hostname LIKE @filter THEN 1
ELSE 0
END
WHEN "database" THEN
CASE
WHEN DB_NAME(sp.dbid) LIKE @filter THEN 1
ELSE 0
END
ELSE 0
END
ELSE 1
END
--Process exclusive filter
AND 0 =
CASE
WHEN @not_filter <> "" THEN
CASE @not_filter_type
WHEN "session" THEN
CASE
WHEN sp.spid = CONVERT(SMALLINT, @not_filter) THEN 1
ELSE 0
END
WHEN "program" THEN
CASE
WHEN sp.program_name LIKE @not_filter THEN 1
ELSE 0
END
WHEN "login" THEN
CASE
WHEN sp.loginame LIKE @not_filter THEN 1
ELSE 0
END
WHEN "host" THEN
CASE
WHEN sp.hostname LIKE @not_filter THEN 1
ELSE 0
END
WHEN "database" THEN
CASE
WHEN DB_NAME(sp.dbid) LIKE @not_filter THEN 1
ELSE 0
END
ELSE 0
END
ELSE 0
END
AND
(
@show_own_spid = 1
OR sp.spid <> @@SPID
)
AND
(
@show_system_spids = 1
OR sp.hostprocess > ""
)
AND sp.ecid = 0
) AS s
INNER HASH JOIN
(
SELECT
x.resource_type,
x.database_name,
x.object_id,
x.file_id,
CASE
WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN "PFS"
WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN "GAM"
WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN "SGAM"
WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN "DCM"
WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN "BCM"
WHEN x.page_no IS NOT NULL THEN "*"
ELSE NULL
END AS page_type,
x.hobt_id,
x.allocation_unit_id,
x.index_id,
x.schema_id,
x.principal_id,
x.request_mode,
x.request_status,
x.session_id,
x.request_id,
CASE
WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, "")
ELSE NULL
END AS resource_description,
COUNT(*) AS request_count
FROM
(
SELECT
tl.resource_type +
CASE
WHEN tl.resource_subtype = "" THEN ""
ELSE "." + tl.resource_subtype
END AS resource_type,
COALESCE(DB_NAME(tl.resource_database_id), N"(null)") AS database_name,
CONVERT
(
INT,
CASE
WHEN tl.resource_type = "OBJECT" THEN tl.resource_associated_entity_id
WHEN tl.resource_description LIKE "%object_id = %" THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX("object_id = ", tl.resource_description) + 12),
COALESCE
(
NULLIF
(
CHARINDEX(",", tl.resource_description, CHARINDEX("object_id = ", tl.resource_description) + 12),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX("object_id = ", tl.resource_description) + 12)
)
)
ELSE NULL
END
) AS object_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_type = "FILE" THEN CONVERT(INT, tl.resource_description)
WHEN tl.resource_type IN ("PAGE", "EXTENT", "RID") THEN LEFT(tl.resource_description, CHARINDEX(":", tl.resource_description)-1)
ELSE NULL
END
) AS file_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_type IN ("PAGE", "EXTENT", "RID") THEN
SUBSTRING
(
tl.resource_description,
CHARINDEX(":", tl.resource_description) + 1,
COALESCE
(
NULLIF
(
CHARINDEX(":", tl.resource_description, CHARINDEX(":", tl.resource_description) + 1),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX(":", tl.resource_description) + 1)
)
ELSE NULL
END
) AS page_no,
CASE
WHEN tl.resource_type IN ("PAGE", "KEY", "RID", "HOBT") THEN tl.resource_associated_entity_id
ELSE NULL
END AS hobt_id,
CASE
WHEN tl.resource_type = "ALLOCATION_UNIT" THEN tl.resource_associated_entity_id
ELSE NULL
END AS allocation_unit_id,
CONVERT
(
INT,
CASE
WHEN
tl.resource_subtype <> "SERVER_PRINCIPAL"
AND tl.resource_description LIKE "%index_id or stats_id = %" THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX("index_id or stats_id = ", tl.resource_description) + 23),
COALESCE
(
NULLIF
(
CHARINDEX(",", tl.resource_description, CHARINDEX("index_id or stats_id = ", tl.resource_description) + 23),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX("index_id or stats_id = ", tl.resource_description) + 23)
)
)
ELSE NULL
END
) AS index_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_description LIKE "%schema_id = %" THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX("schema_id = ", tl.resource_description) + 12),
COALESCE
(
NULLIF
(
CHARINDEX(",", tl.resource_description, CHARINDEX("schema_id = ", tl.resource_description) + 12),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX("schema_id = ", tl.resource_description) + 12)
)
)
ELSE NULL
END
) AS schema_id,
CONVERT
(
INT,
CASE
WHEN tl.resource_description LIKE "%principal_id = %" THEN
(
SUBSTRING
(
tl.resource_description,
(CHARINDEX("principal_id = ", tl.resource_description) + 15),
COALESCE
(
NULLIF
(
CHARINDEX(",", tl.resource_description, CHARINDEX("principal_id = ", tl.resource_description) + 15),
0
),
DATALENGTH(tl.resource_description)+1
) - (CHARINDEX("principal_id = ", tl.resource_description) + 15)
)
)
ELSE NULL
END
) AS principal_id,
tl.request_mode,
tl.request_status,
tl.request_session_id AS session_id,
tl.request_request_id AS request_id,
RTRIM(tl.resource_description) AS resource_description,
tl.resource_associated_entity_id
FROM
(
SELECT
request_session_id,
CONVERT(VARCHAR(120), resource_type) COLLATE Latin1_General_Bin2 AS resource_type,
CONVERT(VARCHAR(120), resource_subtype) COLLATE Latin1_General_Bin2 AS resource_subtype,
resource_database_id,
CONVERT(VARCHAR(512), resource_description) COLLATE Latin1_General_Bin2 AS resource_description,
resource_associated_entity_id,
CONVERT(VARCHAR(120), request_mode) COLLATE Latin1_General_Bin2 AS request_mode,
CONVERT(VARCHAR(120), request_status) COLLATE Latin1_General_Bin2 AS request_status,
request_request_id
FROM sys.dm_tran_locks
) AS tl
) AS x
GROUP BY
x.resource_type,
x.database_name,
x.object_id,
x.file_id,
CASE
WHEN x.page_no = 1 OR x.page_no % 8088 = 0 THEN "PFS"
WHEN x.page_no = 2 OR x.page_no % 511232 = 0 THEN "GAM"
WHEN x.page_no = 3 OR (x.page_no - 1) % 511232 = 0 THEN "SGAM"
WHEN x.page_no = 6 OR (x.page_no - 6) % 511232 = 0 THEN "DCM"
WHEN x.page_no = 7 OR (x.page_no - 7) % 511232 = 0 THEN "BCM"
WHEN x.page_no IS NOT NULL THEN "*"
ELSE NULL
END,
x.hobt_id,
x.allocation_unit_id,
x.index_id,
x.schema_id,
x.principal_id,
x.request_mode,
x.request_status,
x.session_id,
x.request_id,
CASE
WHEN COALESCE(x.object_id, x.file_id, x.hobt_id, x.allocation_unit_id, x.index_id, x.schema_id, x.principal_id) IS NULL THEN NULLIF(resource_description, "")
ELSE NULL
END
) AS y ON
y.session_id = s.session_id
AND y.request_id = s.request_id
OPTION (HASH GROUP);
--Disable unnecessary autostats on the table
CREATE STATISTICS s_database_name ON #locks (database_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_object_id ON #locks (object_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_hobt_id ON #locks (hobt_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_allocation_unit_id ON #locks (allocation_unit_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_index_id ON #locks (index_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_schema_id ON #locks (schema_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_principal_id ON #locks (principal_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_id ON #locks (request_id)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_start_time ON #locks (start_time)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_resource_type ON #locks (resource_type)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_object_name ON #locks (object_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_schema_name ON #locks (schema_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_page_type ON #locks (page_type)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_mode ON #locks (request_mode)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_request_status ON #locks (request_status)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_resource_description ON #locks (resource_description)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_index_name ON #locks (index_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_principal_name ON #locks (principal_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
END;
DECLARE
@sql VARCHAR(MAX),
@sql_n NVARCHAR(MAX);
SET @sql =
CONVERT(VARCHAR(MAX), "") +
"DECLARE @blocker BIT;
SET @blocker = 0;
DECLARE @i INT;
SET @i = 2147483647;
DECLARE @sessions TABLE
(
session_id SMALLINT NOT NULL,
request_id INT NOT NULL,
login_time DATETIME,
last_request_end_time DATETIME,
status VARCHAR(30),
statement_start_offset INT,
statement_end_offset INT,
sql_handle BINARY(20),
host_name NVARCHAR(128),
login_name NVARCHAR(128),
program_name NVARCHAR(128),
database_id SMALLINT,
memory_usage INT,
open_tran_count SMALLINT,
" +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
"wait_type NVARCHAR(32),
wait_resource NVARCHAR(256),
wait_time BIGINT,
"
ELSE
""
END +
"blocked SMALLINT,
is_user_process BIT,
cmd VARCHAR(32),
PRIMARY KEY CLUSTERED (session_id, request_id) WITH (IGNORE_DUP_KEY = ON)
);
DECLARE @blockers TABLE
(
session_id INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON)
);
BLOCKERS:;
INSERT @sessions
(
session_id,
request_id,
login_time,
last_request_end_time,
status,
statement_start_offset,
statement_end_offset,
sql_handle,
host_name,
login_name,
program_name,
database_id,
memory_usage,
open_tran_count,
" +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
"wait_type,
wait_resource,
wait_time,
"
ELSE
""
END +
"blocked,
is_user_process,
cmd
)
SELECT TOP(@i)
spy.session_id,
spy.request_id,
spy.login_time,
spy.last_request_end_time,
spy.status,
spy.statement_start_offset,
spy.statement_end_offset,
spy.sql_handle,
spy.host_name,
spy.login_name,
spy.program_name,
spy.database_id,
spy.memory_usage,
spy.open_tran_count,
" +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
"spy.wait_type,
CASE
WHEN
spy.wait_type LIKE N""PAGE%LATCH_%""
OR spy.wait_type = N""CXPACKET""
OR spy.wait_type LIKE N""LATCH[_]%""
OR spy.wait_type = N""OLEDB"" THEN
spy.wait_resource
ELSE
NULL
END AS wait_resource,
spy.wait_time,
"
ELSE
""
END +
"spy.blocked,
spy.is_user_process,
spy.cmd
FROM
(
SELECT TOP(@i)
spx.*,
" +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
"ROW_NUMBER() OVER
(
PARTITION BY
spx.session_id,
spx.request_id
ORDER BY
CASE
WHEN spx.wait_type LIKE N""LCK[_]%"" THEN
1
ELSE
99
END,
spx.wait_time DESC,
spx.blocked DESC
) AS r
"
ELSE
"1 AS r
"
END +
"FROM
(
SELECT TOP(@i)
sp0.session_id,
sp0.request_id,
sp0.login_time,
sp0.last_request_end_time,
LOWER(sp0.status) AS status,
CASE
WHEN sp0.cmd = ""CREATE INDEX"" THEN
0
ELSE
sp0.stmt_start
END AS statement_start_offset,
CASE
WHEN sp0.cmd = N""CREATE INDEX"" THEN
-1
ELSE
COALESCE(NULLIF(sp0.stmt_end, 0), -1)
END AS statement_end_offset,
sp0.sql_handle,
sp0.host_name,
sp0.login_name,
sp0.program_name,
sp0.database_id,
sp0.memory_usage,
sp0.open_tran_count,
" +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
"CASE
WHEN sp0.wait_time > 0 AND sp0.wait_type <> N""CXPACKET"" THEN
sp0.wait_type
ELSE
NULL
END AS wait_type,
CASE
WHEN sp0.wait_time > 0 AND sp0.wait_type <> N""CXPACKET"" THEN
sp0.wait_resource
ELSE
NULL
END AS wait_resource,
CASE
WHEN sp0.wait_type <> N""CXPACKET"" THEN
sp0.wait_time
ELSE
0
END AS wait_time,
"
ELSE
""
END +
"sp0.blocked,
sp0.is_user_process,
sp0.cmd
FROM
(
SELECT TOP(@i)
sp1.session_id,
sp1.request_id,
sp1.login_time,
sp1.last_request_end_time,
sp1.status,
sp1.cmd,
sp1.stmt_start,
sp1.stmt_end,
MAX(NULLIF(sp1.sql_handle, 0x00)) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS sql_handle,
sp1.host_name,
MAX(sp1.login_name) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS login_name,
sp1.program_name,
sp1.database_id,
MAX(sp1.memory_usage) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS memory_usage,
MAX(sp1.open_tran_count) OVER (PARTITION BY sp1.session_id, sp1.request_id) AS open_tran_count,
sp1.wait_type,
sp1.wait_resource,
sp1.wait_time,
sp1.blocked,
sp1.hostprocess,
sp1.is_user_process
FROM
(
SELECT TOP(@i)
sp2.spid AS session_id,
CASE sp2.status
WHEN ""sleeping"" THEN
CONVERT(INT, 0)
ELSE
sp2.request_id
END AS request_id,
MAX(sp2.login_time) AS login_time,
MAX(sp2.last_batch) AS last_request_end_time,
MAX(CONVERT(VARCHAR(30), RTRIM(sp2.status)) COLLATE Latin1_General_Bin2) AS status,
MAX(CONVERT(VARCHAR(32), RTRIM(sp2.cmd)) COLLATE Latin1_General_Bin2) AS cmd,
MAX(sp2.stmt_start) AS stmt_start,
MAX(sp2.stmt_end) AS stmt_end,
MAX(sp2.sql_handle) AS sql_handle,
MAX(CONVERT(sysname, RTRIM(sp2.hostname)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS host_name,
MAX(CONVERT(sysname, RTRIM(sp2.loginame)) COLLATE SQL_Latin1_General_CP1_CI_AS) AS login_name,
MAX
(
CASE
WHEN blk.queue_id IS NOT NULL THEN
N""Service Broker
database_id: "" + CONVERT(NVARCHAR, blk.database_id) +
N"" queue_id: "" + CONVERT(NVARCHAR, blk.queue_id)
ELSE
CONVERT
(
sysname,
RTRIM(sp2.program_name)
)
END COLLATE SQL_Latin1_General_CP1_CI_AS
) AS program_name,
MAX(sp2.dbid) AS database_id,
MAX(sp2.memusage) AS memory_usage,
MAX(sp2.open_tran) AS open_tran_count,
RTRIM(sp2.lastwaittype) AS wait_type,
RTRIM(sp2.waitresource) AS wait_resource,
MAX(sp2.waittime) AS wait_time,
COALESCE(NULLIF(sp2.blocked, sp2.spid), 0) AS blocked,
MAX
(
CASE
WHEN blk.session_id = sp2.spid THEN
""blocker""
ELSE
RTRIM(sp2.hostprocess)
END
) AS hostprocess,
CONVERT
(
BIT,
MAX
(
CASE
WHEN sp2.hostprocess > """" THEN
1
ELSE
0
END
)
) AS is_user_process
FROM
(
SELECT TOP(@i)
session_id,
CONVERT(INT, NULL) AS queue_id,
CONVERT(INT, NULL) AS database_id
FROM @blockers
UNION ALL
SELECT TOP(@i)
CONVERT(SMALLINT, 0),
CONVERT(INT, NULL) AS queue_id,
CONVERT(INT, NULL) AS database_id
WHERE
@blocker = 0
UNION ALL
SELECT TOP(@i)
CONVERT(SMALLINT, spid),
queue_id,
database_id
FROM sys.dm_broker_activated_tasks
WHERE
@blocker = 0
) AS blk
INNER JOIN sys.sysprocesses AS sp2 ON
sp2.spid = blk.session_id
OR
(
blk.session_id = 0
AND @blocker = 0
)
" +
CASE
WHEN
(
@get_task_info = 0
AND @find_block_leaders = 0
) THEN
"WHERE
sp2.ecid = 0
"
ELSE
""
END +
"GROUP BY
sp2.spid,
CASE sp2.status
WHEN ""sleeping"" THEN
CONVERT(INT, 0)
ELSE
sp2.request_id
END,
RTRIM(sp2.lastwaittype),
RTRIM(sp2.waitresource),
COALESCE(NULLIF(sp2.blocked, sp2.spid), 0)
) AS sp1
) AS sp0
WHERE
@blocker = 1
OR
(1=1
" +
--inclusive filter
CASE
WHEN @filter <> "" THEN
CASE @filter_type
WHEN "session" THEN
CASE
WHEN CONVERT(SMALLINT, @filter) <> 0 THEN
"AND sp0.session_id = CONVERT(SMALLINT, @filter)
"
ELSE
""
END
WHEN "program" THEN
"AND sp0.program_name LIKE @filter
"
WHEN "login" THEN
"AND sp0.login_name LIKE @filter
"
WHEN "host" THEN
"AND sp0.host_name LIKE @filter
"
WHEN "database" THEN
"AND DB_NAME(sp0.database_id) LIKE @filter
"
ELSE
""
END
ELSE
""
END +
--exclusive filter
CASE
WHEN @not_filter <> "" THEN
CASE @not_filter_type
WHEN "session" THEN
CASE
WHEN CONVERT(SMALLINT, @not_filter) <> 0 THEN
"AND sp0.session_id <> CONVERT(SMALLINT, @not_filter)
"
ELSE
""
END
WHEN "program" THEN
"AND sp0.program_name NOT LIKE @not_filter
"
WHEN "login" THEN
"AND sp0.login_name NOT LIKE @not_filter
"
WHEN "host" THEN
"AND sp0.host_name NOT LIKE @not_filter
"
WHEN "database" THEN
"AND DB_NAME(sp0.database_id) NOT LIKE @not_filter
"
ELSE
""
END
ELSE
""
END +
CASE @show_own_spid
WHEN 1 THEN
""
ELSE
"AND sp0.session_id <> @@spid
"
END +
CASE
WHEN @show_system_spids = 0 THEN
"AND sp0.hostprocess > """"
"
ELSE
""
END +
CASE @show_sleeping_spids
WHEN 0 THEN
"AND sp0.status <> ""sleeping""
"
WHEN 1 THEN
"AND
(
sp0.status <> ""sleeping""
OR sp0.open_tran_count > 0
)
"
ELSE
""
END +
")
) AS spx
) AS spy
WHERE
spy.r = 1;
" +
CASE @recursion
WHEN 1 THEN
"IF @@ROWCOUNT > 0
BEGIN;
INSERT @blockers
(
session_id
)
SELECT TOP(@i)
blocked
FROM @sessions
WHERE
NULLIF(blocked, 0) IS NOT NULL
EXCEPT
SELECT TOP(@i)
session_id
FROM @sessions;
" +
CASE
WHEN
(
@get_task_info > 0
OR @find_block_leaders = 1
) THEN
"IF @@ROWCOUNT > 0
BEGIN;
SET @blocker = 1;
GOTO BLOCKERS;
END;
"
ELSE
""
END +
"END;
"
ELSE
""
END +
"SELECT TOP(@i)
@recursion AS recursion,
x.session_id,
x.request_id,
DENSE_RANK() OVER
(
ORDER BY
x.session_id
) AS session_number,
" +
CASE
WHEN @output_column_list LIKE "%|[dd hh:mm:ss.mss|]%" ESCAPE "|" THEN
"x.elapsed_time "
ELSE
"0 "
END +
"AS elapsed_time,
" +
CASE
WHEN
(
@output_column_list LIKE "%|[dd hh:mm:ss.mss (avg)|]%" ESCAPE "|" OR
@output_column_list LIKE "%|[avg_elapsed_time|]%" ESCAPE "|"
)
AND @recursion = 1
THEN
"x.avg_elapsed_time / 1000 "
ELSE
"NULL "
END +
"AS avg_elapsed_time,
" +
CASE
WHEN
@output_column_list LIKE "%|[physical_io|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[physical_io_delta|]%" ESCAPE "|"
THEN
"x.physical_io "
ELSE
"NULL "
END +
"AS physical_io,
" +
CASE
WHEN
@output_column_list LIKE "%|[reads|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[reads_delta|]%" ESCAPE "|"
THEN
"x.reads "
ELSE
"0 "
END +
"AS reads,
" +
CASE
WHEN
@output_column_list LIKE "%|[physical_reads|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[physical_reads_delta|]%" ESCAPE "|"
THEN
"x.physical_reads "
ELSE
"0 "
END +
"AS physical_reads,
" +
CASE
WHEN
@output_column_list LIKE "%|[writes|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[writes_delta|]%" ESCAPE "|"
THEN
"x.writes "
ELSE
"0 "
END +
"AS writes,
" +
CASE
WHEN
@output_column_list LIKE "%|[tempdb_allocations|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[tempdb_allocations_delta|]%" ESCAPE "|"
THEN
"x.tempdb_allocations "
ELSE
"0 "
END +
"AS tempdb_allocations,
" +
CASE
WHEN
@output_column_list LIKE "%|[tempdb_current|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[tempdb_current_delta|]%" ESCAPE "|"
THEN
"x.tempdb_current "
ELSE
"0 "
END +
"AS tempdb_current,
" +
CASE
WHEN
@output_column_list LIKE "%|[CPU|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[CPU_delta|]%" ESCAPE "|"
THEN
"x.CPU "
ELSE
"0 "
END +
"AS CPU,
" +
CASE
WHEN
@output_column_list LIKE "%|[CPU_delta|]%" ESCAPE "|"
AND @get_task_info = 2
THEN
"x.thread_CPU_snapshot "
ELSE
"0 "
END +
"AS thread_CPU_snapshot,
" +
CASE
WHEN
@output_column_list LIKE "%|[context_switches|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[context_switches_delta|]%" ESCAPE "|"
THEN
"x.context_switches "
ELSE
"NULL "
END +
"AS context_switches,
" +
CASE
WHEN
@output_column_list LIKE "%|[used_memory|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[used_memory_delta|]%" ESCAPE "|"
THEN
"x.used_memory "
ELSE
"0 "
END +
"AS used_memory,
" +
CASE
WHEN
@output_column_list LIKE "%|[tasks|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.tasks "
ELSE
"NULL "
END +
"AS tasks,
" +
CASE
WHEN
(
@output_column_list LIKE "%|[status|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[sql_command|]%" ESCAPE "|"
)
AND @recursion = 1
THEN
"x.status "
ELSE
""""" "
END +
"AS status,
" +
CASE
WHEN
@output_column_list LIKE "%|[wait_info|]%" ESCAPE "|"
AND @recursion = 1
THEN
CASE @get_task_info
WHEN 2 THEN
"COALESCE(x.task_wait_info, x.sys_wait_info) "
ELSE
"x.sys_wait_info "
END
ELSE
"NULL "
END +
"AS wait_info,
" +
CASE
WHEN
(
@output_column_list LIKE "%|[tran_start_time|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[tran_log_writes|]%" ESCAPE "|"
)
AND @recursion = 1
THEN
"x.transaction_id "
ELSE
"NULL "
END +
"AS transaction_id,
" +
CASE
WHEN
@output_column_list LIKE "%|[open_tran_count|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.open_tran_count "
ELSE
"NULL "
END +
"AS open_tran_count,
" +
CASE
WHEN
@output_column_list LIKE "%|[sql_text|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.sql_handle "
ELSE
"NULL "
END +
"AS sql_handle,
" +
CASE
WHEN
(
@output_column_list LIKE "%|[sql_text|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[query_plan|]%" ESCAPE "|"
)
AND @recursion = 1
THEN
"x.statement_start_offset "
ELSE
"NULL "
END +
"AS statement_start_offset,
" +
CASE
WHEN
(
@output_column_list LIKE "%|[sql_text|]%" ESCAPE "|"
OR @output_column_list LIKE "%|[query_plan|]%" ESCAPE "|"
)
AND @recursion = 1
THEN
"x.statement_end_offset "
ELSE
"NULL "
END +
"AS statement_end_offset,
" +
"NULL AS sql_text,
" +
CASE
WHEN
@output_column_list LIKE "%|[query_plan|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.plan_handle "
ELSE
"NULL "
END +
"AS plan_handle,
" +
CASE
WHEN
@output_column_list LIKE "%|[blocking_session_id|]%" ESCAPE "|"
AND @recursion = 1
THEN
"NULLIF(x.blocking_session_id, 0) "
ELSE
"NULL "
END +
"AS blocking_session_id,
" +
CASE
WHEN
@output_column_list LIKE "%|[percent_complete|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.percent_complete "
ELSE
"NULL "
END +
"AS percent_complete,
" +
CASE
WHEN
@output_column_list LIKE "%|[host_name|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.host_name "
ELSE
""""" "
END +
"AS host_name,
" +
CASE
WHEN
@output_column_list LIKE "%|[login_name|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.login_name "
ELSE
""""" "
END +
"AS login_name,
" +
CASE
WHEN
@output_column_list LIKE "%|[database_name|]%" ESCAPE "|"
AND @recursion = 1
THEN
"DB_NAME(x.database_id) "
ELSE
"NULL "
END +
"AS database_name,
" +
CASE
WHEN
@output_column_list LIKE "%|[program_name|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.program_name "
ELSE
""""" "
END +
"AS program_name,
" +
CASE
WHEN
@output_column_list LIKE "%|[additional_info|]%" ESCAPE "|"
AND @recursion = 1
THEN
"(
SELECT TOP(@i)
x.text_size,
x.language,
x.date_format,
x.date_first,
CASE x.quoted_identifier
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS quoted_identifier,
CASE x.arithabort
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS arithabort,
CASE x.ansi_null_dflt_on
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS ansi_null_dflt_on,
CASE x.ansi_defaults
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS ansi_defaults,
CASE x.ansi_warnings
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS ansi_warnings,
CASE x.ansi_padding
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS ansi_padding,
CASE ansi_nulls
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS ansi_nulls,
CASE x.concat_null_yields_null
WHEN 0 THEN ""OFF""
WHEN 1 THEN ""ON""
END AS concat_null_yields_null,
CASE x.transaction_isolation_level
WHEN 0 THEN ""Unspecified""
WHEN 1 THEN ""ReadUncomitted""
WHEN 2 THEN ""ReadCommitted""
WHEN 3 THEN ""Repeatable""
WHEN 4 THEN ""Serializable""
WHEN 5 THEN ""Snapshot""
END AS transaction_isolation_level,
x.lock_timeout,
x.deadlock_priority,
x.row_count,
x.command_type,
master.dbo.fn_varbintohexstr(x.sql_handle) AS sql_handle,
master.dbo.fn_varbintohexstr(x.plan_handle) AS plan_handle,
" +
CASE
WHEN @output_column_list LIKE "%|[program_name|]%" ESCAPE "|" THEN
"(
SELECT TOP(1)
CONVERT(uniqueidentifier, CONVERT(XML, """").value(""xs:hexBinary( substring(sql:column("agent_info.job_id_string"), 0) )"", ""binary(16)"")) AS job_id,
agent_info.step_id,
(
SELECT TOP(1)
NULL
FOR XML
PATH(""job_name""),
TYPE
),
(
SELECT TOP(1)
NULL
FOR XML
PATH(""step_name""),
TYPE
)
FROM
(
SELECT TOP(1)
SUBSTRING(x.program_name, CHARINDEX(""0x"", x.program_name) + 2, 32) AS job_id_string,
SUBSTRING(x.program_name, CHARINDEX("": Step "", x.program_name) + 7, CHARINDEX("")"", x.program_name, CHARINDEX("": Step "", x.program_name)) - (CHARINDEX("": Step "", x.program_name) + 7)) AS step_id
WHERE
x.program_name LIKE N""SQLAgent - TSQL JobStep (Job 0x%""
) AS agent_info
FOR XML
PATH(""agent_job_info""),
TYPE
),
"
ELSE ""
END +
CASE
WHEN @get_task_info = 2 THEN
"CONVERT(XML, x.block_info) AS block_info,
"
ELSE
""
END +
"x.host_process_id
FOR XML
PATH(""additional_info""),
TYPE
) "
ELSE
"NULL "
END +
"AS additional_info,
x.start_time,
" +
CASE
WHEN
@output_column_list LIKE "%|[login_time|]%" ESCAPE "|"
AND @recursion = 1
THEN
"x.login_time "
ELSE
"NULL "
END +
"AS login_time,
x.last_request_start_time
FROM
(
SELECT TOP(@i)
y.*,
CASE
WHEN DATEDIFF(hour, y.start_time, GETDATE()) > 576 THEN
DATEDIFF(second, GETDATE(), y.start_time)
ELSE DATEDIFF(ms, y.start_time, GETDATE())
END AS elapsed_time,
COALESCE(tempdb_info.tempdb_allocations, 0) AS tempdb_allocations,
COALESCE
(
CASE
WHEN tempdb_info.tempdb_current < 0 THEN 0
ELSE tempdb_info.tempdb_current
END,
0
) AS tempdb_current,
" +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
"N""("" + CONVERT(NVARCHAR, y.wait_duration_ms) + N""ms)"" +
y.wait_type +
CASE
WHEN y.wait_type LIKE N""PAGE%LATCH_%"" THEN
N"":"" +
COALESCE(DB_NAME(CONVERT(INT, LEFT(y.resource_description, CHARINDEX(N"":"", y.resource_description) - 1))), N""(null)"") +
N"":"" +
SUBSTRING(y.resource_description, CHARINDEX(N"":"", y.resource_description) + 1, LEN(y.resource_description) - CHARINDEX(N"":"", REVERSE(y.resource_description)) - CHARINDEX(N"":"", y.resource_description)) +
N""("" +
CASE
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 1 OR
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) % 8088 = 0
THEN
N""PFS""
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 2 OR
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) % 511232 = 0
THEN
N""GAM""
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 3 OR
(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) - 1) % 511232 = 0
THEN
N""SGAM""
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 6 OR
(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) - 6) % 511232 = 0
THEN
N""DCM""
WHEN
CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) = 7 OR
(CONVERT(INT, RIGHT(y.resource_description, CHARINDEX(N"":"", REVERSE(y.resource_description)) - 1)) - 7) % 511232 = 0
THEN
N""BCM""
ELSE
N""*""
END +
N"")""
WHEN y.wait_type = N""CXPACKET"" THEN
N"":"" + SUBSTRING(y.resource_description, CHARINDEX(N""nodeId"", y.resource_description) + 7, 4)
WHEN y.wait_type LIKE N""LATCH[_]%"" THEN
N"" ["" + LEFT(y.resource_description, COALESCE(NULLIF(CHARINDEX(N"" "", y.resource_description), 0), LEN(y.resource_description) + 1) - 1) + N""]""
WHEN
y.wait_type = N""OLEDB""
AND y.resource_description LIKE N""%(SPID=%)"" THEN
N""["" + LEFT(y.resource_description, CHARINDEX(N""(SPID="", y.resource_description) - 2) +
N"":"" + SUBSTRING(y.resource_description, CHARINDEX(N""(SPID="", y.resource_description) + 6, CHARINDEX(N"")"", y.resource_description, (CHARINDEX(N""(SPID="", y.resource_description) + 6)) - (CHARINDEX(N""(SPID="", y.resource_description) + 6)) + ""]""
ELSE
N""""
END COLLATE Latin1_General_Bin2 AS sys_wait_info,
"
ELSE
""
END +
CASE
WHEN @get_task_info = 2 THEN
"tasks.physical_io,
tasks.context_switches,
tasks.tasks,
tasks.block_info,
tasks.wait_info AS task_wait_info,
tasks.thread_CPU_snapshot,
"
ELSE
""
END +
CASE
WHEN NOT (@get_avg_time = 1 AND @recursion = 1) THEN
"CONVERT(INT, NULL) "
ELSE
"qs.total_elapsed_time / qs.execution_count "
END +
"AS avg_elapsed_time
FROM
(
SELECT TOP(@i)
sp.session_id,
sp.request_id,
COALESCE(r.logical_reads, s.logical_reads) AS reads,
COALESCE(r.reads, s.reads) AS physical_reads,
COALESCE(r.writes, s.writes) AS writes,
COALESCE(r.CPU_time, s.CPU_time) AS CPU,
sp.memory_usage + COALESCE(r.granted_query_memory, 0) AS used_memory,
LOWER(sp.status) AS status,
COALESCE(r.sql_handle, sp.sql_handle) AS sql_handle,
COALESCE(r.statement_start_offset, sp.statement_start_offset) AS statement_start_offset,
COALESCE(r.statement_end_offset, sp.statement_end_offset) AS statement_end_offset,
" +
CASE
WHEN
(
@get_task_info <> 0
OR @find_block_leaders = 1
) THEN
"sp.wait_type COLLATE Latin1_General_Bin2 AS wait_type,
sp.wait_resource COLLATE Latin1_General_Bin2 AS resource_description,
sp.wait_time AS wait_duration_ms,
"
ELSE
""
END +
"NULLIF(sp.blocked, 0) AS blocking_session_id,
r.plan_handle,
NULLIF(r.percent_complete, 0) AS percent_complete,
sp.host_name,
sp.login_name,
sp.program_name,
s.host_process_id,
COALESCE(r.text_size, s.text_size) AS text_size,
COALESCE(r.language, s.language) AS language,
COALESCE(r.date_format, s.date_format) AS date_format,
COALESCE(r.date_first, s.date_first) AS date_first,
COALESCE(r.quoted_identifier, s.quoted_identifier) AS quoted_identifier,
COALESCE(r.arithabort, s.arithabort) AS arithabort,
COALESCE(r.ansi_null_dflt_on, s.ansi_null_dflt_on) AS ansi_null_dflt_on,
COALESCE(r.ansi_defaults, s.ansi_defaults) AS ansi_defaults,
COALESCE(r.ansi_warnings, s.ansi_warnings) AS ansi_warnings,
COALESCE(r.ansi_padding, s.ansi_padding) AS ansi_padding,
COALESCE(r.ansi_nulls, s.ansi_nulls) AS ansi_nulls,
COALESCE(r.concat_null_yields_null, s.concat_null_yields_null) AS concat_null_yields_null,
COALESCE(r.transaction_isolation_level, s.transaction_isolation_level) AS transaction_isolation_level,
COALESCE(r.lock_timeout, s.lock_timeout) AS lock_timeout,
COALESCE(r.deadlock_priority, s.deadlock_priority) AS deadlock_priority,
COALESCE(r.row_count, s.row_count) AS row_count,
COALESCE(r.command, sp.cmd) AS command_type,
COALESCE
(
CASE
WHEN
(
s.is_user_process = 0
AND r.total_elapsed_time >= 0
) THEN
DATEADD
(
ms,
1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
)
END,
NULLIF(COALESCE(r.start_time, sp.last_request_end_time), CONVERT(DATETIME, ""19000101"", 112)),
(
SELECT TOP(1)
DATEADD(second, -(ms_ticks / 1000), GETDATE())
FROM sys.dm_os_sys_info
)
) AS start_time,
sp.login_time,
CASE
WHEN s.is_user_process = 1 THEN
s.last_request_start_time
ELSE
COALESCE
(
DATEADD
(
ms,
1000 * (DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())) / 500) - DATEPART(ms, DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())),
DATEADD(second, -(r.total_elapsed_time / 1000), GETDATE())
),
s.last_request_start_time
)
END AS last_request_start_time,
r.transaction_id,
sp.database_id,
sp.open_tran_count
FROM @sessions AS sp
LEFT OUTER LOOP JOIN sys.dm_exec_sessions AS s ON
s.session_id = sp.session_id
AND s.login_time = sp.login_time
LEFT OUTER LOOP JOIN sys.dm_exec_requests AS r ON
sp.status <> ""sleeping""
AND r.session_id = sp.session_id
AND r.request_id = sp.request_id
AND
(
(
s.is_user_process = 0
AND sp.is_user_process = 0
)
OR
(
r.start_time = s.last_request_start_time
AND s.last_request_end_time <= sp.last_request_end_time
)
)
) AS y
" +
CASE
WHEN @get_task_info = 2 THEN
CONVERT(VARCHAR(MAX), "") +
"LEFT OUTER HASH JOIN
(
SELECT TOP(@i)
task_nodes.task_node.value(""(session_id/text())[1]"", ""SMALLINT"") AS session_id,
task_nodes.task_node.value(""(request_id/text())[1]"", ""INT"") AS request_id,
task_nodes.task_node.value(""(physical_io/text())[1]"", ""BIGINT"") AS physical_io,
task_nodes.task_node.value(""(context_switches/text())[1]"", ""BIGINT"") AS context_switches,
task_nodes.task_node.value(""(tasks/text())[1]"", ""INT"") AS tasks,
task_nodes.task_node.value(""(block_info/text())[1]"", ""NVARCHAR(4000)"") AS block_info,
task_nodes.task_node.value(""(waits/text())[1]"", ""NVARCHAR(4000)"") AS wait_info,
task_nodes.task_node.value(""(thread_CPU_snapshot/text())[1]"", ""BIGINT"") AS thread_CPU_snapshot
FROM
(
SELECT TOP(@i)
CONVERT
(
XML,
REPLACE
(
CONVERT(NVARCHAR(MAX), tasks_raw.task_xml_raw) COLLATE Latin1_General_Bin2,
N""" + ERROR_MESSAGE() + "" />"
END
FROM #sessions AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END CATCH;
FETCH NEXT FROM sql_cursor
INTO
@session_id,
@request_id,
@sql_handle,
@statement_start_offset,
@statement_end_offset;
END;
--Return this to the default
SET LOCK_TIMEOUT -1;
CLOSE sql_cursor;
DEALLOCATE sql_cursor;
END;
IF
@get_outer_command = 1
AND @recursion = 1
AND @output_column_list LIKE "%|[sql_command|]%" ESCAPE "|"
BEGIN;
DECLARE @buffer_results TABLE
(
EventType VARCHAR(30),
Parameters INT,
EventInfo NVARCHAR(4000),
start_time DATETIME,
session_number INT IDENTITY(1,1) NOT NULL PRIMARY KEY
);
DECLARE buffer_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT
session_id,
MAX(start_time) AS start_time
FROM #sessions
WHERE
recursion = 1
GROUP BY
session_id
ORDER BY
session_id
OPTION (KEEPFIXED PLAN);
OPEN buffer_cursor;
FETCH NEXT FROM buffer_cursor
INTO
@session_id,
@start_time;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
--In SQL Server 2008, DBCC INPUTBUFFER will throw
--an exception if the session no longer exists
INSERT @buffer_results
(
EventType,
Parameters,
EventInfo
)
EXEC sp_executesql
N"DBCC INPUTBUFFER(@session_id) WITH NO_INFOMSGS;",
N"@session_id SMALLINT",
@session_id;
UPDATE br
SET
br.start_time = @start_time
FROM @buffer_results AS br
WHERE
br.session_number =
(
SELECT MAX(br2.session_number)
FROM @buffer_results br2
);
END TRY
BEGIN CATCH
END CATCH;
FETCH NEXT FROM buffer_cursor
INTO
@session_id,
@start_time;
END;
UPDATE s
SET
sql_command =
(
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
N"--" + NCHAR(13) + NCHAR(10) + br.EventInfo + NCHAR(13) + NCHAR(10) + N"--" COLLATE Latin1_General_Bin2
),
NCHAR(31),N"?"),NCHAR(30),N"?"),NCHAR(29),N"?"),NCHAR(28),N"?"),NCHAR(27),N"?"),NCHAR(26),N"?"),NCHAR(25),N"?"),NCHAR(24),N"?"),NCHAR(23),N"?"),NCHAR(22),N"?"),
NCHAR(21),N"?"),NCHAR(20),N"?"),NCHAR(19),N"?"),NCHAR(18),N"?"),NCHAR(17),N"?"),NCHAR(16),N"?"),NCHAR(15),N"?"),NCHAR(14),N"?"),NCHAR(12),N"?"),
NCHAR(11),N"?"),NCHAR(8),N"?"),NCHAR(7),N"?"),NCHAR(6),N"?"),NCHAR(5),N"?"),NCHAR(4),N"?"),NCHAR(3),N"?"),NCHAR(2),N"?"),NCHAR(1),N"?"),
NCHAR(0),
N""
) AS [processing-instruction(query)]
FROM @buffer_results AS br
WHERE
br.session_number = s.session_number
AND br.start_time = s.start_time
AND
(
(
s.start_time = s.last_request_start_time
AND EXISTS
(
SELECT *
FROM sys.dm_exec_requests r2
WHERE
r2.session_id = s.session_id
AND r2.request_id = s.request_id
AND r2.start_time = s.start_time
)
)
OR
(
s.request_id = 0
AND EXISTS
(
SELECT *
FROM sys.dm_exec_sessions s2
WHERE
s2.session_id = s.session_id
AND s2.last_request_start_time = s.last_request_start_time
)
)
)
FOR XML
PATH(""),
TYPE
)
FROM #sessions AS s
WHERE
recursion = 1
OPTION (KEEPFIXED PLAN);
CLOSE buffer_cursor;
DEALLOCATE buffer_cursor;
END;
IF
@get_plans >= 1
AND @recursion = 1
AND @output_column_list LIKE "%|[query_plan|]%" ESCAPE "|"
BEGIN;
DECLARE plan_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT
session_id,
request_id,
plan_handle,
statement_start_offset,
statement_end_offset
FROM #sessions
WHERE
recursion = 1
AND plan_handle IS NOT NULL
OPTION (KEEPFIXED PLAN);
OPEN plan_cursor;
FETCH NEXT FROM plan_cursor
INTO
@session_id,
@request_id,
@plan_handle,
@statement_start_offset,
@statement_end_offset;
--Wait up to 5 ms for a query plan, then give up
SET LOCK_TIMEOUT 5;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
UPDATE s
SET
s.query_plan =
(
SELECT
CONVERT(xml, query_plan)
FROM sys.dm_exec_text_query_plan
(
@plan_handle,
CASE @get_plans
WHEN 1 THEN
@statement_start_offset
ELSE
0
END,
CASE @get_plans
WHEN 1 THEN
@statement_end_offset
ELSE
-1
END
)
)
FROM #sessions AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END TRY
BEGIN CATCH;
IF ERROR_NUMBER() = 6335
BEGIN;
UPDATE s
SET
s.query_plan =
(
SELECT
N"--" + NCHAR(13) + NCHAR(10) +
N"-- Could not render showplan due to XML data type limitations. " + NCHAR(13) + NCHAR(10) +
N"-- To see the graphical plan save the XML below as a .SQLPLAN file and re-open in SSMS." + NCHAR(13) + NCHAR(10) +
N"--" + NCHAR(13) + NCHAR(10) +
REPLACE(qp.query_plan, N"", NCHAR(13)+NCHAR(10)+N"") +
NCHAR(13) + NCHAR(10) + N"--" COLLATE Latin1_General_Bin2 AS [processing-instruction(query_plan)]
FROM sys.dm_exec_text_query_plan
(
@plan_handle,
CASE @get_plans
WHEN 1 THEN
@statement_start_offset
ELSE
0
END,
CASE @get_plans
WHEN 1 THEN
@statement_end_offset
ELSE
-1
END
) AS qp
FOR XML
PATH(""),
TYPE
)
FROM #sessions AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END;
ELSE
BEGIN;
UPDATE s
SET
s.query_plan =
CASE ERROR_NUMBER()
WHEN 1222 THEN "" + ERROR_MESSAGE() + "" />"
END
FROM #sessions AS s
WHERE
s.session_id = @session_id
AND s.request_id = @request_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END;
END CATCH;
FETCH NEXT FROM plan_cursor
INTO
@session_id,
@request_id,
@plan_handle,
@statement_start_offset,
@statement_end_offset;
END;
--Return this to the default
SET LOCK_TIMEOUT -1;
CLOSE plan_cursor;
DEALLOCATE plan_cursor;
END;
IF
@get_locks = 1
AND @recursion = 1
AND @output_column_list LIKE "%|[locks|]%" ESCAPE "|"
BEGIN;
DECLARE locks_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT
database_name
FROM #locks
WHERE
EXISTS
(
SELECT *
FROM #sessions AS s
WHERE
s.session_id = #locks.session_id
AND recursion = 1
)
AND database_name <> "(null)"
OPTION (KEEPFIXED PLAN);
OPEN locks_cursor;
FETCH NEXT FROM locks_cursor
INTO
@database_name;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
SET @sql_n = CONVERT(NVARCHAR(MAX), "") +
"UPDATE l " +
"SET " +
"object_name = " +
"REPLACE " +
"( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"o.name COLLATE Latin1_General_Bin2, " +
"NCHAR(31),N""?""),NCHAR(30),N""?""),NCHAR(29),N""?""),NCHAR(28),N""?""),NCHAR(27),N""?""),NCHAR(26),N""?""),NCHAR(25),N""?""),NCHAR(24),N""?""),NCHAR(23),N""?""),NCHAR(22),N""?""), " +
"NCHAR(21),N""?""),NCHAR(20),N""?""),NCHAR(19),N""?""),NCHAR(18),N""?""),NCHAR(17),N""?""),NCHAR(16),N""?""),NCHAR(15),N""?""),NCHAR(14),N""?""),NCHAR(12),N""?""), " +
"NCHAR(11),N""?""),NCHAR(8),N""?""),NCHAR(7),N""?""),NCHAR(6),N""?""),NCHAR(5),N""?""),NCHAR(4),N""?""),NCHAR(3),N""?""),NCHAR(2),N""?""),NCHAR(1),N""?""), " +
"NCHAR(0), " +
N""""" " +
"), " +
"index_name = " +
"REPLACE " +
"( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"i.name COLLATE Latin1_General_Bin2, " +
"NCHAR(31),N""?""),NCHAR(30),N""?""),NCHAR(29),N""?""),NCHAR(28),N""?""),NCHAR(27),N""?""),NCHAR(26),N""?""),NCHAR(25),N""?""),NCHAR(24),N""?""),NCHAR(23),N""?""),NCHAR(22),N""?""), " +
"NCHAR(21),N""?""),NCHAR(20),N""?""),NCHAR(19),N""?""),NCHAR(18),N""?""),NCHAR(17),N""?""),NCHAR(16),N""?""),NCHAR(15),N""?""),NCHAR(14),N""?""),NCHAR(12),N""?""), " +
"NCHAR(11),N""?""),NCHAR(8),N""?""),NCHAR(7),N""?""),NCHAR(6),N""?""),NCHAR(5),N""?""),NCHAR(4),N""?""),NCHAR(3),N""?""),NCHAR(2),N""?""),NCHAR(1),N""?""), " +
"NCHAR(0), " +
N""""" " +
"), " +
"schema_name = " +
"REPLACE " +
"( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"s.name COLLATE Latin1_General_Bin2, " +
"NCHAR(31),N""?""),NCHAR(30),N""?""),NCHAR(29),N""?""),NCHAR(28),N""?""),NCHAR(27),N""?""),NCHAR(26),N""?""),NCHAR(25),N""?""),NCHAR(24),N""?""),NCHAR(23),N""?""),NCHAR(22),N""?""), " +
"NCHAR(21),N""?""),NCHAR(20),N""?""),NCHAR(19),N""?""),NCHAR(18),N""?""),NCHAR(17),N""?""),NCHAR(16),N""?""),NCHAR(15),N""?""),NCHAR(14),N""?""),NCHAR(12),N""?""), " +
"NCHAR(11),N""?""),NCHAR(8),N""?""),NCHAR(7),N""?""),NCHAR(6),N""?""),NCHAR(5),N""?""),NCHAR(4),N""?""),NCHAR(3),N""?""),NCHAR(2),N""?""),NCHAR(1),N""?""), " +
"NCHAR(0), " +
N""""" " +
"), " +
"principal_name = " +
"REPLACE " +
"( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"dp.name COLLATE Latin1_General_Bin2, " +
"NCHAR(31),N""?""),NCHAR(30),N""?""),NCHAR(29),N""?""),NCHAR(28),N""?""),NCHAR(27),N""?""),NCHAR(26),N""?""),NCHAR(25),N""?""),NCHAR(24),N""?""),NCHAR(23),N""?""),NCHAR(22),N""?""), " +
"NCHAR(21),N""?""),NCHAR(20),N""?""),NCHAR(19),N""?""),NCHAR(18),N""?""),NCHAR(17),N""?""),NCHAR(16),N""?""),NCHAR(15),N""?""),NCHAR(14),N""?""),NCHAR(12),N""?""), " +
"NCHAR(11),N""?""),NCHAR(8),N""?""),NCHAR(7),N""?""),NCHAR(6),N""?""),NCHAR(5),N""?""),NCHAR(4),N""?""),NCHAR(3),N""?""),NCHAR(2),N""?""),NCHAR(1),N""?""), " +
"NCHAR(0), " +
N""""" " +
") " +
"FROM #locks AS l " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.allocation_units AS au ON " +
"au.allocation_unit_id = l.allocation_unit_id " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.partitions AS p ON " +
"p.hobt_id = " +
"COALESCE " +
"( " +
"l.hobt_id, " +
"CASE " +
"WHEN au.type IN (1, 3) THEN au.container_id " +
"ELSE NULL " +
"END " +
") " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.partitions AS p1 ON " +
"l.hobt_id IS NULL " +
"AND au.type = 2 " +
"AND p1.partition_id = au.container_id " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.objects AS o ON " +
"o.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.indexes AS i ON " +
"i.object_id = COALESCE(l.object_id, p.object_id, p1.object_id) " +
"AND i.index_id = COALESCE(l.index_id, p.index_id, p1.index_id) " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.schemas AS s ON " +
"s.schema_id = COALESCE(l.schema_id, o.schema_id) " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.database_principals AS dp ON " +
"dp.principal_id = l.principal_id " +
"WHERE " +
"l.database_name = @database_name " +
"OPTION (KEEPFIXED PLAN); ";
EXEC sp_executesql
@sql_n,
N"@database_name sysname",
@database_name;
END TRY
BEGIN CATCH;
UPDATE #locks
SET
query_error =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
ERROR_MESSAGE() COLLATE Latin1_General_Bin2
),
NCHAR(31),N"?"),NCHAR(30),N"?"),NCHAR(29),N"?"),NCHAR(28),N"?"),NCHAR(27),N"?"),NCHAR(26),N"?"),NCHAR(25),N"?"),NCHAR(24),N"?"),NCHAR(23),N"?"),NCHAR(22),N"?"),
NCHAR(21),N"?"),NCHAR(20),N"?"),NCHAR(19),N"?"),NCHAR(18),N"?"),NCHAR(17),N"?"),NCHAR(16),N"?"),NCHAR(15),N"?"),NCHAR(14),N"?"),NCHAR(12),N"?"),
NCHAR(11),N"?"),NCHAR(8),N"?"),NCHAR(7),N"?"),NCHAR(6),N"?"),NCHAR(5),N"?"),NCHAR(4),N"?"),NCHAR(3),N"?"),NCHAR(2),N"?"),NCHAR(1),N"?"),
NCHAR(0),
N""
)
WHERE
database_name = @database_name
OPTION (KEEPFIXED PLAN);
END CATCH;
FETCH NEXT FROM locks_cursor
INTO
@database_name;
END;
CLOSE locks_cursor;
DEALLOCATE locks_cursor;
CREATE CLUSTERED INDEX IX_SRD ON #locks (session_id, request_id, database_name);
UPDATE s
SET
s.locks =
(
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
l1.database_name COLLATE Latin1_General_Bin2
),
NCHAR(31),N"?"),NCHAR(30),N"?"),NCHAR(29),N"?"),NCHAR(28),N"?"),NCHAR(27),N"?"),NCHAR(26),N"?"),NCHAR(25),N"?"),NCHAR(24),N"?"),NCHAR(23),N"?"),NCHAR(22),N"?"),
NCHAR(21),N"?"),NCHAR(20),N"?"),NCHAR(19),N"?"),NCHAR(18),N"?"),NCHAR(17),N"?"),NCHAR(16),N"?"),NCHAR(15),N"?"),NCHAR(14),N"?"),NCHAR(12),N"?"),
NCHAR(11),N"?"),NCHAR(8),N"?"),NCHAR(7),N"?"),NCHAR(6),N"?"),NCHAR(5),N"?"),NCHAR(4),N"?"),NCHAR(3),N"?"),NCHAR(2),N"?"),NCHAR(1),N"?"),
NCHAR(0),
N""
) AS [Database/@name],
MIN(l1.query_error) AS [Database/@query_error],
(
SELECT
l2.request_mode AS [Lock/@request_mode],
l2.request_status AS [Lock/@request_status],
COUNT(*) AS [Lock/@request_count]
FROM #locks AS l2
WHERE
l1.session_id = l2.session_id
AND l1.request_id = l2.request_id
AND l2.database_name = l1.database_name
AND l2.resource_type = "DATABASE"
GROUP BY
l2.request_mode,
l2.request_status
FOR XML
PATH(""),
TYPE
) AS [Database/Locks],
(
SELECT
COALESCE(l3.object_name, "(null)") AS [Object/@name],
l3.schema_name AS [Object/@schema_name],
(
SELECT
l4.resource_type AS [Lock/@resource_type],
l4.page_type AS [Lock/@page_type],
l4.index_name AS [Lock/@index_name],
CASE
WHEN l4.object_name IS NULL THEN l4.schema_name
ELSE NULL
END AS [Lock/@schema_name],
l4.principal_name AS [Lock/@principal_name],
l4.resource_description AS [Lock/@resource_description],
l4.request_mode AS [Lock/@request_mode],
l4.request_status AS [Lock/@request_status],
SUM(l4.request_count) AS [Lock/@request_count]
FROM #locks AS l4
WHERE
l4.session_id = l3.session_id
AND l4.request_id = l3.request_id
AND l3.database_name = l4.database_name
AND COALESCE(l3.object_name, "(null)") = COALESCE(l4.object_name, "(null)")
AND COALESCE(l3.schema_name, "") = COALESCE(l4.schema_name, "")
AND l4.resource_type <> "DATABASE"
GROUP BY
l4.resource_type,
l4.page_type,
l4.index_name,
CASE
WHEN l4.object_name IS NULL THEN l4.schema_name
ELSE NULL
END,
l4.principal_name,
l4.resource_description,
l4.request_mode,
l4.request_status
FOR XML
PATH(""),
TYPE
) AS [Object/Locks]
FROM #locks AS l3
WHERE
l3.session_id = l1.session_id
AND l3.request_id = l1.request_id
AND l3.database_name = l1.database_name
AND l3.resource_type <> "DATABASE"
GROUP BY
l3.session_id,
l3.request_id,
l3.database_name,
COALESCE(l3.object_name, "(null)"),
l3.schema_name
FOR XML
PATH(""),
TYPE
) AS [Database/Objects]
FROM #locks AS l1
WHERE
l1.session_id = s.session_id
AND l1.request_id = s.request_id
AND l1.start_time IN (s.start_time, s.last_request_start_time)
AND s.recursion = 1
GROUP BY
l1.session_id,
l1.request_id,
l1.database_name
FOR XML
PATH(""),
TYPE
)
FROM #sessions s
OPTION (KEEPFIXED PLAN);
END;
IF
@find_block_leaders = 1
AND @recursion = 1
AND @output_column_list LIKE "%|[blocked_session_count|]%" ESCAPE "|"
BEGIN;
WITH
blockers AS
(
SELECT
session_id,
session_id AS top_level_session_id,
CONVERT(VARCHAR(8000), "." + CONVERT(VARCHAR(8000), session_id) + ".") AS the_path
FROM #sessions
WHERE
recursion = 1
UNION ALL
SELECT
s.session_id,
b.top_level_session_id,
CONVERT(VARCHAR(8000), b.the_path + CONVERT(VARCHAR(8000), s.session_id) + ".") AS the_path
FROM blockers AS b
JOIN #sessions AS s ON
s.blocking_session_id = b.session_id
AND s.recursion = 1
AND b.the_path NOT LIKE "%." + CONVERT(VARCHAR(8000), s.session_id) + ".%" COLLATE Latin1_General_Bin2
)
UPDATE s
SET
s.blocked_session_count = x.blocked_session_count
FROM #sessions AS s
JOIN
(
SELECT
b.top_level_session_id AS session_id,
COUNT(*) - 1 AS blocked_session_count
FROM blockers AS b
GROUP BY
b.top_level_session_id
) x ON
s.session_id = x.session_id
WHERE
s.recursion = 1;
END;
IF
@get_task_info = 2
AND @output_column_list LIKE "%|[additional_info|]%" ESCAPE "|"
AND @recursion = 1
BEGIN;
CREATE TABLE #blocked_requests
(
session_id SMALLINT NOT NULL,
request_id INT NOT NULL,
database_name sysname NOT NULL,
object_id INT,
hobt_id BIGINT,
schema_id INT,
schema_name sysname NULL,
object_name sysname NULL,
query_error NVARCHAR(2048),
PRIMARY KEY (database_name, session_id, request_id)
);
CREATE STATISTICS s_database_name ON #blocked_requests (database_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_schema_name ON #blocked_requests (schema_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_object_name ON #blocked_requests (object_name)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
CREATE STATISTICS s_query_error ON #blocked_requests (query_error)
WITH SAMPLE 0 ROWS, NORECOMPUTE;
INSERT #blocked_requests
(
session_id,
request_id,
database_name,
object_id,
hobt_id,
schema_id
)
SELECT
session_id,
request_id,
database_name,
object_id,
hobt_id,
CONVERT(INT, SUBSTRING(schema_node, CHARINDEX(" = ", schema_node) + 3, LEN(schema_node))) AS schema_id
FROM
(
SELECT
session_id,
request_id,
agent_nodes.agent_node.value("(database_name/text())[1]", "sysname") AS database_name,
agent_nodes.agent_node.value("(object_id/text())[1]", "int") AS object_id,
agent_nodes.agent_node.value("(hobt_id/text())[1]", "bigint") AS hobt_id,
agent_nodes.agent_node.value("(metadata_resource/text()[.="SCHEMA"]/../../metadata_class_id/text())[1]", "varchar(100)") AS schema_node
FROM #sessions AS s
CROSS APPLY s.additional_info.nodes("//block_info") AS agent_nodes (agent_node)
WHERE
s.recursion = 1
) AS t
WHERE
t.database_name IS NOT NULL
AND
(
t.object_id IS NOT NULL
OR t.hobt_id IS NOT NULL
OR t.schema_node IS NOT NULL
);
DECLARE blocks_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT
database_name
FROM #blocked_requests;
OPEN blocks_cursor;
FETCH NEXT FROM blocks_cursor
INTO
@database_name;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
SET @sql_n =
CONVERT(NVARCHAR(MAX), "") +
"UPDATE b " +
"SET " +
"b.schema_name = " +
"REPLACE " +
"( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"s.name COLLATE Latin1_General_Bin2, " +
"NCHAR(31),N""?""),NCHAR(30),N""?""),NCHAR(29),N""?""),NCHAR(28),N""?""),NCHAR(27),N""?""),NCHAR(26),N""?""),NCHAR(25),N""?""),NCHAR(24),N""?""),NCHAR(23),N""?""),NCHAR(22),N""?""), " +
"NCHAR(21),N""?""),NCHAR(20),N""?""),NCHAR(19),N""?""),NCHAR(18),N""?""),NCHAR(17),N""?""),NCHAR(16),N""?""),NCHAR(15),N""?""),NCHAR(14),N""?""),NCHAR(12),N""?""), " +
"NCHAR(11),N""?""),NCHAR(8),N""?""),NCHAR(7),N""?""),NCHAR(6),N""?""),NCHAR(5),N""?""),NCHAR(4),N""?""),NCHAR(3),N""?""),NCHAR(2),N""?""),NCHAR(1),N""?""), " +
"NCHAR(0), " +
N""""" " +
"), " +
"b.object_name = " +
"REPLACE " +
"( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( " +
"o.name COLLATE Latin1_General_Bin2, " +
"NCHAR(31),N""?""),NCHAR(30),N""?""),NCHAR(29),N""?""),NCHAR(28),N""?""),NCHAR(27),N""?""),NCHAR(26),N""?""),NCHAR(25),N""?""),NCHAR(24),N""?""),NCHAR(23),N""?""),NCHAR(22),N""?""), " +
"NCHAR(21),N""?""),NCHAR(20),N""?""),NCHAR(19),N""?""),NCHAR(18),N""?""),NCHAR(17),N""?""),NCHAR(16),N""?""),NCHAR(15),N""?""),NCHAR(14),N""?""),NCHAR(12),N""?""), " +
"NCHAR(11),N""?""),NCHAR(8),N""?""),NCHAR(7),N""?""),NCHAR(6),N""?""),NCHAR(5),N""?""),NCHAR(4),N""?""),NCHAR(3),N""?""),NCHAR(2),N""?""),NCHAR(1),N""?""), " +
"NCHAR(0), " +
N""""" " +
") " +
"FROM #blocked_requests AS b " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.partitions AS p ON " +
"p.hobt_id = b.hobt_id " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.objects AS o ON " +
"o.object_id = COALESCE(p.object_id, b.object_id) " +
"LEFT OUTER JOIN " + QUOTENAME(@database_name) + ".sys.schemas AS s ON " +
"s.schema_id = COALESCE(o.schema_id, b.schema_id) " +
"WHERE " +
"b.database_name = @database_name; ";
EXEC sp_executesql
@sql_n,
N"@database_name sysname",
@database_name;
END TRY
BEGIN CATCH;
UPDATE #blocked_requests
SET
query_error =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
ERROR_MESSAGE() COLLATE Latin1_General_Bin2
),
NCHAR(31),N"?"),NCHAR(30),N"?"),NCHAR(29),N"?"),NCHAR(28),N"?"),NCHAR(27),N"?"),NCHAR(26),N"?"),NCHAR(25),N"?"),NCHAR(24),N"?"),NCHAR(23),N"?"),NCHAR(22),N"?"),
NCHAR(21),N"?"),NCHAR(20),N"?"),NCHAR(19),N"?"),NCHAR(18),N"?"),NCHAR(17),N"?"),NCHAR(16),N"?"),NCHAR(15),N"?"),NCHAR(14),N"?"),NCHAR(12),N"?"),
NCHAR(11),N"?"),NCHAR(8),N"?"),NCHAR(7),N"?"),NCHAR(6),N"?"),NCHAR(5),N"?"),NCHAR(4),N"?"),NCHAR(3),N"?"),NCHAR(2),N"?"),NCHAR(1),N"?"),
NCHAR(0),
N""
)
WHERE
database_name = @database_name;
END CATCH;
FETCH NEXT FROM blocks_cursor
INTO
@database_name;
END;
CLOSE blocks_cursor;
DEALLOCATE blocks_cursor;
UPDATE s
SET
additional_info.modify
("
insert {sql:column("b.schema_name")}
as last
into (/additional_info/block_info)[1]
")
FROM #sessions AS s
INNER JOIN #blocked_requests AS b ON
b.session_id = s.session_id
AND b.request_id = s.request_id
AND s.recursion = 1
WHERE
b.schema_name IS NOT NULL;
UPDATE s
SET
additional_info.modify
("
insert {sql:column("b.object_name")}
as last
into (/additional_info/block_info)[1]
")
FROM #sessions AS s
INNER JOIN #blocked_requests AS b ON
b.session_id = s.session_id
AND b.request_id = s.request_id
AND s.recursion = 1
WHERE
b.object_name IS NOT NULL;
UPDATE s
SET
additional_info.modify
("
insert {sql:column("b.query_error")}
as last
into (/additional_info/block_info)[1]
")
FROM #sessions AS s
INNER JOIN #blocked_requests AS b ON
b.session_id = s.session_id
AND b.request_id = s.request_id
AND s.recursion = 1
WHERE
b.query_error IS NOT NULL;
END;
IF
@output_column_list LIKE "%|[program_name|]%" ESCAPE "|"
AND @output_column_list LIKE "%|[additional_info|]%" ESCAPE "|"
AND @recursion = 1
BEGIN;
DECLARE @job_id UNIQUEIDENTIFIER;
DECLARE @step_id INT;
DECLARE agent_cursor
CURSOR LOCAL FAST_FORWARD
FOR
SELECT
s.session_id,
agent_nodes.agent_node.value("(job_id/text())[1]", "uniqueidentifier") AS job_id,
agent_nodes.agent_node.value("(step_id/text())[1]", "int") AS step_id
FROM #sessions AS s
CROSS APPLY s.additional_info.nodes("//agent_job_info") AS agent_nodes (agent_node)
WHERE
s.recursion = 1
OPTION (KEEPFIXED PLAN);
OPEN agent_cursor;
FETCH NEXT FROM agent_cursor
INTO
@session_id,
@job_id,
@step_id;
WHILE @@FETCH_STATUS = 0
BEGIN;
BEGIN TRY;
DECLARE @job_name sysname;
SET @job_name = NULL;
DECLARE @step_name sysname;
SET @step_name = NULL;
SELECT
@job_name =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
j.name,
NCHAR(31),N"?"),NCHAR(30),N"?"),NCHAR(29),N"?"),NCHAR(28),N"?"),NCHAR(27),N"?"),NCHAR(26),N"?"),NCHAR(25),N"?"),NCHAR(24),N"?"),NCHAR(23),N"?"),NCHAR(22),N"?"),
NCHAR(21),N"?"),NCHAR(20),N"?"),NCHAR(19),N"?"),NCHAR(18),N"?"),NCHAR(17),N"?"),NCHAR(16),N"?"),NCHAR(15),N"?"),NCHAR(14),N"?"),NCHAR(12),N"?"),
NCHAR(11),N"?"),NCHAR(8),N"?"),NCHAR(7),N"?"),NCHAR(6),N"?"),NCHAR(5),N"?"),NCHAR(4),N"?"),NCHAR(3),N"?"),NCHAR(2),N"?"),NCHAR(1),N"?"),
NCHAR(0),
N"?"
),
@step_name =
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
s.step_name,
NCHAR(31),N"?"),NCHAR(30),N"?"),NCHAR(29),N"?"),NCHAR(28),N"?"),NCHAR(27),N"?"),NCHAR(26),N"?"),NCHAR(25),N"?"),NCHAR(24),N"?"),NCHAR(23),N"?"),NCHAR(22),N"?"),
NCHAR(21),N"?"),NCHAR(20),N"?"),NCHAR(19),N"?"),NCHAR(18),N"?"),NCHAR(17),N"?"),NCHAR(16),N"?"),NCHAR(15),N"?"),NCHAR(14),N"?"),NCHAR(12),N"?"),
NCHAR(11),N"?"),NCHAR(8),N"?"),NCHAR(7),N"?"),NCHAR(6),N"?"),NCHAR(5),N"?"),NCHAR(4),N"?"),NCHAR(3),N"?"),NCHAR(2),N"?"),NCHAR(1),N"?"),
NCHAR(0),
N"?"
)
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb..sysjobsteps AS s ON
j.job_id = s.job_id
WHERE
j.job_id = @job_id
AND s.step_id = @step_id;
IF @job_name IS NOT NULL
BEGIN;
UPDATE s
SET
additional_info.modify
("
insert text{sql:variable("@job_name")}
into (/additional_info/agent_job_info/job_name)[1]
")
FROM #sessions AS s
WHERE
s.session_id = @session_id
OPTION (KEEPFIXED PLAN);
UPDATE s
SET
additional_info.modify
("
insert text{sql:variable("@step_name")}
into (/additional_info/agent_job_info/step_name)[1]
")
FROM #sessions AS s
WHERE
s.session_id = @session_id
OPTION (KEEPFIXED PLAN);
END;
END TRY
BEGIN CATCH;
DECLARE @msdb_error_message NVARCHAR(256);
SET @msdb_error_message = ERROR_MESSAGE();
UPDATE s
SET
additional_info.modify
("
insert {sql:variable("@msdb_error_message")}
as last
into (/additional_info/agent_job_info)[1]
")
FROM #sessions AS s
WHERE
s.session_id = @session_id
AND s.recursion = 1
OPTION (KEEPFIXED PLAN);
END CATCH;
FETCH NEXT FROM agent_cursor
INTO
@session_id,
@job_id,
@step_id;
END;
CLOSE agent_cursor;
DEALLOCATE agent_cursor;
END;
IF
@delta_interval > 0
AND @recursion <> 1
BEGIN;
SET @recursion = 1;
DECLARE @delay_time CHAR(12);
SET @delay_time = CONVERT(VARCHAR, DATEADD(second, @delta_interval, 0), 114);
WAITFOR DELAY @delay_time;
GOTO REDO;
END;
END;
SET @sql =
--Outer column list
CONVERT
(
VARCHAR(MAX),
CASE
WHEN
@destination_table <> ""
AND @return_schema = 0
THEN "INSERT " + @destination_table + " "
ELSE ""
END +
"SELECT " +
@output_column_list + " " +
CASE @return_schema
WHEN 1 THEN "INTO #session_schema "
ELSE ""
END
--End outer column list
) +
--Inner column list
CONVERT
(
VARCHAR(MAX),
"FROM " +
"( " +
"SELECT " +
"session_id, " +
--[dd hh:mm:ss.mss]
CASE
WHEN @format_output IN (1, 2) THEN
"CASE " +
"WHEN elapsed_time < 0 THEN " +
"RIGHT " +
"( " +
"REPLICATE(""0"", max_elapsed_length) + CONVERT(VARCHAR, (-1 * elapsed_time) / 86400), " +
"max_elapsed_length " +
") + " +
"RIGHT " +
"( " +
"CONVERT(VARCHAR, DATEADD(second, (-1 * elapsed_time), 0), 120), " +
"9 " +
") + " +
""".000"" " +
"ELSE " +
"RIGHT " +
"( " +
"REPLICATE(""0"", max_elapsed_length) + CONVERT(VARCHAR, elapsed_time / 86400000), " +
"max_elapsed_length " +
") + " +
"RIGHT " +
"( " +
"CONVERT(VARCHAR, DATEADD(second, elapsed_time / 1000, 0), 120), " +
"9 " +
") + " +
"""."" + " +
"RIGHT(""000"" + CONVERT(VARCHAR, elapsed_time % 1000), 3) " +
"END AS [dd hh:mm:ss.mss], "
ELSE
""
END +
--[dd hh:mm:ss.mss (avg)] / avg_elapsed_time
CASE
WHEN @format_output IN (1, 2) THEN
"RIGHT " +
"( " +
"""00"" + CONVERT(VARCHAR, avg_elapsed_time / 86400000), " +
"2 " +
") + " +
"RIGHT " +
"( " +
"CONVERT(VARCHAR, DATEADD(second, avg_elapsed_time / 1000, 0), 120), " +
"9 " +
") + " +
"""."" + " +
"RIGHT(""000"" + CONVERT(VARCHAR, avg_elapsed_time % 1000), 3) AS [dd hh:mm:ss.mss (avg)], "
ELSE
"avg_elapsed_time, "
END +
--physical_io
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io))) OVER() - LEN(CONVERT(VARCHAR, physical_io))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io), 1), 19)) AS "
ELSE ""
END + "physical_io, " +
--reads
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads))) OVER() - LEN(CONVERT(VARCHAR, reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads), 1), 19)) AS "
ELSE ""
END + "reads, " +
--physical_reads
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads))) OVER() - LEN(CONVERT(VARCHAR, physical_reads))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads), 1), 19)) AS "
ELSE ""
END + "physical_reads, " +
--writes
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes))) OVER() - LEN(CONVERT(VARCHAR, writes))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes), 1), 19)) AS "
ELSE ""
END + "writes, " +
--tempdb_allocations
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations), 1), 19)) AS "
ELSE ""
END + "tempdb_allocations, " +
--tempdb_current
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current), 1), 19)) AS "
ELSE ""
END + "tempdb_current, " +
--CPU
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CPU))) OVER() - LEN(CONVERT(VARCHAR, CPU))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU), 1), 19)) AS "
ELSE ""
END + "CPU, " +
--context_switches
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches))) OVER() - LEN(CONVERT(VARCHAR, context_switches))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches), 1), 19)) AS "
ELSE ""
END + "context_switches, " +
--used_memory
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory))) OVER() - LEN(CONVERT(VARCHAR, used_memory))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory), 1), 19)) AS "
ELSE ""
END + "used_memory, " +
CASE
WHEN @output_column_list LIKE "%|_delta|]%" ESCAPE "|" THEN
--physical_io_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"AND physical_io_delta >= 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_io_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_io_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_io_delta), 1), 19)) "
ELSE "physical_io_delta "
END +
"ELSE NULL " +
"END AS physical_io_delta, " +
--reads_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"AND reads_delta >= 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, reads_delta))) OVER() - LEN(CONVERT(VARCHAR, reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, reads_delta), 1), 19)) "
ELSE "reads_delta "
END +
"ELSE NULL " +
"END AS reads_delta, " +
--physical_reads_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"AND physical_reads_delta >= 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, physical_reads_delta))) OVER() - LEN(CONVERT(VARCHAR, physical_reads_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, physical_reads_delta), 1), 19)) "
ELSE "physical_reads_delta "
END +
"ELSE NULL " +
"END AS physical_reads_delta, " +
--writes_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"AND writes_delta >= 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, writes_delta))) OVER() - LEN(CONVERT(VARCHAR, writes_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, writes_delta), 1), 19)) "
ELSE "writes_delta "
END +
"ELSE NULL " +
"END AS writes_delta, " +
--tempdb_allocations_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"AND tempdb_allocations_delta >= 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_allocations_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_allocations_delta), 1), 19)) "
ELSE "tempdb_allocations_delta "
END +
"ELSE NULL " +
"END AS tempdb_allocations_delta, " +
--tempdb_current_delta
--this is the only one that can (legitimately) go negative
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tempdb_current_delta))) OVER() - LEN(CONVERT(VARCHAR, tempdb_current_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tempdb_current_delta), 1), 19)) "
ELSE "tempdb_current_delta "
END +
"ELSE NULL " +
"END AS tempdb_current_delta, " +
--CPU_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"THEN " +
"CASE " +
"WHEN " +
"thread_CPU_delta > CPU_delta " +
"AND thread_CPU_delta > 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, thread_CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, thread_CPU_delta), 1), 19)) "
ELSE "thread_CPU_delta "
END +
"WHEN CPU_delta >= 0 THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, thread_CPU_delta + CPU_delta))) OVER() - LEN(CONVERT(VARCHAR, CPU_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, CPU_delta), 1), 19)) "
ELSE "CPU_delta "
END +
"ELSE NULL " +
"END " +
"ELSE " +
"NULL " +
"END AS CPU_delta, " +
--context_switches_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"AND context_switches_delta >= 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, context_switches_delta))) OVER() - LEN(CONVERT(VARCHAR, context_switches_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, context_switches_delta), 1), 19)) "
ELSE "context_switches_delta "
END +
"ELSE NULL " +
"END AS context_switches_delta, " +
--used_memory_delta
"CASE " +
"WHEN " +
"first_request_start_time = last_request_start_time " +
"AND num_events = 2 " +
"AND used_memory_delta >= 0 " +
"THEN " +
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, used_memory_delta))) OVER() - LEN(CONVERT(VARCHAR, used_memory_delta))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, used_memory_delta), 1), 19)) "
ELSE "used_memory_delta "
END +
"ELSE NULL " +
"END AS used_memory_delta, "
ELSE ""
END +
--tasks
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, tasks))) OVER() - LEN(CONVERT(VARCHAR, tasks))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, tasks), 1), 19)) "
ELSE ""
END + "tasks, " +
"status, " +
"wait_info, " +
"locks, " +
"tran_start_time, " +
"LEFT(tran_log_writes, LEN(tran_log_writes) - 1) AS tran_log_writes, " +
--open_tran_count
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, open_tran_count))) OVER() - LEN(CONVERT(VARCHAR, open_tran_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, open_tran_count), 1), 19)) AS "
ELSE ""
END + "open_tran_count, " +
--sql_command
CASE @format_output
WHEN 0 THEN "REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_command), """"+CHAR(13)+CHAR(10), """"), CHAR(13)+CHAR(10)+""--?>"", """") AS "
ELSE ""
END + "sql_command, " +
--sql_text
CASE @format_output
WHEN 0 THEN "REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), sql_text), """"+CHAR(13)+CHAR(10), """"), CHAR(13)+CHAR(10)+""--?>"", """") AS "
ELSE ""
END + "sql_text, " +
"query_plan, " +
"blocking_session_id, " +
--blocked_session_count
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, blocked_session_count))) OVER() - LEN(CONVERT(VARCHAR, blocked_session_count))) + LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, LEFT(CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1), 19)) AS "
ELSE ""
END + "blocked_session_count, " +
--percent_complete
CASE @format_output
WHEN 1 THEN "CONVERT(VARCHAR, SPACE(MAX(LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) OVER() - LEN(CONVERT(VARCHAR, CONVERT(MONEY, percent_complete), 2))) + CONVERT(CHAR(22), CONVERT(MONEY, percent_complete), 2)) AS "
WHEN 2 THEN "CONVERT(VARCHAR, CONVERT(CHAR(22), CONVERT(MONEY, blocked_session_count), 1)) AS "
ELSE ""
END + "percent_complete, " +
"host_name, " +
"login_name, " +
"database_name, " +
"program_name, " +
"additional_info, " +
"start_time, " +
"login_time, " +
"CASE " +
"WHEN status = N""sleeping"" THEN NULL " +
"ELSE request_id " +
"END AS request_id, " +
"GETDATE() AS collection_time "
--End inner column list
) +
--Derived table and INSERT specification
CONVERT
(
VARCHAR(MAX),
"FROM " +
"( " +
"SELECT TOP(2147483647) " +
"*, " +
"CASE " +
"MAX " +
"( " +
"LEN " +
"( " +
"CONVERT " +
"( " +
"VARCHAR, " +
"CASE " +
"WHEN elapsed_time < 0 THEN " +
"(-1 * elapsed_time) / 86400 " +
"ELSE " +
"elapsed_time / 86400000 " +
"END " +
") " +
") " +
") OVER () " +
"WHEN 1 THEN 2 " +
"ELSE " +
"MAX " +
"( " +
"LEN " +
"( " +
"CONVERT " +
"( " +
"VARCHAR, " +
"CASE " +
"WHEN elapsed_time < 0 THEN " +
"(-1 * elapsed_time) / 86400 " +
"ELSE " +
"elapsed_time / 86400000 " +
"END " +
") " +
") " +
") OVER () " +
"END AS max_elapsed_length, " +
CASE
WHEN @output_column_list LIKE "%|_delta|]%" ESCAPE "|" THEN
"MAX(physical_io * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(physical_io * recursion) OVER (PARTITION BY session_id, request_id) AS physical_io_delta, " +
"MAX(reads * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(reads * recursion) OVER (PARTITION BY session_id, request_id) AS reads_delta, " +
"MAX(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(physical_reads * recursion) OVER (PARTITION BY session_id, request_id) AS physical_reads_delta, " +
"MAX(writes * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(writes * recursion) OVER (PARTITION BY session_id, request_id) AS writes_delta, " +
"MAX(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(tempdb_allocations * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_allocations_delta, " +
"MAX(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(tempdb_current * recursion) OVER (PARTITION BY session_id, request_id) AS tempdb_current_delta, " +
"MAX(CPU * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(CPU * recursion) OVER (PARTITION BY session_id, request_id) AS CPU_delta, " +
"MAX(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(thread_CPU_snapshot * recursion) OVER (PARTITION BY session_id, request_id) AS thread_CPU_delta, " +
"MAX(context_switches * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(context_switches * recursion) OVER (PARTITION BY session_id, request_id) AS context_switches_delta, " +
"MAX(used_memory * recursion) OVER (PARTITION BY session_id, request_id) + " +
"MIN(used_memory * recursion) OVER (PARTITION BY session_id, request_id) AS used_memory_delta, " +
"MIN(last_request_start_time) OVER (PARTITION BY session_id, request_id) AS first_request_start_time, "
ELSE ""
END +
"COUNT(*) OVER (PARTITION BY session_id, request_id) AS num_events " +
"FROM #sessions AS s1 " +
CASE
WHEN @sort_order = "" THEN ""
ELSE
"ORDER BY " +
@sort_order
END +
") AS s " +
"WHERE " +
"s.recursion = 1 " +
") x " +
"OPTION (KEEPFIXED PLAN); " +
"" +
CASE @return_schema
WHEN 1 THEN
"SET @schema = " +
"""CREATE TABLE ( "" + " +
"STUFF " +
"( " +
"( " +
"SELECT " +
""","" + " +
"QUOTENAME(COLUMN_NAME) + "" "" + " +
"DATA_TYPE + " +
"CASE " +
"WHEN DATA_TYPE LIKE ""%char"" THEN ""("" + COALESCE(NULLIF(CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH), ""-1""), ""max"") + "") "" " +
"ELSE "" "" " +
"END + " +
"CASE IS_NULLABLE " +
"WHEN ""NO"" THEN ""NOT "" " +
"ELSE """" " +
"END + ""NULL"" AS [text()] " +
"FROM tempdb.INFORMATION_SCHEMA.COLUMNS " +
"WHERE " +
"TABLE_NAME = (SELECT name FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(""tempdb..#session_schema"")) " +
"ORDER BY " +
"ORDINAL_POSITION " +
"FOR XML " +
"PATH("""") " +
"), + " +
"1, " +
"1, " +
""""" " +
") + " +
""")""; "
ELSE ""
END
--End derived table and INSERT specification
);
SET @sql_n = CONVERT(NVARCHAR(MAX), @sql);
EXEC sp_executesql
@sql_n,
N"@schema VARCHAR(MAX) OUTPUT",
@schema OUTPUT;
END;
GO
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
历年真题答案解析
备考技巧名师总结
高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1148
183.71 KB下载数642
644.84 KB下载数2756
相关文章
发现更多好内容- 在 Java 中，Integer 究竟有哪些作用呢？(java中integer的作用是什么)
- Java 实体类如何进行赋值操作？(java实体类怎么赋值)
- Java 中 switch case 的具体用法有哪些？(switchcase在java中的用法)
- 如何在 Java EMqtt 中实现消息确认机制？(Java EMqtt怎样实现消息确认机制)
- Java 性能调优的有效手段有哪些？(Java性能调优有何有效手段)
- 探讨Uncomtrade数据库的安全防护措施
- Java 如何获取某个接口的实现类？(Java怎么获取某个接口的实现类)
- Java中如何正确使用 collections.shuffle 方法？(Java中collections.shuffle怎么使用)
- Java Swing 中常用的布局有哪些？(java swing常用布局有哪些)
- 如何通过 Java Reflection 获取泛型信息？(java reflection如何获取泛型信息)
猜你喜欢
AI推送时光机常用SQL Server进行性能优化语句
![常用SQL Server进行性能优化语句]()
数据库2015-10-19对SQL语句进行分析和优化
数据库2024-04-0252条SQL语句教你性能优化
数据库2024-04-02sql server查询语句阻塞优化性能问题怎么解决
数据库2023-06-29MyBatis中怎么对SQL语句进行性能分析和调优
![MyBatis中怎么对SQL语句进行性能分析和调优]()
数据库2024-05-08MySQL高性能SQL语句的编写和优化
数据库2024-04-02怎么对MySQL SQL语句性能进行分析
数据库2024-04-02常用SQL语句优化技巧有哪些
数据库2024-04-02SQL语句性能优化的策略有哪些
数据库2023-06-28Oracle 学习之性能优化（一）SQL语句处理
数据库2024-04-02MongoDB和SQL语句的性能比较及优化策略？
![MongoDB和SQL语句的性能比较及优化策略？]()
数据库2023-12-18如何优化MySQL数据库中的SQL语句性能？
![如何优化MySQL数据库中的SQL语句性能？]()
数据库2023-12-17oracle中对于复杂的SQL语句怎么进行优化
数据库2024-04-02MySQL常用30种SQL查询语句优化方法
数据库2023-10-23如何使用SQL语句在MongoDB中进行索引创建和优化？
![如何使用SQL语句在MongoDB中进行索引创建和优化？]()
数据库2023-12-17如何使用SQL语句在MySQL中进行数据索引和优化？
![如何使用SQL语句在MySQL中进行数据索引和优化？]()
数据库2023-12-17sql语句查询性能的优化思想（个人理解）
数据库2024-04-02数据库技能实战进阶之常用结构化sql语句（中）
数据库2024-04-02数据库技能实战进阶之常用结构化sql语句（上）
数据库2024-04-02 咦！没有更多了？去看看其它编程学习网 内容吧 
