To make a long story short we (my ace team at Mercy) recently discovered our statistic maintenance job was not up to snuff and was actually ignoring any non-clustered and heap indexes. This was not a good thing. As we were finding the root cause some team members created this procedure. The output is the actual and sampled rows and last updated value for statistics in each database where the table row count exceeds a million rows. Of course you can configure the row count however you wish. For extra credit you can create an SSIS job which will execute this on every server in your organization and send you an email. Here's the link on how to do it.
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61621/
Here here's the procedure. I give you the code "as is". Pardon the inline comments.....
______________________________________________________________________
USE [master]
CREATE PROCEDURE [dbo].[sp_stats_alerts]
as
--gets stats info from sql 2005 and above
Set nocount on
SET QUOTED_IDENTIFIER OFF
SET FMTONLY OFF
Declare @dbname varchar(100);
Declare @sql nvarchar(4000);
Declare @rowcount bigint;
Create table #tblDbs (
rowNo int
,dbname varchar(100)
)
--#stats tables holds results from DBCC SHOW_STATISTICS() WITH STAT_HEADER
--creating table for sql2005 then below checking for current version of the server then if it is > sql2005
--then adding 2 extra columns for sql 2008 and above
create table #stats (
[Servername] varchar(25) default @@servername
,[dbname] varchar(100) DEFAULT db_name()
, [Name] VARCHAR(500)
, [Updated] VARCHAR(500)
, [Rows] VARCHAR(500)
, [Rows Sampled] VARCHAR(500)
, [Steps] VARCHAR(500)
, [Density] VARCHAR(500)
, [Average key length] VARCHAR(500)
, [String index] VARCHAR(500)
)
--select * from #stats
if ((select left(cast(SERVERPROPERTY('productversion') as varchar(20)),1) )<>9)
alter table #stats add [Filter Expression] VARCHAR(500), [Unfiltered Rows] VARCHAR(500);
--#dbccQueries Table holds dbcc queries which are later executed with exec() satatement
Create Table #dbccQueries
(
RowNo bigint
,dbname varchar(100)
,querytext varchar(1000)
)
insert into #tblDbs select ROW_NUMBER() over (order by name) rowno, name from sys.databases where name not in ('master','tempdb','model','msdb')
--for single database stats uncomment below and comment above statement
--insert into #tblDbs select ROW_NUMBER() over (order by name) rowno, name from sys.databases where name in ('dba')
set @rowcount =@@ROWCOUNT
while (@rowcount >0) --1
BEGIN --while1
select @dbname = dbname from #tblDbs where rowNo=@rowcount
--select @dbname
set @sql=
"USE " + @dbname + "; " +
"select row_number() over (order by a.name) row_num,db_name() dbname,'DBCC SHOW_STATISTICS ( ''' + c.name + '.' + a.name + ''',''' + b.name + ''' ) with NO_INFOMSGS, stat_header '
from sys.tables a
inner join sys.indexes b on a.object_id = b.object_id
inner join sys.schemas c on a.schema_id = c.schema_id
where b.name is not null
--where a.name = @lname
"
--select @sql
insert into #dbccqueries exec(@sql)
set @sql=
"Declare @sqlsub varchar(2000);" +
"Declare @rowcountsub bigint;" +
"USE " + @dbname + "; " +
"select @rowcountsub = count(*) from #dbccqueries
while (@rowcountsub>0) --2
Begin --while 2
select @sqlsub= querytext from #dbccQueries where RowNo=@rowcountsub
--select @sqlsub
"
if ((select left(cast(SERVERPROPERTY('productversion') as varchar(20)),1) )=9)
set @sql=@sql + "insert into #stats([Name],[Updated],[Rows],[Rows Sampled] ,[Steps],[Density],[Average key length],[String index]) exec (@sqlsub)"
else
set @sql= @sql +"insert into #stats([Name],[Updated],[Rows],[Rows Sampled] ,[Steps],[Density],[Average key length],[String index],[Filter Expression],[Unfiltered Rows]) exec (@sqlsub)"
set @sql=@sql + "set @rowcountsub = @rowcountsub - 1
End --while 2
"
--select @sql
exec (@sql)
--select * from #dbccqueries
truncate table #dbccqueries
set @rowcount = @rowcount -1
End --while 1
--select * from #dbccQueries order by 2,1
--select * from #tblDbs
PRINT @@servername
PRINT ''
select [Servername] Instance,
dbname as [Database],
Name as [Stat Name],
[rows] as [Actual Rows],
[Rows Sampled] as [Sampled Rows],
Updated as [Last Updated]
from #stats
where [Rows] is not null
and
[Rows Sampled] < [Rows]
and [Rows] > 100000
drop table #tblDbs
drop table #dbccqueries
drop Table #stats
______________________________________________________________________
Kudos to Travis Whitley and Ravi Rangineni for pulling this together on short notice.
