3

How often should you update your stats? What is "too seldom"? How often is "too often"?

The answer is "it depends" on your database, users, data, etc.

So I've tried to log what our stats look like over time, in two tables. Here they are:

DROP TABLE /*IF EXISTS */ dbo.dm_db_stats_histogram
DROP TABLE /*IF EXISTS */ dbo.dm_db_stats_properties
go
CREATE TABLE dbo.dm_db_stats_properties(
  dm_db_stats_propertiesID INT IDENTITY(1,1) NOT NULL constraint PK_dm_db_stats_properties PRIMARY KEY CLUSTERED,
  DatabaseId INT NOT NULL,
  object_id int NOT NULL,
  stats_id  int NOT NULL,
  last_updated  DATETIME2 NOT NULL,
  rows  BIGINT NOT NULL,
  rows_sampled  BIGINT NOT NULL,
  steps int NOT NULL,
  unfiltered_rows   BIGINT NOT NULL,
  modification_counter  BIGINT NOT NULL,
  persisted_sample_percent  FLOAT  NULL
  , SampleDate DATETIME2 NOT NULL CONSTRAINT df_dm_db_stats_properties_SampleDate DEFAULT SYSUTCDATETIME()
)
GO
ALTER TABLE dbo.dm_db_stats_properties ADD StatsName NVARCHAR(128) NOT NULL CONSTRAINT df_dm_db_stats_properties_StatsName DEFAULT ('')

GO
CREATE TABLE dbo.dm_db_stats_histogram(
  dm_db_stats_histogramID INT IDENTITY(1,1) NOT NULL constraint PK_dm_db_stats_histogram PRIMARY KEY CLUSTERED,
  dm_db_stats_propertiesID INT NOT NULL, 
  object_id int NOT NULL,
  stats_id  int NOT NULL,
  step_number   int NOT NULL,
  range_high_key    sql_variant NOT NULL,
  range_rows    real    NOT NULL,
  equal_rows    real    NOT NULL,
  distinct_range_rows   bigint  NOT NULL,
  average_range_rows    REAL NOT NULL
)
go
ALTER TABLE dbo.dm_db_stats_histogram ADD CONSTRAINT fk_dm_db_stats_properties FOREIGN KEY(dm_db_stats_propertiesID) REFERENCES dbo.dm_db_stats_properties(dm_db_stats_propertiesID)
ALTER TABLE dbo.dm_db_stats_histogram ALTER COLUMN range_high_key SQL_VARIANT NULL
GO

And here is the code I use to log our stats:

SET NOCOUNT ON
BEGIN TRY
  DROP TABLE #Stat_Header
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #Stat_Header (Name sysname, Updated DATETIME, Rows BIGINT, Rows_Sampled BIGINT, Steps SMALLINT, Density REAL, AverageKeyLength INT, StringIndex varchar(10)
, FilterExpression varchar(8000), unfiltered_rows bigint, persisted_sample_percent float)

BEGIN TRY
  DROP TABLE #Histogram
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #Histogram (Step_Number INT IDENTITY(1,1), range_high_key SQL_VARIANT, range_rows REAL NOT NULL, equal_rows    REAL NOT NULL, distinct_range_rows BIGINT NOT NULL, average_range_rows REAL NOT NULL)


DECLARE TableCursor CURSOR LOCAL STATIC FOR 
    SELECT t.name AS TableName, sc.name AS SchemaName
    FROM sys.tables t 
    INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
    ORDER BY sc.name, t.name

