/****************The Lytic Group, copyright, 2021*****************/
/****************Published January, 2021 *****************/
/*********from http://www.lyticgroup.com by Edward Heraux*****************/
/****** Object: StoredProcedure [dbo].[uspUpdateOldStatistics2012] Script Date: 1/15/2021 11:59:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--*****************************************************************************************************************
--If part of your database administration routine is to manually update statistics for tables that need it,
--run this script on a daily schedule instead.
--This stored procedure will minimize guesswork and research time, as you can set an ideal number of days
--after which you'd like any tables' statistics updated,
--and only update those tables.
This version of the script will work with SQL Server version 2008 RS _WITH SP2_ and up.
--*****************************************************************************************************************
/****************IMPORTANT WARNING!!!!!*****************************************************
DO NOT run this script in the middle of your production day.
You risk locking up entire tables of your data at horribly inopportune times.
Remember also that updating statistics also causes stored procedures to recompile,
which will likely slow their performance the next time they are run.
*************************************************************************************************/
/****************WE’D LOVE YOUR FEEDBACK****************************************************************************
Email: ed@lyticgroup.com
and connect with Ed Heraux (the script author) on LinkedIn, http://www.linkedin.com/pub/edward-heraux-pmp/1/826/730/
for announcements of all new scripts.
************************************************************************************************************************/
CREATE PROC [dbo].[uspUpdateOldStatistics2012] @TableName sysname=NULL --Leave this blank for the stored proc to *find* all the tables that need their statistics updated.
--Otherwise, indicate a table name
, @MinDaysOld int=0 -- Only update statistics whose last update was at least this many days ago
/*************Find or update all statistics for tables whose statistics haven't been up dated in a given set of days.***************/
AS
DECLARE @YoungestDateToUpdate date= DATEADD(DAY,(-1)* @MinDaysOld,CURRENT_TIMESTAMP)
DECLARE @SQLText varchar(MAX)
--If the TableName variable was skipped, retrieve statistics update data on all tables
DECLARE @StatsHistory TABLE(RowID int IDENTITY
,ObjectType varchar(20)
,ObjectID int
,StatsId int
,ObjectName sysname
,FilterDefinition varchar(100)
,LastStatisticsUpdate datetime
,NumRows int
,RowsSampled int
,AgeInDays int
)
--If a table was provided, check its statistics update history, and update if the last one was long enough ago
IF @TableName IS NOT NULL
BEGIN
--Make sure the passed table name is legitimate
IF NOT EXISTS (SELECT 0
FROM sys.sysobjects
WHERE Name=@TableName AND type IN ('U','S','V'))
BEGIN
RAISERROR('The table name passed in as a parameter is not a valid name of a table or view in this database.',16,1)
RETURN
END
--The sys.dm_db_stats_properties function is only available starting with SQL 2008 R2 **with SP2.** .
--This is the principle difference between this version of the stored proc and the <2008R2 version, also
INSERT INTO @StatsHistory(ObjectType,ObjectID, StatsID,ObjectName, FilterDefinition, LastStatisticsUpdate, NumRows,RowsSampled, AgeInDays)
SELECT O.type AS ObjectType,sp.object_id, sp.stats_id, O.name, filter_definition, last_updated, rows, rows_sampled
,DateDiff(DAY,STATS_DATE(Stat.object_id, Stat.stats_id),CAST (CURRENT_TIMESTAMP AS DATE)) AS AgeInDays
FROM sys.stats AS stat
INNER JOIN sys.sysobjects O
ON O.id=stat.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id(@TableName)
AND CAST(last_updated as DATE) <=@YoungestDateToUpdate
--Update the statistics on the table if it hasn't had them updated recently enough. Throw an error if it hasn't.
IF @@ROWCOUNT>0
BEGIN
SET @SQLText= 'UPDATE STATISTICS [' + @TableName + '] WITH RESAMPLE' --Change this last item to FULLSCAN or SAMPLE X PERCENT if you have a preference (see "UPDATE STATISTICS" in Books Online)
EXECUTE(@SQLText)
END
ELSE
RAISERROR ('There were no tables with statistics last updated long enough ago.',16,1)
RETURN
END
--Otherwise, grab statistics for all tables that qualify by age of their last stats update
ELSE
BEGIN
--Again, the sys.dm_db_stats_properties function is only available starting with SQL 2008 R2 **with SP2.**
INSERT INTO @StatsHistory(ObjectType,ObjectID, StatsID,ObjectName, FilterDefinition, LastStatisticsUpdate, NumRows,RowsSampled, AgeInDays)
SELECT O.type AS ObjectType,sp.object_id, sp.stats_id, O.name, filter_definition, last_updated, rows, rows_sampled
,DateDiff(DAY,STATS_DATE(Stat.object_id, Stat.stats_id),CAST (CURRENT_TIMESTAMP AS DATE)) AS AgeInDays
FROM sys.stats AS stat
INNER JOIN sys.sysobjects O
ON O.id=stat.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
--Exclude system tables, although this is enitrely optional.
WHERE O.type IN ('U','V')
AND CAST(last_updated as DATE) <=@YoungestDateToUpdate
--Check whether there are any tables to work on. If there aren't, exit with an error.
IF @@ROWCOUNT=0
BEGIN
RAISERROR ('There were no tables with their statistics last updated long enough ago.',16,1)
RETURN
END
END
--Size up and save the most recent statistics updates. This gives us a table of unique table names to work with.
SELECT ObjectID, ObjectType, ObjectName, MAX(CAST(LastStatisticsUpdate AS DATE)) AS LatestStatisticsUpdate, MAX(AgeInDays) AS AgeInDays
INTO #TargetObjects
FROM @StatsHIstory
GROUP BY ObjectID, ObjectType, ObjectName
--Cursor through all the tables in the table variable, updating the statistics in each
DECLARE @Counter as int=1, @NumTables int, @CurrentObjectID int
SELECT @NumTables=COUNT(*), @CurrentObjectID=MIN(ObjectID)
FROM #TargetObjects
WHILE @Counter<=@NumTables
BEGIN
--grab one table name to work with
SELECT @TableName=MIN(ObjectName)
FROM #TargetObjects
SET @SQLText= 'UPDATE STATISTICS [' + @TableName + '] WITH RESAMPLE' --Change this last item to FULLSCAN or SAMPLE X PERCENT if you have a preference (see "UPDATE STATISTICS" in Books Online)
EXECUTE(@SQLText)
SET @Counter=@Counter+1
DELETE #TargetObjects
WHERE ObjectName=@TableName
END
GO
/****************WE’D LOVE YOUR FEEDBACK****************************************************************************
Email info@lyticgroup.com
and connect with Ed Heraux (the script author) on LinkedIn, http://www.linkedin.com/pub/edward-heraux-pmp/1/826/730/
for announcements of all new scripts.
************************************************************************************************************************/