info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
Use T-SQL to log Active SPID Execution Duration
by BF (Principal Consultant; Architecture; Engineering)
2015-06-28
Deployment Steps:
#1. Create a new Table - dbo.LOG_ACTIVE_SPID_EXECUTING_DURATION
#2. Create a new stored procedure - dbo.sp_Active_SPIDS
#3. Create a new SQL Agent Job - Log Active SPID Execution Duration
- Set Schedule (as required)
- Set Filtering (per login, per app, per duration > X)
Create Table:
CREATE TABLE [dbo].[LOG_ACTIVE_SPID_EXECUTING_DURATION](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[session_id] [int] NOT NULL,
[status] [varchar](150) NULL,
[Blk_by] [int] NULL,
[wait_type] [varchar](150) NULL,
[wait_resource] [varchar](250) NULL,
[wait_sec] [decimal](18, 6) NULL,
[cpu_time] [int] NULL,
[logical_reads] [int] NULL,
[reads] [int] NULL,
[writes] [int] NULL,
[elaps_sec] [decimal](18, 6) NULL,
[dbname] [varchar](30) NULL,
[statement_text] [varchar](max) NULL,
[command_text] [varchar](max) NULL,
[command] [varchar](100) NULL,
[login_name] [varchar](100) NULL,
[host_name] [varchar](100) NULL,
[program_name] [varchar](250) NULL,
[last_request_end_time] [datetime] NULL,
[login_time] [datetime] NULL,
[open_transaction_count] [int] NULL,
[inserteddate] [datetime] NULL,
CONSTRAINT [PK_LOG_ACTIVE_SPID_EXECUTING_DURATION] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Create Stored Procedure:
/****** Object: StoredProcedure [dbo].[sp_Active_SPIDs] Script Date: 06/18/2015 15:40:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[sp_Active_SPIDS]
as
SELECT s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000.0) 'Wait Sec',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000.0) 'Elaps Sec',
db_name(r.database_id) as DBName,
'"'+Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1)+'"' AS statement_text,
'"'+Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)),
'')+'"' AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc, r.status,
r.blocking_session_id,
s.session_id
GO
SQL Agent Job:
USE
GO
Create Table #TMPMS (session_id int, [status] varchar(150), Blk_by int,
wait_type varchar(150), wait_resource varchar(250),
wait_sec decimal(18,6), cpu_time int, logical_reads int, reads int, writes int,
elaps_sec decimal(18,6),
dbname varchar(30), statement_text varchar(max), command_text varchar(max),
command varchar(100), login_name varchar(100),
[host_name] varchar(100), [program_name] varchar(250), last_request_end_time datetime,
login_time datetime, open_transaction_count int)
Insert into #TMPMS
Exec [dbo].[sp_Active_SPID]
If exists (select * from #TMPMS where elaps_sec > 15.000000 and login_name not in ('xyz',)
Begin
--Log
Insert into OPS.dbo.LOG_ACTIVE_SPID_EXECUTING_DURATION
(session_id,[status],Blk_by,wait_type,wait_resource,wait_sec,cpu_time,logical_reads,reads,
writes,elaps_sec,dbname,statement_text,command_text,command,
login_name,[host_name],[program_name],last_request_end_time,login_time,open_transaction_count)
Select * from #TMPMS
End
Drop Table #TMPMS
|
|
|
|
|
|
|
|
|
|