Previously, we looked at analyzing a table to see which columns in that table may contain skewed data.
That was a good start, but now it’s time to look at the statistics that exist on that column to see if we can identify potential candidates for filtered statistics, based on the “windows” between histogram steps.
Much of the logic is the same in this script, except it counts every value in the column. Additionally, it will look at all statistics that exist on that column. If there are no statistics on that column, then we can’t do a histogram step window analysis anyway. The same general principles exist for this analysis as well. We’re looking at the test statistic (zG1) to determine how skewed the data may actually be.
Just like with the table analysis, I worked around data type issues by using dense_rank() over the keys. In the histogram dump table, I created a column called alt_key which I then update based on matching the count table key to the histogram step key.
set @sql = 'dbcc show_statistics('''+quotename(@SchemaName)+'.'+quotename(@TableName)+''','''+@StatName+''')
with histogram,no_infomsgs;'
print @sql;
insert into tempdb.dbo.histo (
range_hi_key,
range_rows ,
eq_rows ,
distinct_range_rows ,
avg_range_rows )
execute sp_executesql @sql
set @sql =
'update h
set h.actual_eq_rows = c.f,
h.alt_key = c.x
from tempdb.dbo.histo h
join '+(@TallyTable)+' c
on h.range_hi_key = c.[key]';
exec sp_executesql @sql;
What this lets me do is pull the analysis results later, without having to muck with sorting on different data types:
select an.*,confirm_query = 'select * from ' + @TallyTable + ' where ' +
case when cols.last_alt_key is not null
then ' where x >= ' + cast(cols.last_alt_key as nvarchar(255)) + ' and '
else ' x <=' + cast(cols.alt_key as nvarchar(255))
end + ' order by [key]'
from tempdb.dbo.histo an
join (
select
stat_name,
last_range_hi_key = lag(range_hi_key,1,null) over (partition by stat_name order by range_hi_key),
range_hi_key,
last_alt_key = lag(alt_key,1,null) over (partition by stat_name order by range_hi_key),
alt_key
from tempdb.dbo.histo
) cols
on an.stat_name = cols.stat_name
and an.range_hi_key = cols.range_hi_key
where an.actual_distinct_range_rows >= 100
order by abs(zg1) desc;
With the above analysis available to us, we’d run the text in confirm_query to examine the window (each histogram step is included) to sanity check the analysis and ensure that the window does present with skew and has enough distinct values to make a filtered statistic worthwhile. Please note that all of the normal considerations with regard to filtered statistics apply. They don’t update with their filtered threshold is met, but only when the threshold for the entire table is met, and only then if the filtered statistics is loaded for plan (re)compilation. They may add more time to any maintenance task. They may never be used. etc. etc. etc.