SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROC [dbo].[p_lockinfo]
@kill_lock_spid BIT = 1 , --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock BIT = 1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
AS
DECLARE @count INT ,
@s VARCHAR(MAX) ,
@i INT
SELECT id = IDENTITY( INT,1,1 ),
标志 ,
进程ID = spid ,
线程ID = kpid ,
块进程ID = blocked ,
数据库ID = dbid ,
数据库名 = DB_NAME(dbid) ,
用户ID = uid ,
用户名 = loginame ,
累计CPU时间 = cpu ,
登陆时间 = login_time ,
打开事务数 = open_tran ,
进程状态 = status ,
工作站名 = hostname ,
应用程序名 = program_name ,
工作站进程ID = hostprocess ,
域名 = nt_domain ,
网卡地址 = net_address,
sql_handle,
stmt_start,
stmt_end
INTO #t
FROM ( SELECT 标志 = '死锁的进程' ,
spid ,
kpid ,
a.blocked ,
dbid ,
uid ,
loginame ,
cpu ,
login_time ,
open_tran ,
status ,
hostname ,
program_name ,
hostprocess ,
nt_domain ,
net_address ,
s1 = a.spid ,
s2 = 0,
sql_handle,
stmt_start,
stmt_end
FROM master..sysprocesses a
JOIN ( SELECT blocked
FROM master..sysprocesses
GROUP BY blocked
) b ON a.spid = b.blocked
WHERE a.blocked = 0
UNION ALL
SELECT '|_牺牲品_>' ,
spid ,
kpid ,
blocked ,
dbid ,
uid ,
loginame ,
cpu ,
login_time ,
open_tran ,
status ,
hostname ,
program_name ,
hostprocess ,
nt_domain ,
net_address ,
s1 = blocked ,
s2 = 1,
sql_handle,
stmt_start,
stmt_end
FROM master..sysprocesses a
WHERE blocked <> 0
) a
ORDER BY s1 ,
s2
SELECT @count = @@rowcount ,
@i = 1
IF @count = 0
AND @show_spid_if_nolock = 1
BEGIN
INSERT #t
SELECT 标志 = '正常的进程' ,
spid ,
kpid ,
blocked ,
dbid ,
DB_NAME(dbid) ,
uid ,
loginame ,
cpu ,
login_time ,
open_tran ,
status ,
hostname ,
program_name ,
hostprocess ,
nt_domain ,
net_address,
sql_handle,
stmt_start,
stmt_end
FROM master..sysprocesses
SET @count = @@rowcount
END
IF @count > 0
BEGIN
CREATE TABLE #t1
(
id INT IDENTITY(1, 1) ,
a NVARCHAR(3000) ,
b INT ,
EventInfo NVARCHAR(2000)
)
IF @kill_lock_spid = 1
BEGIN
DECLARE @spid VARCHAR(50) ,
@标志 VARCHAR(50)
WHILE @i <= @count
BEGIN
SELECT @spid = 进程ID ,
@标志 = 标志
FROM #t
WHERE id = @i
INSERT #t1
EXEC ( 'dbcc inputbuffer(' + @spid + ')'
)
IF @标志 = '死锁的进程'
EXEC('kill '+@spid)
SET @i = @i + 1
END
END
ELSE
WHILE @i <= @count
BEGIN
SELECT @s = 'dbcc inputbuffer('
+ CAST(进程ID AS VARCHAR) + ')'
FROM #t
WHERE id = @i
INSERT #t1
EXEC ( @s
)
SET @i = @i + 1
END
SELECT 标志 ,
数据库名 ,
进程的SQL语句 = b.EventInfo,
sqlText=substring(qt.text,a.stmt_start/2,
(case when a.stmt_end = -1
then len(convert(nvarchar(max), qt.text)) * 2
else a.stmt_end end -a.stmt_start)/2
) ,
进程状态,
用户名,
累计CPU时间,
登陆时间 ,
工作站名 ,
应用程序名,
进程ID ,
线程ID
FROM #t a
JOIN #t1 b ON a.id = b.id
OUTER apply sys.dm_exec_sql_text(a.sql_handle) as qt
END
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
--==========================
--创建人： zxs 20110728
--==========================
CREATE PROCEDURE [dbo].[p_cpu]
@TopNum INT =NULL,
@Last_Execution_Time VARCHAR(50) = NULL
AS
SET @TopNum = ISNULL(@TopNum,50) --默认前50条
SET @Last_Execution_Time = ISNULL(@Last_Execution_Time,'2012-01-01') --默认前50条
/*
SELECT TOP (@TopNum)
qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(case when qs.statement_end_offset = -1
then DATALENGTH(qt.text)
else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)
as [查询语句], qt.text [所在存储过程],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE qs.last_execution_time >=@Last_Execution_Time --限定时间
ORDER BY
[平均消耗CPU 时间(ms)] DESC
*/
SELECT SUM([平均消耗CPU 时间(ms)])[平均消耗CPU 时间(ms)],
SUM([总消耗CPU 时间(ms)]) [总消耗CPU 时间(ms)],
SUM([运行次数])[运行次数],[查询语句],[所在存储过程],
dbid, dbname,objectid,ObjectName
FROM (
SELECT TOP (50)
qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
dbo.SQLSig(SUBSTRING(qt.text,qs.statement_start_offset/2+1, --利用sqlsig函数进行参数化，以屏蔽传来的参数，使之标准化
(case when qs.statement_end_offset = -1
then DATALENGTH(qt.text)
else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1),4000)
as [查询语句], qt.text [所在存储过程],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE qs.last_execution_time >=CONVERT(VARCHAR(10),GETDATE(),120)
ORDER BY [平均消耗CPU 时间(ms)] DESC
)M
GROUP BY [查询语句],[所在存储过程],dbid, dbname,objectid,ObjectName
ORDER BY [平均消耗CPU 时间(ms)] DESC
GO
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
--==========================
--创建人： zxs 20120420
--查询当前的锁,及相关sql语句
--==========================
CREATE PROCEDURE [dbo].[p_curwaitdropindex]
AS
Select d.database_id,d.name,t.object_id, ix.index_id,user_updates, (user_seeks+user_scans) AS use_Count,t.name AS tb_name,ix.name AS ix_name,sc.name AS col_name
from sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id=ius.database_id
JOIN sys.tables t ON ius.object_id = t.object_id
JOIN sys.indexes ix ON t.object_id = ix.object_id AND ix.index_id = ius.index_id
JOIN sys.index_columns ixc ON t.object_id = ixc.object_id AND ix.index_id = ixc.index_id
JOIN sys.columns sc ON t.object_id = sc.object_id AND ixc.column_id = sc.column_id
where user_updates > 10 * (user_seeks+user_scans)
and ius.index_id > 1
AND CHARINDEX('merge',t.name) <1
order by user_updates / (user_seeks+user_scans+1) DESC
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
--==========================
--创建人： zxs 20110728
--==========================
CREATE PROCEDURE [dbo].[p_curRun]
AS
SELECT DB_NAME(r.database_id) DB,s.session_id,
substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)/2
) as 'SQL statement',
qt.text batch,s.status,c.client_net_address,s.login_name, s.program_name,s.host_name,
s.login_time,c.connect_time,s.last_request_start_time, s.last_request_end_time,s.session_id,
s.host_process_id, s.client_version, s.client_interface_name,c.net_transport, c.net_packet_size,
r.request_id, r.start_time, r.status, r.command, r.user_id, r.blocking_session_id, r.wait_type,r.wait_time,
r.last_wait_type, r.wait_resource, r.open_transaction_count,r.transaction_id, r.percent_complete, r.cpu_time, r.reads, r.writes,r.granted_query_memory
FROM Sys.dm_exec_connections c
JOIN Sys.dm_exec_sessions s ON c.session_id = s.session_id
JOIN Sys.dm_exec_requests r ON s.session_id = r.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) as qt
WHERE r.status = 'running' AND s.session_id != @@SPID
ORDER BY c.client_net_address,s.login_name
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
--==========================
--创建人： zxs 20120420
--查询当前的锁,及相关sql语句
--==========================
CREATE PROCEDURE [dbo].[p_curlock]
AS
SELECT TL.resource_type AS ResType
,TL.resource_description AS ResDescr
,TL.request_mode AS ReqMode
,TL.request_type AS ReqType
,TL.request_status AS ReqStatus
,TL.request_owner_type AS ReqOwnerType
,TAT.[name] AS TransName
,TAT.transaction_begin_time AS TransBegin
,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
,ES.session_id AS S_Id
,ES.login_name AS LoginName
,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
,PARIDX.name AS IndexName
,ES.host_name AS HostName
,ES.program_name AS ProgramName
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE REQ.statement_end_offset END - REQ.statement_start_offset) / 2
) AS SqlStatement
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_exec_sessions AS ES
ON TL.request_session_id = ES.session_id
LEFT JOIN sys.dm_tran_active_transactions AS TAT
ON TL.request_owner_id = TAT.transaction_id
AND TL.request_owner_type = 'TRANSACTION'
LEFT JOIN sys.objects AS OBJ
ON TL.resource_associated_entity_id = OBJ.object_id
AND TL.resource_type = 'OBJECT'
LEFT JOIN sys.partitions AS PAR
ON TL.resource_associated_entity_id = PAR.hobt_id
AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')
LEFT JOIN sys.objects AS PAROBJ
ON PAR.object_id = PAROBJ.object_id
LEFT JOIN sys.indexes AS PARIDX
ON PAR.object_id = PARIDX.object_id
AND PAR.index_id = PARIDX.index_id
LEFT JOIN sys.dm_exec_requests AS REQ
ON TAT.transaction_id = REQ.transaction_id
outer APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST
WHERE TL.resource_database_id = DB_ID()
AND ES.session_id <> @@Spid
AND TL.request_mode like '%x%'
ORDER BY TL.resource_type
,TL.request_mode
,TL.request_type
,TL.request_status
,ObjectName
,ES.login_name;
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
--==========================
--创建人： zxs 20120420
--查询当前事务
--==========================
CREATE PROCEDURE [dbo].[p_curtran]
AS
SELECT CASE WHEN TDT.database_id = 32767
THEN 'MSSQLSystemResource'
ELSE DB.name END AS DatabaseName
,REQ.start_time AS ReqStart
,TAT.transaction_begin_time AS TransBegin
,TAT.name AS TransName
,CASE TDT.database_transaction_type
WHEN 1 THEN N'Read/Write'
WHEN 2 THEN N'Read-only'
WHEN 3 THEN N'System'
ELSE N'Unkown' END AS TransType
,CASE TAT.transaction_state
WHEN 0 THEN N'Not initialized'
WHEN 1 THEN N'Not started'
WHEN 2 THEN N'Active'
WHEN 3 THEN N'Ended'
WHEN 4 THEN N'DTC active'
WHEN 5 THEN N'Preparing'
WHEN 6 THEN N'Committing'
WHEN 7 THEN N'Being rolled back'
WHEN 8 THEN N'Rolled back'
ELSE N'Unkown'
END AS TransState
,REQ.[status] AS ReqStatus
,TDT.database_transaction_log_record_count AS LogRec
,TDT.database_transaction_log_bytes_used AS LogBytes
,SES.login_name AS LoginName
,REQ.wait_type AS ReqWaitType
,REQ.percent_complete AS [ReqCompl%]
,REQ.command AS ReqCommand
,SUBSTRING(EST.text
,1 + REQ.statement_start_offset / 2
,(CASE WHEN REQ.statement_end_offset = -1
THEN LEN(convert(nvarchar(max), EST.text)) * 2
ELSE REQ.statement_end_offset END - REQ.statement_start_offset) / 2
) AS SqlStatement
FROM sys.dm_tran_active_transactions AS TAT
INNER JOIN sys.dm_tran_database_transactions AS TDT
ON TAT.transaction_id = TDT.transaction_id
INNER JOIN sys.databases AS DB
ON TDT.database_id = DB.database_id
LEFT JOIN sys.dm_tran_session_transactions AS TST
ON TAT.transaction_id = TST.transaction_id
LEFT JOIN sys.dm_exec_requests AS REQ
ON TAT.transaction_id = REQ.transaction_id
LEFT JOIN sys.dm_exec_sessions AS SES
ON REQ.session_id = SES.session_id
CROSS APPLY sys.dm_exec_sql_text(REQ.sql_handle) AS EST
WHERE TAT.transaction_id > 255
AND ISNULL(REQ.session_id, -1) <> @@SPID
AND TDT.database_id <> DB_ID(N'tempdb')
ORDER BY DatabaseName
,TransBegin
,TransName;
GO