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
|
| | | |
|
SQL Server Execution Plan Handle Changes - Monitoring, Logging & Alerting
by BF (Principal Consultant; Architecture; Engineering)
2016-05-06
Solution:
1. Create Stored Procedure to monitor, log & alert on any Plan_Handle changes:
Create Procedure [dbo].[LogProcedureCache]
as
Set NoCount On
Declare @LastInsertedDate datetime
Set @LastInsertedDate = (Select max(inserteddate) from [OPS].[dbo].[LOG_PROC_CACHE] with (nolock))
Declare @Alert bit
Set @Alert = 0
Create Table #TMP (DB varchar(50), ObjectID int, ObjectName varchar(150), ObjType varchar(10), CacheObjectType varchar(50),
UseCounts int, RefCounts int, SizeInBytes int, Plan_Handle varbinary(max), Inserteddate datetime)
Insert into #TMP
select
DB_NAME(dbid), objectid, so.name, objtype, cacheobjtype, usecounts, refcounts, p.size_in_bytes,
plan_handle, --LEFT([sql].[text], 150) as [text],
getdate()
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
inner join sysobjects so on so.id = sql.objectid
ORDER BY name asc
--If a new Plan Handle Exists, Alert Once, Set Flag for Alerted
IF Exists (Select 1 from #TMP where Plan_Handle not in (Select Plan_Handle from [OPS].[dbo].[LOG_PROC_CACHE] where Inserteddate =
(Select max(inserteddate) from [OPS].[dbo].[LOG_PROC_CACHE] with (nolock))))
Begin
Set @Alert = 1
--Log Entries from Procedure Cache
Insert into [OPS].[dbo].[LOG_PROC_CACHE] ([DB],[ObjectID],[ObjectName],[ObjType],[CacheObjectType],[UseCounts],
[RefCounts],[SizeInBytes],[Plan_Handle],[AlertSent],[Inserteddate])
Select DB,ObjectID,ObjectName,ObjType,CacheObjectType,UseCounts,RefCounts,SizeInBytes,Plan_Handle, 0, GetDate() from #TMP
--Remove any enrties that already exist in permanent log table
Delete from #TMP where Plan_Handle in (Select Plan_Handle from [OPS].[dbo].[LOG_PROC_CACHE] where Inserteddate = @LastInsertedDate)
--Update any alertable new record(s)
Update t1 set t1.alertsent = 1
from [OPS].[dbo].[LOG_PROC_CACHE] t1 inner join #TMP t2 on t1.Plan_Handle = t2.Plan_Handle
where t1.Inserteddate = (Select max(inserteddate) from [OPS].[dbo].[LOG_PROC_CACHE] with (nolock))
--Create & Send alert using Database Mail
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H4><Font Color=black><font face="arial">Delivered by SQLMail on SQLVM1:</H4>' +
N'<H5><Font Color=black><font face="arial">Report Title: Procedure Cache - New Plan_Handle created</H5>' +
N'<table border="1" cellpadding=5 style=”font-family:Calibri;color:black;font-size:10pt;”> ' +
N'<tr align="left"><th align="left">DB</th><th align="left">ObjectName</th><th align="left">UseCounts</th><th align="left">InsertedDate</th></tr>' +
CAST ((
select td =t1.DB, '', td =t1.ObjectName, '', td =t1.UseCounts, '', td =t1.InsertedDate, ''--,td =t1.Plan_Handle, ''
from #TMP t1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
Set @tableHTML = @tableHTML + '</br></br>' + 'Alert Source: SQL Job: Log Procedure Cache'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProSQLMailXYZ',
@recipients='xyz@xyz.ca',
@subject = 'Alert PRD - Procedure Cache - New Plan_Handle created',
@body = @tableHTML,
@body_format = 'HTML' ;
Set @Alert = 1
End
--Log Entries from Procedure Cache (no Alert)
If @Alert = 0
Begin
Insert into [OPS].[dbo].[LOG_PROC_CACHE] ([DB],[ObjectID],[ObjectName],[ObjType],[CacheObjectType],
[UseCounts],[RefCounts],[SizeInBytes],[Plan_Handle],[AlertSent],[Inserteddate])
Select DB,ObjectID,ObjectName,ObjType,CacheObjectType,UseCounts,RefCounts,SizeInBytes,Plan_Handle,
@Alert, GetDate() from #TMP
End
Drop Table #TMP
GO
2. Create Table for Logs
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOG_PROC_CACHE](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[DB] [varchar](50) NULL,
[ObjectID] [int] NULL,
[ObjectName] [varchar](150) NULL,
[ObjType] [varchar](10) NULL,
[CacheObjectType] [varchar](50) NULL,
[UseCounts] [int] NULL,
[RefCounts] [int] NULL,
[SizeInBytes] [int] NULL,
[Plan_Handle] [varbinary](max) NULL,
[AlertSent] [bit] NULL,
[Inserteddate] [datetime] NULL,
CONSTRAINT [PK_LOG_PROC_CACHE] 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] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
3. Create a SQL Job that calls the stored procedure every x mins.
Image #1:
|
|
|
|
|
|
|
|
|
|