Update Statistics and finding Statistics update date

To find how old in days are statistics for all the Tables and Indexes in AdventureWorks database use this query:

USE AdventureWorks
GO
SELECT OBJECT_NAME(I.object_id) AS Object_Name, I.name AS index_name, STATS_DATE(I.OBJECT_ID, index_id) AS Statistics_Updated_Date ,
DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) Days_since_update
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) DESC

To find how old in days are statistics for 'Person' Table and Indexes in the table in AdventureWorks database:

USE AdventureWorks
GO
SELECT OBJECT_NAME(I.object_id) AS Object_Name, I.name AS index_name, STATS_DATE(I.OBJECT_ID, index_id) AS Statistics_Updated_Date ,
DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) Days_since_update
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.name IS NOT NULL AND T.name=('Person')
ORDER BY DATEDIFF(d,STATS_DATE(I.OBJECT_ID, index_id),getdate()) DESC

To update statistics for 'Person.Person' Table with an additional option of FULLSCAN that means that the complete table is scanned to update the statistics:

USE AdventureWorks;
GO
UPDATE STATISTICS Person.Person
WITH FULLSCAN
GO

To update statistics for 'Person.Person' Table with an option of SAMPLE 50 PERCENT:

USE AdventureWorks;
GO
UPDATE STATISTICS Person.Person
WITH SAMPLE 50 PERCENT
GO

To update all statistics for the database:

USE AdventureWorks;
GO
EXEC sp_updatestats;

To update statistics for all tables in 'Person' schema in AdventureWorks database with an additional option of FULLSCAN:

USE AdventureWorks;
GO
SET NOCOUNT ON
DECLARE @SQLcommand NVARCHAR(512),
@Table SYSNAME
DECLARE Tables_Cursor CURSOR FOR
SELECT table_schema + '.' + table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' and table_schema = 'Person'
OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor
INTO @Table
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS FOR TABLE: ' + @Table
SET @SQLcommand = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'
EXEC sp_executesql @SQLcommand
FETCH NEXT FROM Tables_Cursor
INTO @Table
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor
SET NOCOUNT OFF
GO

To update statistics that require updating, using sp_updatestats, in all user databases:

Set Nocount on
Declare databases Cursor For
Select name from master.dbo.sysdatabases where name not in ('master','TempDB', 'msdb', 'model')
Declare @dbname varchar(100)
Declare @SQLcommand nvarchar(200)
Open databases
Fetch Next from databases into @dbname
While @@Fetch_status=0
begin
if @dbname is null
Begin
Print 'null Value'
end
else
Begin
PRINT 'Updating Statistics in ' + @dbname
SELECT @SQLcommand = 'USE ' + @dbname + ' EXEC sp_updatestats'
EXEC(@SQLcommand)
PRINT ''
End
Fetch Next from databases into @dbname
end
Close databases
Deallocate databases
GO

Updating database statistics is an I/O intensive operation. Plan to update statistics when database traffic is at its lightest.

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">