7 out of 10 DBAs Say Statistics are Not Accurate

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.

Red Gate's Exceptional DBA Award

Along with 4 other DBA's I received notice of my esteemed spot as a finalist for RedGate's Exceptional DBA Award.  It reminds of the time I watched the show on the Discovery Channel where they film the Navy Seals training. One of the best lines in the show is when the Instructors ask the candidates what second place is and they all reply "First Loser! Sir!".  Well, there are times when being second place, or third, or fourth, or even fifth isn't all that bad.

Don't get me wrong. I'd love nothing more than to win. Both for the honor and acknowledgement of years of hard work but also for the opportunity to go to PASS which I've never been able to attend. I met tons of great people at the SQLRally and it would be great to see them again and meet new friends.  I also spent an interesting time in my life in Seattle. I lived downtown, worked as a concrete finisher, and basically starved. But it was a great place and I'd love to see how it changed.

There's some fantastic competition which makes being a finalist all that more exciting. Colin Stasiuk is extremely active in the community and already has a wonderful following that is hard to compete against.  There is also Tom Hill who I actually had the pleasure of working with at Anheuser-Busch and he also worked with many of my friends at Monsanto and they all have good things to say about him. I wish all the candidates the best of luck.

As for me, please go to the RedGate website and take a look.  Hopefully you'll see a word or phrase or something familiar in my experiences which may help win your vote.  Oh, also, if you vote for me and I end up winning - talk to me and we'll have a beer at PASS. :)

VOTE FOR PEDRO (OR SCOTT)