January 7, 2016 at 12:38 pm
Hi ,
i need the script for my database.
which needs to give results of stats are not update from last one month with more than 20% rows samples.
Can you please provide any good script for this.
January 7, 2016 at 2:54 pm
Something like this should get you close if you're actually on 2008 R2 or later:
SELECT table_name=object_name(s.object_id),
stats_name=name,
last_updated,
rows,
rows_sampled,
last_sample_rate=(rows_sampled*1.0)/rows,
days_since_update=DATEDIFF(dd,last_updated,GETDATE())
FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties (object_id, stats_id) sp
WHERE s.object_id>100 AND (last_updated<DATEADD(mm,-1,GETDATE())
OR (rows_sampled*1.0)/rows<.20
)
ORDER BY rows DESC
For 2008 something like this should work:
CREATE TABLE #stats_info (Name nvarchar(128),
Updated datetime,
Rows int,
[Rows Sampled] int,
Steps int,
Density decimal(5,4),
[Average key length] int,
[String Index] char(3),
[Filter Expression] nvarchar(300),
[Unfiltered Rows] int);
DECLARE @sql nvarchar(max)='';
SELECT @sql=@sql+'INSERT INTO #stats_info EXECUTE(''DBCC SHOW_STATISTICS (['+sch.name+'.'+o.name+'],['+st.name+']) WITH STAT_HEADER'');'
FROM sys.stats st INNER JOIN sys.objects o ON st.object_id=o.object_id
INNER JOIN sys.schemas sch ON sch.schema_id=o.schema_id
WHERE o.object_id>100;
EXECUTE (@sql);
SELECT table_name=object_name(s.object_id),
stats_name=s.name,
last_updated=Updated,
rows,
[rows sampled],
last_sample_rate=([rows sampled]*1.0)/rows,
days_since_update=DATEDIFF(dd,Updated,GETDATE())
FROM sys.stats s INNER JOIN #stats_info si ON s.name=si.name
WHERE Updated IS NULL
OR Updated<DATEADD(mm,-1,GETDATE())
OR ([rows sampled]*1.0)/rows<.20
ORDER BY rows DESC;
DROP TABLE #stats_info;
Cheers!
EDIT: Realized after I posted this was in a 2008 forum. I'll update with a 2008-compatible example in a bit.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply