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