DECLARE @sql NVARCHAR(MAX) = '', @TableName VARCHAR(100), @SchemaName VARCHAR(100), @loopCounter INT =0
SELECT GETDATE() AS StartDate
OPEN TableCursor
WHILE 1 =1 BEGIN
    FETCH TableCursor INTO @TableName, @SchemaName
    IF @@fetch_status <> 0 BREAK

    SELECT @sql = 'declare @Scope_Identity int = 0, @RowCount int
    SET NOCOUNT ON' + CHAR(13)
    SELECT @sql += '
    TRUNCATE TABLE #Stat_Header
    TRUNCATE TABLE #Histogram 
    INSERT INTO #Stat_Header(Name, Updated, Rows, Rows_Sampled, Steps, Density, AverageKeyLength, StringIndex, FilterExpression, unfiltered_rows/*, persisted_sample_percent*/)
    exec (''DBCC SHOW_STATISTICS ("' + @SchemaName + '.' + @TableName + '", "' + s.name +'") with STAT_HEADER'')
    INSERT INTO dbo.dm_db_stats_properties(databaseid, object_id, stats_id, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter, persisted_sample_percent, SampleDate, StatsName)
    SELECT db_id(), ' + LTRIM(t.object_id) + ', ' + LTRIM(s.stats_id) +', coalesce(sh.Updated, ''2000-01-01''), isnull(sh.rows,0), isnull(sh.Rows_Sampled,0), isnull(sh.steps,0), isnull(sh.unfiltered_rows,0), 0, sh.persisted_sample_percent, cast(''' + LTRIM(SYSUTCDATETIME()) + ''' as datetime2(7)), ''' + s.name + '''
    FROM #Stat_Header sh
    LEFT JOIN dbo.dm_db_stats_properties sp ON sp.object_id=' + LTRIM(t.object_id) + ' AND sp.stats_id=' + LTRIM(s.stats_id) + ' AND sh.Updated=sp.last_updated
    WHERE sp.dm_db_stats_propertiesID IS NULL
    SELECT @Scope_Identity = SCOPE_IDENTITY(), @RowCount=@@ROWCOUNT
    IF @RowCount>0 BEGIN 
        --raiserror (''here'', 10, 1) with nowait
        INSERT INTO #Histogram(range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
        exec (''DBCC SHOW_STATISTICS ("' + @SchemaName + '.' + @TableName + '", "' + s.name +'") with HISTOGRAM'')
        INSERT INTO dbo.dm_db_stats_histogram(dm_db_stats_propertiesID, object_id, stats_id, step_number, range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
        SELECT @Scope_Identity, ' + LTRIM(t.object_id) + ', ' + LTRIM(s.stats_id) +', h.Step_Number, h.range_high_key, h.range_rows, h.equal_rows, h.distinct_range_rows, h.average_range_rows
        FROM #Histogram h
    END 
    raiserror (''table = ' + @TableName + ', ' + s.name + ', rc= %i '', 10, 1, @RowCount) with nowait
    waitfor delay ''00:00:01''
    '
    FROM sys.stats AS s
    INNER JOIN sys.tables t ON t.object_id = s.object_id
    INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
    WHERE t.name=@TableName
    AND sc.name = @SchemaName
    IF @loopCounter < 1 EXEC dbo.LongPrint @String=@sql
    SET @loopCounter +=1
    EXEC sp_executesql @sql
    --BREAK
END
DEALLOCATE TableCursor
SELECT GETDATE() AS StopDate

I also have a solution that uses the new DMV's on SQL Server 2016+17

exec sp_foreachdb @command = N'
use ?
DECLARE @SampleDate DATETIME2 = SYSUTCDATETIME()

  INSERT INTO master.dbo.dm_db_stats_properties(DatabaseID, object_id, stats_id, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter, persisted_sample_percent, SampleDate, StatsName)
  SELECT db_id() as DatabaseID, s.object_id, s.stats_id, sp.last_updated, sp.rows, sp.rows_sampled, sp.steps, sp.unfiltered_rows, sp.modification_counter, sp.persisted_sample_percent, @SampleDate, s.name
  FROM ?.sys.stats AS s
  INNER JOIN ?.sys.tables t ON t.object_id = s.object_id
  INNER JOIN ?.sys.schemas sc ON sc.schema_id = t.schema_id
  CROSS APPLY ?.sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
  LEFT JOIN master.dbo.dm_db_stats_properties T1 ON T1.object_id = s.object_id AND T1.stats_id = s.stats_id AND T1.last_updated=sp.last_updated
  WHERE sp.last_updated IS NOT NULL
  AND T1.last_updated IS NULL
  select @@rowcount as r1

INSERT INTO master.dbo.dm_db_stats_histogram(dm_db_stats_propertiesID, object_id, stats_id, step_number, range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
SELECT sp.dm_db_stats_propertiesID, sp.object_id, sp.stats_id, hist.step_number, hist.range_high_key, hist.range_rows, hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM master.dbo.dm_db_stats_properties sp
CROSS APPLY ?.sys.dm_db_stats_histogram(sp.[object_id], sp.stats_id) AS hist
WHERE sp.SampleDate = @SampleDate
  select @@rowcount as r2

', @exclude_list='tempdb, model', @print_dbname=1


My real question

is now, how do I write queries, based on the data that I've collected, that will show me
a) which tables, indexes, columns that do (not) change a lot over my sampling period?
b) which tables, index, columns that benefit from a WITH FULLSCAN command?

  • yes, I too use Ola's scripts. But I often wonder if I'm over- or under- updating stats. (yes, I use the part d) in the help text about updating stats) – Henrik Staun Poulsen Jan 11 at 10:37
  • I have just moved one of my databases to Azure SQL DB, where I do not want to spend time/money on unnecessary tasks. – Henrik Staun Poulsen Jun 21 at 7:40
6
+50

b) which tables, index, columns that benefit from a WITH FULLSCAN command?

