Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Statistics script Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 9:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 28, 2014 8:02 AM
Points: 197, Visits: 698
I am looking for a script where I can update the statistics of the objects in a database if only the last stat updates date was before any specific date. Any help would be greatly appreciated.

Thank you.
Post #1410658
Posted Wednesday, January 23, 2013 9:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:15 AM
Points: 318, Visits: 1,067
SET NOCOUNT ON
DECLARE @StatsToUpdate TABLE ( StatName NVARCHAR(MAX) )
DECLARE @StatUpdating NVARCHAR(MAX)

INSERT INTO @StatsToUpdate
( StatName
)
SELECT 'UPDATE STATISTICS dbo.' + OBJECT_NAME + ' ' + index_name
FROM ( SELECT TOP 1
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 DATEDIFF(d, STATS_DATE(A.OBJECT_ID, index_id),
GETDATE()) >= 0
) StatsThatAreTooOld

WHILE ( SELECT COUNT(*)
FROM @StatsToUpdate
) > 0
BEGIN
SET @StatUpdating = ( SELECT TOP 1
statname
FROM @StatsToUpdate
)
EXECUTE sp_executesql @StatUpdating
PRINT 'Done ' + @StatUpdating
DELETE FROM @StatsToUpdate
WHERE StatName = @StatUpdating
END

Not the most elegant. Simply looks at the stats and in this case returns a list of stats more than 0 days old and then carrys out an update stats statement

Would need a little tweaking to fully meet your needs probably and there are probably better ways to do this.
Post #1410679
Posted Friday, January 25, 2013 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:04 AM
Points: 30, Visits: 280
If you're running SQL Server 2008R2 SP2, or SQL Server 2012 SP1, you can use the new statistics DMF sys.dm_db_stats_properties. I have a query for it on my blog: http://www.sqlskills.com/blogs/erin/new-statistics-dmf-in-sql-server-2008r2-sp2/

Otherwise, you'll have to roll through DBCC SHOW_STATISTICS or use STATS_DATE as the previous poster mentioned.

Hope that helps!

Erin
Post #1411743
Posted Friday, January 25, 2013 8:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 565, Visits: 65,772
This is what I use to do that same thing. It is used in a SSIS package and the results are held in an object variable then I loop through that variable executing an update stats command.

select o.object_id,o.name,s.stats_id,s.name
from sys.objects o
inner join sys.stats s on o.object_id = s.object_id
where o.type = 'U'
and stats_date(o.object_id,s.stats_id) <= getdate()-20
order by o.object_id,s.stats_id

Post #1411749
Posted Friday, January 25, 2013 11:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:41 PM
Points: 41, Visits: 549
Execute following ---

use dbname
go
exec sp_updatestats

(this sp will detect and automatically update only require)
Post #1411859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse