/****************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.

************************************************************************************************************************/