Here is the Script to generate a report of all Outdated Stats in a server and send the report Via mail to your inbox.
You can automate this in SQL job just by adding Reciepients and DB Mail profiles as parameter
SP will send details Report of Outdates Stats to your Mail, You can Schdeule this job or run on demand to fetch the data.
Based on the report you can analyze the data on weekly basis and set weekly Reindex and Update Stats plans(Column Stats and Index Stats)
__________________________________________________________________________
You can automate this in SQL job just by adding Reciepients and DB Mail profiles as parameter
SP will send details Report of Outdates Stats to your Mail, You can Schdeule this job or run on demand to fetch the data.
Based on the report you can analyze the data on weekly basis and set weekly Reindex and Update Stats plans(Column Stats and Index Stats)
__________________________________________________________________________
Step1:
Create an SP using Script 1.
Description: SP will fetch the data (Outdates Stats info)from all the databases and send mail to reciepients
Step2:
Sample Query to get Outdatad Stats report
exec [USP_OutdatedStat_Report] 'hanuman.devineni@gmail.com','DBMailProfile'
--Parameter 1 : Recipient to which you want to send mail
--Parameter 2 : DB Mail Profile Name
Script 1 to create the Outdated Stats Report.
/****** Object: StoredProcedure [dbo].[USP_OutdatedStat_Report] Script Date: 03/18/2014 08:33:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_OutdatedStat_Report_V1]
@To varchar(100),
@profile varchar(50)
AS
/***************************************************************************
-- PROGRAM: OutdatedStatistics Index report
-- CREATED: 02/04/2013 AUTHOR: Hanuman D
-- EXAMPLE: EXEC the script to get the Outdated Stats{object name}
-- DESC : Script will get the Outdated Stats report mail to Reciepients based on requirement
--
***************************************************************************************************/
Set Nocount On
/*-- Query to Check for the temporary table if exists drops and recreates */
If exists (select * from tempdb.sys.all_objects where name like '#Temp%' )
drop table #Temp
create table #Temp
(DatabaseName varchar(200),ObjectName varchar(200),indexName varchar(200),StatsUpdated Datetime,Daysold int)
/*-- Insert records of Outdated Stats indexes from each user database to temperorary table */
insert into #Temp (DatabaseName,ObjectName,indexName,StatsUpdated,Daysold)
exec master.sys.sp_MSforeachdb ' USE [?]
IF DB_ID(''?'') > 4
SELECT db_name() as DatabaseName,OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE
(A.OBJECT_ID, index_id)AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
and b.NAME not like ''x%''
and b.NAME not like ''at%''
and b.NAME not like ''bj%''
and b.NAME not like ''ej%''
and b.NAME not like ''jd%''
and b.NAME not like ''%test%''
and b.NAME not like ''%bak%''
and b.NAME not like ''%_201%''
and (DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate())>3)
ORDER BY StatsUpdated DESC
'
/*Query to delete the data for readonly databases*/
delete from #Temp where DatabaseName in (select name from sys.databases where is_read_only <> 0)
/*-- Query to Capture information and send mail in HTML format*/
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @Subject NVARCHAR(300)
DECLARE @rowcount int
SET @Subject='Outdated Stats Report for Server '+(select @@servername)+''
SELECT @rowcount = COUNT(*) FROM #Temp
IF (@rowcount > 0)
BEGIN
SET @xml = CAST(( SELECT [DatabaseName] AS 'td','',[ObjectName] AS 'td','',
[indexName] AS 'td','',[StatsUpdated] AS 'td','',[Daysold] AS 'td'
FROM #Temp Where databasename not like '%FP%' and databasename not like '%reportserver%' and databasename not like
'%_test%' and databasename <>'_Testdata' and databasename <>'profiler' and databasename not like '%FYE%' ORDER BY
DatabaseName
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body =
N'<H3>Oudated Stats on MMI server '+ (select td = (select @@servername))+'</H3>'+
N'<html><body><table border = 1>
<tr>
<th> DatabaseName </th> <th> ObjectName </th> <th> indexName</th><th> StatsUpdated</th><th>Daysold</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = @To, -- replace with your email address
@subject = @Subject ;
END
ELSE
BEGIN
Set @body='NO Fragmented indexes on '+(select @@servername)+' server'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = @To, -- replace with your email address
@subject = @Subject ;
END
GO
Script 1 to create the Outdated Stats Report.
/****** Object: StoredProcedure [dbo].[USP_OutdatedStat_Report] Script Date: 03/18/2014 08:33:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_OutdatedStat_Report_V1]
@To varchar(100),
@profile varchar(50)
AS
/***************************************************************************
-- PROGRAM: OutdatedStatistics Index report
-- CREATED: 02/04/2013 AUTHOR: Hanuman D
-- EXAMPLE: EXEC the script to get the Outdated Stats{object name}
-- DESC : Script will get the Outdated Stats report mail to Reciepients based on requirement
--
***************************************************************************************************/
Set Nocount On
/*-- Query to Check for the temporary table if exists drops and recreates */
If exists (select * from tempdb.sys.all_objects where name like '#Temp%' )
drop table #Temp
create table #Temp
(DatabaseName varchar(200),ObjectName varchar(200),indexName varchar(200),StatsUpdated Datetime,Daysold int)
/*-- Insert records of Outdated Stats indexes from each user database to temperorary table */
insert into #Temp (DatabaseName,ObjectName,indexName,StatsUpdated,Daysold)
exec master.sys.sp_MSforeachdb ' USE [?]
IF DB_ID(''?'') > 4
SELECT db_name() as DatabaseName,OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE
(A.OBJECT_ID, index_id)AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
and b.NAME not like ''x%''
and b.NAME not like ''at%''
and b.NAME not like ''bj%''
and b.NAME not like ''ej%''
and b.NAME not like ''jd%''
and b.NAME not like ''%test%''
and b.NAME not like ''%bak%''
and b.NAME not like ''%_201%''
and (DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate())>3)
ORDER BY StatsUpdated DESC
'
/*Query to delete the data for readonly databases*/
delete from #Temp where DatabaseName in (select name from sys.databases where is_read_only <> 0)
/*-- Query to Capture information and send mail in HTML format*/
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @Subject NVARCHAR(300)
DECLARE @rowcount int
SET @Subject='Outdated Stats Report for Server '+(select @@servername)+''
SELECT @rowcount = COUNT(*) FROM #Temp
IF (@rowcount > 0)
BEGIN
SET @xml = CAST(( SELECT [DatabaseName] AS 'td','',[ObjectName] AS 'td','',
[indexName] AS 'td','',[StatsUpdated] AS 'td','',[Daysold] AS 'td'
FROM #Temp Where databasename not like '%FP%' and databasename not like '%reportserver%' and databasename not like
'%_test%' and databasename <>'_Testdata' and databasename <>'profiler' and databasename not like '%FYE%' ORDER BY
DatabaseName
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body =
N'<H3>Oudated Stats on MMI server '+ (select td = (select @@servername))+'</H3>'+
N'<html><body><table border = 1>
<tr>
<th> DatabaseName </th> <th> ObjectName </th> <th> indexName</th><th> StatsUpdated</th><th>Daysold</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = @To, -- replace with your email address
@subject = @Subject ;
END
ELSE
BEGIN
Set @body='NO Fragmented indexes on '+(select @@servername)+' server'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profile, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = @To, -- replace with your email address
@subject = @Subject ;
END
GO
No comments:
Post a Comment