From my point of view you aren't gathering the right data to answer that question. If you're looking for improvements that you can make just by analyzing statistics on the database, I can only think of two query performance issues that can be caused by using sampled statistics instead of FULLSCAN:

  1. The density is off by at least an order of magnitude.

    Some data distributions aren't a good fit for some of the assumptions that SQL Server makes when turning sampled data into a histogram. In those situations you can end up with density which is off by 10X, 100X, or even more. That can cause performance issues for queries that use the density vector in the statistics object.

    You can search for possible issues by saving off density information for all sampled statistics, gathering statistics on all relevant columns with FULLSCAN, and comparing the densities between the two result sets. Anything which is too inaccurate is a candidate for seeing a benefit from gathering stats in full.

  2. The query is vulnerable to the ascending key problem.

    You have SQL Server 2008 listed as a tag so this still might be relevant to you. Consider a column that stores the datetime when a row was inserted. If you have queries that filter on that column looking for very recent data they might be searching for data outside of the histogram. With the legacy CE you can end up with very low cardinality estimates with can lead to query performance issues.

    This can be addressed with FULLSCAN stats, although it feels a bit overkill to me. You could gather stats in full on all statistics with a relevant data type (hopefully no need to worry about ascending key for VARCHAR) a few different times and see how the maximum high key changes.

For both of the above issues, I can't think of a way to programmatically find them just by looking at sampled statistics alone. That's why I said that you weren't gathering the right data to answer your question.

If you care for my opinion, the way to truly minimize statistics maintenance is to analyze the workload for queries that aren't performing well enough, do a careful root cause analysis to figure out when statistics issues are contributing, identify the precise type of statistics issue, and finally adjust the statistics maintenance job accordingly.

  • I have never seen problems with the density, but I'll try to collect the data, to see if you're right. I've often seen problems with the histograms; mostly the Ascending key problem, but also problems with frequent vs non-frequent values as with the ReputationID in the StackOverFlow database. Hence the request for help. I have also collected the stats when if was created WITH FULLSCAN, on specific dates. So I should be able to query that. It is just difficult to write the query. – Henrik Staun Poulsen Jun 25 at 12:53
  • @HenrikStaunPoulsen Would it be helpful if I included example queries to check for the two issues that I mentioned here? – Joe Obbish Jun 25 at 23:10
  • yes, please. That would be great. – Henrik Staun Poulsen Jun 26 at 6:59
  • I've just read about PERSIST_SAMPLE_PERSIST. It works on Azure SQL DB too. This may help me from having to run UPDATE STATS every night, from fear of being hit by auto-update stats – Henrik Staun Poulsen Jun 26 at 9:37
2

My own steps

I have spent quite some time on this, but the most useful so far, is a query that shows me the tables/columns/index with the biggest difference in number of steps:

drop table /* if exists */ #inv
create table #inv(dm_db_stats_propertiesID int, DatabaseID int, object_id int, stats_id int, Steps varchar(100), dm_db_stats_propertiesIDs varchar(400)
, DatabaseName sysname, StatsName varchar(100) null, TableName varchar(100) null, ColumnName varchar(100) null, minSteps int, maxSteps int)

insert into #inv( dm_db_stats_propertiesID, DatabaseID, object_id, stats_id, Steps, dm_db_stats_propertiesIDs, DatabaseName, StatsName, minSteps, maxSteps)
SELECT dm_db_stats_propertiesID, DatabaseID, object_id, stats_id, Steps, dm_db_stats_propertiesIDs, a.Name as DatabaseName, StatsName, minSteps, maxSteps
from (
    SELECT top (10000) L.Name, sp.*
    , stuff((select ', ' +ltrim(steps) from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id for xml path('')),1,2,'') as Steps2
    , stuff((select ', ' + ltrim(dm_db_stats_propertiesID) from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id for xml path('')),1,2,'') as dm_db_stats_propertiesIDs
    , (select count(*) as rowcnt from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id ) as rowcnt
    , (select min(Steps) as minSteps from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id ) as minSteps
    , (select max(Steps) as maxSteps from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id ) as maxSteps
    FROM dbo.dm_db_stats_properties sp
    inner join #List L on sp.DatabaseID=L.DatabaseID and sp.SampleDate>=L.SampleDate
) as a
where a.Steps2 like '%,%' --',%'

exec sp_foreachdb 'update #inv set TableName = t.name /* select * */ from #inv i inner join ?.sys.tables t on i.object_id = t.object_id and (''['' + i.DatabaseName + '']'') = ''?'''

exec sp_foreachdb 'update #inv set ColumnName = c.name from #inv i
inner join ?.sys.columns c on i.object_id=c.object_id and c.column_id = convert(int, convert(varbinary, SUBSTRING(i.StatsName, 9, 8),2)) and (''['' + i.DatabaseName + '']'') = ''?''
where i.StatsName like ''_WA_Sys%''
'

select * , (0.0+maxSteps-minSteps)/maxSteps * 100  as PctChange
from #inv
where minSteps <> maxSteps
and DatabaseName <> 'master'
AND TableName NOT LIKE 'dm_db_stats%'
order by PctChange desc

not nearly what I was hoping for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.