Optionally recompile statement

USE [SysAdmin]
GO

ALTER TABLE [dbo].[DBA_QueryPlanHistory] DROP CONSTRAINT [DF_LOGDATE]
GO

/****** Object:  Table [dbo].[DBA_QueryPlanHistory]    Script Date: 5/8/2015 10:02:28 AM ******/
DROP TABLE [dbo].[DBA_QueryPlanHistory]
GO

/****** Object:  Table [dbo].[DBA_QueryPlanHistory]    Script Date: 5/8/2015 10:02:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO


CREATE TABLE [dbo].[DBA_QueryPlanHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[query_hash] [binary](8) NULL,
[plan_handle] [varbinary](64) NULL,
[query_plan_hash] [binary](8) NULL,
[avg_cpu_time_ms] int null,
[avg_execution_time_ms] int null,
[last_execution_time_ms] int null,
[LogDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[DBA_QueryPlanHistory] ADD CONSTRAINT DF_LOGDATE  DEFAULT (getdate()) FOR [LogDate]
GO
+++++++++++++++++++++++
++++++++++++++++++++++




use master
set nocount on
--- Recompile statement in cp_UpdateEventConflicts
--

DECLARE @query_hash Binary(8) = 0xD635959FFEC552CC  --- this is the sql we are concerned about
DECLARE @Bad_query_plan_hash Binary(8) = 0xEBF4F7D683B4BBDD --- this is a know bad plan if the statement has this plan we will always recompile


DECLARE @current_query_plan_hash Binary(8)
DECLARE @current_plan_handle varbinary(64)


-- Perf Metrics
--
Declare @avg_cpu_time_ms int
Declare @avg_execution_time_ms int
Declare @last_execution_time_ms int


-- -Email


DECLARE @EmailRecipient VARCHAR(1000), @SubjectText VARCHAR(1000), @ProfileName VARCHAR(1000), @tableHTML VARCHAR(MAX)
--SELECT @EmailRecipient = 'DBA_DL@XXXXXXX.com'
SELECT @EmailRecipient = 'joe.obrien@joeobrien.me'


SELECT TOP 1 @Current_plan_handle = plan_handle,
@Current_query_plan_hash = query_plan_hash ,
@avg_cpu_time_ms= ((s.total_worker_time * 1.00)/ s.execution_count)/ 1000.00 ,
@avg_execution_time_ms =  ((s.total_elapsed_time)/ s.execution_count)/ 1000.00 ,
@last_execution_time_ms = (s.last_elapsed_time)/ 1000.00
FROM sys.dm_exec_query_stats s
WHERE query_hash = @query_hash
ORDER BY total_worker_time DESC




SELECT TOP 1 @ProfileName = name FROM msdb.dbo.sysmail_profile

SET @SubjectText = 'Query plan has been recompiled on ' + @@servername + ' '

SET @tableHTML = N'Query_Hash: ' + CONVERT(nvarchar(4000),@query_hash,1)
SET @tableHTML += N' Query_Plan_Hash: ' + CONVERT(nVARCHAR(4000),@Current_query_plan_hash,1)
SET @tableHTML += N' Plan_Handle: ' + CONVERT(nVARCHAR(4000),@Current_plan_handle,1)


--if the last execution_time  or the average_execution  or it is a know bad plan - recompile
If (@last_execution_time_ms >= 5000) or (@avg_execution_time_ms >= 3000) or (@Current_query_plan_hash = @Bad_query_plan_hash)
Begin


dbcc freeproccache( @Current_plan_handle)



INSERT INTO [Sysadmin].[dbo].[DBA_QueryPlanHistory]
           ([query_hash]
           ,[plan_handle]
           ,[query_plan_hash]
           ,[avg_cpu_time_ms]
           ,[avg_execution_time_ms]
           ,[last_execution_time_ms]
           )
     VALUES
           (@query_hash,
  @Current_plan_handle,
  @Current_query_plan_hash,
  @avg_cpu_time_ms  ,
  @avg_execution_time_ms   ,
  @last_execution_time_ms )

 


EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = @EmailRecipient,
@body = @tableHTML,
@body_format = 'HTML',
@subject = @SubjectText



end