Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



SQL Server Execution Plan Handle Changes - Monitoring, Logging & Alerting
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
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: