SQL Monitoring in Power BI - Lock Wait Time

 

First of all, I prepared the data in SQL, this is the statement:

--===============================================================

--// Created a table to save all the Locks

--===============================================================

USE [DBA]
GO
 
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[ServerLock](
[ServerLockID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[SPID] [int] NULL,
[Status] [varchar](1000) NULL,
[Login] [sysname] NULL,
[HostName] [sysname] NULL,
[BlkBy] [int] NULL,
[DBName] [sysname] NULL,
[Command] [varchar](1000) NULL,
[CPUTime_ms] [bigint] NULL,
[waittime_ms] [bigint] NULL,
[Open_Tran] [varchar](4) NULL,
[waitresource] [nvarchar](1000) NULL,
[lastwaittype] [nvarchar](1000) NULL,
[memusage_pages] [bigint] NULL,
[DiskIO_RW] [int] NULL,
[LastBatch] [datetime] NULL,
[ProgramName] [varchar](1000) NULL,
[ModifiedDateOn] [smalldatetime] NULL,
[DataHash] [binary](1) NULL,
[CreatedDateOn] [datetime] NOT NULL,
[IsOperational] [bit] NULL,
[LockDateID] [int] NULL,
 CONSTRAINT [ServerLockID_pk] PRIMARY KEY CLUSTERED 
(
[ServerLockID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[ServerLock] ADD  DEFAULT (getdate()) FOR [ModifiedDateOn]
GO
 

--===============================================================

--// Created a SP to gathers all the Locks

--===============================================================

USE [DBA]
GO
/****** Object:  StoredProcedure [dbo].[sp_DBAWEB_Lock]    Script Date: 15/10/2018 4:47:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE OR ALTER   proc [dbo].[sp_DBAWEB_Lock] (
 
@OperatorGroup Varchar(50) = 'DBA',
@IntervalTime Int = 5,
@SendEmail char(1) = 'N',
@AlertName varchar(200) = 'Lock Wait Time',
@AlertWindowStarts Int = '6',
@AlertWindowStops Int = '21'
)
as
/*****************************************************************************************************************
 
Name: dbo.sp_DBAWEB_lock
 
Shows the existents transaction locks and evidence causes.
 
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Oct 2018 Leandro Buffone Created Initial Version 
*****************************************************************************************************************
 
Usage:
set statistics io on
set statistics time on
exec dbo.sp_DBAWEB_lock @SendEmail = 'Y'
 
*****************************************************************************************************************/
 
 
If (datepart(Hour, getdate()) between @AlertWindowStarts and @AlertWindowStops) Begin
 
DECLARE @ID varchar(10),
@EmailOperator varchar(400),
@AlertComments varchar(800),
@strEmailBody varchar(Max),
@nOccurence varchar(4),
@strEmailSubject varchar(400),
@strSQLTranCause varchar(max),
@strSQL nvarchar(400),
@DataHash binary, 
@ServerName nvarchar(100),
@PerformanceIndicatorCategoryName nvarchar(100),
@PerformanceIndicatorName nvarchar(100),
@PerformanceAVGIndicatorValue int,
@PerformanceIndicatorValue int,
@IsOperational int
 
Set @ServerName = @@ServerName
Set @PerformanceIndicatorCategoryName = 'Lock'
Set @PerformanceIndicatorName = 'Lock Wait Time'
Set @PerformanceAVGIndicatorValue = 1
Set @PerformanceIndicatorValue = 1
 
Set @DataHash = (Select hashbytes('MD5',concat(getdate(),'')))
Set @IsOperational = (Case When (datepart(hour, getdate()) Between 8 and 18) Then 1 Else 0 End);
 
 
 
DECLARE @tbSP_who2Result TABLE
(
SPID INT, 
Status VARCHAR(1000) NULL, 
Login SYSNAME NULL, 
HostName SYSNAME NULL,  
BlkBy INT NULL, 
DBName SYSNAME NULL, 
Command VARCHAR(1000) NULL,
CPUTime_ms BIGINT NULL,
waittime_ms BIGINT,
Open_Tran BIGINT,
waitresource NVARCHAR(1000),
lastwaittype NVARCHAR(1000),
memusage_pages BIGINT,
DiskIO_RW INT NULL, 
LastBatch DATETIME,--VARCHAR(1000) NULL, 
ProgramName VARCHAR(1000) NULL, 
ModifiedDateOn SMALLDATETIME DEFAULT GETDATE(),
CreatedDateOn  SMALLDATETIME DEFAULT GETDATE(),
DataHash binary,
LockDateId int,
IsOperational int
)
 
Declare @tranResult Table (Event varchar(50), Parameters varchar(50),  EventInfo varchar(max))
 
 
--// Converted to millisecond
set @IntervalTime = ((@IntervalTime * 60) ) 
 
if (select count(1) from sysprocesses where blocked > 0)>0 and (select max(waittime) from sysprocesses where blocked > 0) > @IntervalTime  begin
 
 
 
 
INSERT INTO @tbSP_who2Result
( SPID ,
status,
Login,
HostName,
BlkBy,
DBName,
Command,
CPUTime_ms, 
waittime_ms, 
Open_Tran,
waitresource, 
lastwaittype,
memusage_pages,
DiskIO_RW,
LastBatch,
ProgramName,
DataHash,
LockDateId
)
  SELECT 
spid 
,p.status 
,convert(sysname, rtrim(p.loginame)) as loginname
,p.hostname 
,blocked 
,DB_NAME(p.dbid)
,p.cmd 
,p.cpu 
,p.waittime
,p.open_Tran
,waitresource
,lastwaittype
,memusage
,physical_io
,p.last_batch
,p.program_name
,@DataHash
,dbo.fnDateKey(getdate())
from master.dbo.sysprocesses  p (nolock)
where 
(
blocked <> 0
or 
spid in (Select blocked from sysprocesses)
)
 
If @SendEmail = 'N' Begin
 
Select '--// -- Locked Transactions'
 
SELECT 
SPID,
BlkBy,
Login,
ProgramName,
DBName,
Command,
HostName,
LastBatch,
CPUTime_ms,
Open_Tran = Case When open_Tran = 0 then 'No' Else 'Yes' End,
DiskIO_RW,
status,
DataHash
,dbo.fnDateKey(getdate())
FROM @tbSP_who2Result a
where BlkBy <> 0
order by 2
 
 
 
  Select '--// -- Caused the lock by the transaction'
 
SELECT 
SPID,
Login,
ProgramName,
DBName,
Command,
HostName,
LastBatch,
CPUTime_ms,
Open_Tran = Case When open_Tran = 0 then 'No' Else 'Yes' End,
DiskIO_RW,
status,
DataHash
,dbo.fnDateKey(getdate())
FROM @tbSP_who2Result a
where SPID in (Select BlkBy from @tbSP_who2Result)
order by 2
End
 
 
--// Details about which  transaction is causing the lock'
 
Declare CUR CURSOR FOR
SELECT distinct blkby FROM @tbSP_who2Result where  blkby <> 0
 
 
Open CUR
 
Fetch Next From CUR
Into @id
 
While @@FETCH_STATUS = 0
Begin
 
 
set @strSQL = 'dbcc inputbuffer(' + @id +')'
 
insert @tranResult
exec sp_executeSQL @strSQL
 
set @strSQLTranCause = (select EventInfo from @tranResult)
 
 
Delete @tranResult
 
Update @tbSP_who2Result Set Command = @strSQLTranCause where SPID = @id
 
Fetch Next From CUR
Into @id
End
 
Close CUR
 
Deallocate CUR
 
 
 
--// Save for historical analysis
Insert [dbo].[ServerLock] ([ServerName],[PerformanceIndicatorCategoryName],[PerformanceIndicatorName],[PerformanceAVGIndicatorValue],[PerformanceIndicatorValue],SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime_ms,waittime_ms,                                       Open_Tran,waitresource,lastwaittype,memusage_pages,DiskIO_RW,LastBatch,ProgramName,ModifiedDateOn,DataHash,[CreatedDateOn], IsOperational,LockDateID)
Select                      @ServerName, @PerformanceIndicatorCategoryName, @PerformanceIndicatorName, @PerformanceAVGIndicatorValue, @PerformanceIndicatorValue,SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime_ms,waittime_ms,Case When open_Tran = 0 then 'No' Else 'Yes' End,waitresource,lastwaittype,memusage_pages,DiskIO_RW,LastBatch,ProgramName,ModifiedDateOn,DataHash,[CreatedDateOn],@IsOperational,LockDateID from @tbSP_who2Result 
 
 
--// Gather the operator email
Set @EmailOperator = (select email_address from msdb.dbo.sysoperators where name = 'DBA')
 
 
If @SendEmail = 'Y' and @EmailOperator is not null Begin
 
 
set @AlertComments = (select notification_message from msdb.dbo.sysalerts where name = @AlertName)
set @nOccurence = (select occurrence_count from msdb.dbo.sysalerts where name = @AlertName)
 
--// Reset the count - Lock Wait Time
update  msdb.dbo.sysalerts set occurrence_count = 0 where name = @AlertName
 
set @strEmailBody = 'DATE/TIME: ' + convert(varchar(100), getdate()) + '

'

set @strEmailBody = @strEmailBody + 'DESCRIPTION: The SQL Server performance counter Lock waits (instance Waits in progress) of object Wait Statistics is now above the threshold of 0.00 (the current value is ' + @nOccurence + ').

'

set @strEmailBody = @strEmailBody + '

Impacted Processes:

'
 
set @strEmailBody = @strEmailBody + '
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
'+
CAST ( (
SELECT td = CAST(SPID AS VARCHAR(100)),'',
td = CAST(BlkBy AS VARCHAR(100)),'',
td = Login,'',
td = ProgramName ,'',
td = DBName ,'',
td = Command,'',
td = HostName,'',
td = status,''
FROM @tbSP_who2Result a
WHERE blkby <> 0
order by 2
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
SPID BlkBy Login ProgramName DBName Command HostName status

'

 
set @strEmailBody = @strEmailBody + '

Caused by Process:

'
set @strEmailBody = @strEmailBody + '
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
'+
CAST ( (
SELECT td = CAST(SPID AS VARCHAR(100)),'',
td = CAST(BlkBy AS VARCHAR(100)),'',
td = Login,'',
td = ProgramName ,'',
td = DBName ,'',
td = Command,'',
td = HostName,'',
td = status,''
FROM @tbSP_who2Result a
WHERE exists
(select 1 from @tbSP_who2Result b
where blkby <> 0
and a.spid=b.blkby
)
order by 2
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
SPID BlkBy Login ProgramName DBName Command HostName status

 

'

set @strEmailBody = @strEmailBody + '

Caused by Transaction:

'
set @strEmailBody = @strEmailBody + @strSQLTranCause + '

'

 
set @strEmailSubject = Concat(@@SERVERNAME,' - SQL Server Alert System: Lock Wait Time')
 
EXEC msdb..sp_send_dbmail 
   @recipients = @EmailOperator
  ,@subject = @strEmailSubject 
  ,@body_format = 'HTML'
  ,@body = @strEmailBody
 
 
End
 
 
End
 
End 
 

--===============================================================

--// Created a SQL Job to call the SP (don't create any schedule)

--===============================================================

-- I called the SQL Job as: DBAWEB - Lock Wait Time - Analysis

 

--===============================================================

--// Created an Operator Email called DBA

--===============================================================

 
USE [msdb]
GO
 
/****** Object:  Operator [DBA]    Script Date: 15/10/2018 4:55:28 PM ******/
EXEC msdb.dbo.sp_add_operator @name=N'DBA', 
@enabled=1, 
@weekday_pager_start_time=90000, 
@weekday_pager_end_time=180000, 
@saturday_pager_start_time=90000, 
@saturday_pager_end_time=180000, 
@sunday_pager_start_time=90000, 
@sunday_pager_end_time=180000, 
@pager_days=0, 
@email_address=N'myemail@onedbsolution.com', 
@category_name=N'[Uncategorized]'
GO
 

--===============================================================

--// Created an Alert called Lock Wait Time

--===============================================================

 
USE [msdb]
GO
 
EXEC msdb.dbo.sp_add_alert @name=N'Lock Wait Time', 
@message_id=0, 
@severity=0, 
@enabled=1, 
@delay_between_responses=300, 
@include_event_description_in=1, 
@category_name=N'[Uncategorized]', 
@performance_condition=N'Wait Statistics|Lock waits|Waits in progress|>|0', 
@job_id=N'0ea99502-abac-4f62-9f8e-000112c756f4'
GO
 
 

--===============================================================

--// Created a View used as my Power By source

--===============================================================

use DBA
go
create or alter view vwSQLLock
 
as
 
SELECT [ServerLockID]
  ,[ServerName]
  ,[PerformanceIndicatorCategoryName]
  ,[PerformanceIndicatorName]
  ,[PerformanceAVGIndicatorValue]
  ,[PerformanceIndicatorValue]
  ,[SPID]
  ,[Status]
  ,[Login]
  ,[HostName]
  ,[BlkBy]
  ,[DBName]
  ,[Command]
  ,[CPUTime_ms]
  ,[waittime_ms]
  ,[Open_Tran]
  ,[waitresource]
  ,[lastwaittype]
  ,[memusage_pages]
  ,[DiskIO_RW]
  ,[LastBatch]
  ,[ProgramName]
  ,[ModifiedDateOn]
  ,[DataHash]
  ,[CreatedDateOn]
  ,[IsOperational]
  ,[LockDateID]
  ,LockTotal = Case When (Lag(DataHash,1,0) over (order by createdDateOn ) <> DataHash) then 1 else 0 End
  FROM [DBA].[dbo].[ServerLock]
 

 

Etiquetas: