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

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

-- find smallest stat for that column

declare @statName varchar(100) =
select name
select top 1, 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 =
left join sys.index_columns ic
on ic.object_id = t.object_id
and i.index_id = ic.index_id
where = @schemaName
and = @tableName
and = @columnName
group by
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

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

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

print @sql
exec (@sql)

-- execute the proc

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