Script to Update Specific Stats
In my previous post I was stating that we need to be more intelligent with our stats updates rather than hitting a whole table with a sledgehammer and potentially suffering poor performance as a result.

However, wouldn’t it be great to be more proactive about the whole thing…

For example, if we know the tables we’re joining, on which columns, and the values we’re looking for, then we could easily check our stats to see if they need updating, and then if they do, update the specific stat.

In order to do this, I’ve knocked up a little script… it’s a procedure in which you can pass the table name, column name, and the column value you require.

It’s designed for an ascending keys issue and therefore is coded specifically with int style values in mind, but it could easily be adapted to work with other data types as required.

if object_id('statsProc') is not null drop procedure statsProc
go

create procedure statsProc
(
  
@schemaName varchar(100),
  
@tableName varchar(100),
  
@columnName varchar(100),
  
@value int
)
as
   set nocount on

  
-- find smallest stat for that column

  
declare @statName varchar(100) =
  
(
      
select name
      
from
      
(
          
select top 1 ss.name, isnull(max(ic.index_column_id), 1) noColumnsInIndex
          
from sys.tables t
          
join sys.schemas s
          
on t.schema_id = s.schema_id
          
join sys.columns c
          
on t.object_id = c.object_id
          
join sys.stats_columns sc
          
on t.object_id = sc.object_id
          
and sc.column_id = c.column_id
          
join sys.stats ss
          
on t.object_id = ss.object_id
          
and ss.stats_id = sc.stats_id
          
left join sys.indexes i
          
on i.object_id = t.object_id
          
and i.name = ss.name
          
left join sys.index_columns ic
          
on ic.object_id = t.object_id
          
and i.index_id = ic.index_id
          
where s.name = @schemaName
          
and t.name = @tableName
          
and c.name = @columnName
          
group by ss.name
          
order by noColumnsInIndex
      
) x
  
)

  
-- check histrogram values

  
declare @sql varchar(max)
  
declare @histogram table
  
(
      
rangeKey int,
      
rangeRows float,
      
eqRows int,
      
distinctRows int,
      
avgRows float
  
)

  
select @sql = 'dbcc show_statistics (''' + @schemaName + '.' + @tableName + ''', ' + @statName + ') with histogram'

  
insert into @histogram
  
exec (@sql)

  
-- update if stats out of date

  
if
  
(
      
select max(rangeKey)
      
from @histogram
  
) < @value
  
begin
       print
'Update stats is required'

      
select @sql = 'update statistics [' + @schemaName + '].[' + @tableName + '] ' + @statName + ' with fullscan'

      
print @sql
      
exec (@sql)
  
end
go

-- execute the proc

exec statsProc 'sales', 'salesOrderDetail', 'salesOrderDetailID', 1000000
go
Comments:
NB: Comments will only appear once they have been moderated.