Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Script to Update Specific Stats
Published: Sep 10, 2017
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 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

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
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.