Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Scope: A billion record table that is indexed in 10 ways. Rate of change per day is 1/2 a percent.

Some of the indexes are monotonously increasing (datetime/timestamp), yet the common queries most likely hit the tail end. I assume statistics need updating frequently for this type, otherwise the recent data becomes unrepresented in the index?

Other indexes are more randomly distributed, e.g. (Customer Key, Datetime). These can do with less frequent updates, since the statistics are quite representative of the whole. We can let the index change enough to force auto statistics updates on these, correct?

For both types, is there any benefit from increasing the sampling from 10% to 100%, if the data to be sampled is random and quite representative of the whole?

Looking for best practice with TB data.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

If you always query the tail, you can give a shot to filtered indexes. Index 10 ways with, say, WHERE datetimecolumn > @INDEXBUILDDAY-30. Periodically (daily, weekly?) you can move this filter forward by adding (online) a new set of filterd indexes and then dropping the old ones. With a much smaller size and a much more recent build (implying recent stats), they would be 'fresh' and very appetizing for the optimizer. The periodic rebuild doesn't even need to scan the base table (no huge IO, no BP cache pollution), the old filtered index should always be usable as a source to build the next filtered index (it covers it).

For those queries that land outside the covered filtered tail, you can keep some (less than the 10 ways hopefully) full, unfiltered indexes. With the right voodoo invocation the optimizer might get your point and use the filtered ones when appropriate and only resort to the full ones when necessary...

share|improve this answer
    
Ingenious and creative. Thanks for the suggestions. –  孔夫子 Nov 4 '12 at 23:55
add comment

There are a couple of trace flags that can help with statistics on ascending keys, TF2389 and TF2390.

Since 2005SP1, SQL Server has identified and "branded" ascending keys. If TF2389 is set and the leading column of a covering index is branded, the statistics for the column will be updated automatically by identifying the highest key value and updating the existing histogram. Enabling TF2930 causes the same update to occur where a column hasn't been identified (branded) as ascending.

Keep in mind that the statistics update occurs during query compilation, so if recompilation is rare you may want to test a RECOMPILE hint on some procedures or queries to force an update. Alternatively, a periodic sp_recompile on the procedure may help if the cost of compilation is high.

Statistics on Ascending Columns is a great article that covers the mechanics.

NB: Usual caveats to using trace flags apply... test, test and test some more.

share|improve this answer
add comment

I don't have a precise answer for your direct question, however I was recently faced with a similar dilemma. Our tables are quit a lot smaller, however they have enough records that the auto-statistics update was almost never working.

We are interested in updating statistics after a minimum number of records are changed, not a minimum percent of the records. For instance, if more than 1,000,000 records have been added or modified, we rebuild the statistics.

I accomplished this by adding a TIMESTAMP field (the database-wide auto-incrementing BINARY(8) style timestamp) to the table(s) in question.

I created a stored procedure to update statistics that looks at the StatsThreshold table to determine which tables to update. The SP is similar to:

CREATE PROCEDURE AutoUpdateStats
(
    @TestOnly bit = 1   /* Defaults to TEST-ONLY mode       */
    , @FullScan bit = 0 /* Defaults to NON FULLSCAN mode    */
)
AS
BEGIN
    /*
        Executes UPDATE STATISTICS against tables with more than x number of changed rows
        By:     Max Vernon
        Date:   2012-10-02
    */
    SET NOCOUNT ON;

    /* if StatsThreshold Table does not exist, create it        */
    IF COALESCE((SELECT name FROM sys.tables WHERE name = 'StatsThreshold'),'')=''
    BEGIN
        CREATE TABLE StatsThreshold
        (
            StatsThreshold INT NOT NULL PRIMARY KEY CONSTRAINT PK_StatsThreshold IDENTITY(1,1)
            , TableName nvarchar(255)
            , LastTimeStamp BINARY(8) CONSTRAINT DF_StatsThreshold_LastTimeStamp DEFAULT((0x0000000000000001))
            , Threshold INT NOT NULL CONSTRAINT DF_StatsThreshold_Threshold DEFAULT((0))
            , StatsThresholdTimeStamp TIMESTAMP
        );
        /*  Create a starting point for StatsThreshold */
        /*  StatsThreshold will need to be manually updated as new tables are brought online */
        INSERT INTO StatsThreshold (TableName, LastTimeStamp, Threshold)
        SELECT name, 0x0000000000000001, 10 /* 10 is the minimum # of changes necessary  */
        FROM sys.tables;
    END

    DECLARE @cmd nvarchar(max);
    DECLARE @params nvarchar(max);
    DECLARE @Table nvarchar(255);
    DECLARE @Threshold int;
    DECLARE @Count int;
    DECLARE @TSField nvarchar(max);
    DECLARE cur cursor local forward_only for
    SELECT TableName, Threshold 
    FROM StatsThreshold;
    OPEN cur;
    FETCH NEXT FROM cur INTO @Table, @Threshold;
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @TSField = (            
            select top(1) c.name
            from sys.columns c
                inner join sys.tables t on c.object_id = t.object_id
                inner join sys.types ty on c.system_type_id = ty.system_type_id
            where t.name = @Table
                and ty.name = 'timestamp'
            );
        IF COALESCE(@TSField,'')=''
        BEGIN
            /* create a timestamp field in the current table */
            SET @TSField = @Table + 'TimeStamp';
            SET @cmd = 'ALTER TABLE ' + QUOTENAME(@Table) + ' ADD ' + quotename(@TSField) + ' TIMESTAMP;';
            RAISERROR (@cmd, 0, 1) WITH NOWAIT;
            IF @TestOnly = 0 
            BEGIN
                EXEC sp_executesql @cmd;
                SET @TSField = @Table + 'TimeStamp';
            END
            ELSE
            BEGIN
                SET @TSField = '';
            END
        END

        IF @TSField <> ''
        BEGIN
            SET @Count = 0;
            IF @TestOnly = 1 RAISERROR (@TSField, 0, 1) WITH NOWAIT;
            /* Get the number of new/changed rows */
            SET @cmd = 'SET @CountChanged = (SELECT COUNT(*) FROM ' + quotename(@Table) + ' WHERE ' + quotename(@TSField) + ' > (SELECT LastTimeStamp FROM StatsThreshold WHERE TableName = ''' + @Table + '''));';
            IF @TestOnly = 1 RAISERROR (@cmd, 0, 1) WITH NOWAIT;
            SET @params =  '@CountChanged int OUTPUT';
            EXEC sp_executesql @cmd, @params, @CountChanged = @Count OUTPUT;
            IF @TestOnly = 1 
            BEGIN
                SET @cmd = '@Count = ' + CAST(COALESCE(@Count,0) as nvarchar(max)) + ', @Threshold = ' + CAST(@Threshold as nvarchar(max));
                RAISERROR (@cmd, 0, 1) WITH NOWAIT;
            END
            /* If the # of new/changed rows is over the threshold, do the STATISTICS UPDATE  */
            IF @Count >= @Threshold
            BEGIN
                SET @cmd = 'UPDATE STATISTICS ' + quotename(@Table) + CASE WHEN @FullScan = 1 THEN ' WITH FULLSCAN' ELSE '' END + ';';
                RAISERROR (@cmd, 0, 1) WITH NOWAIT;
                IF @TestOnly = 0 EXEC sp_executesql @cmd;
                SET @cmd = 'UPDATE StatsThreshold SET LastTimeStamp = (SELECT MAX(' + quotename(@TSField) + ') FROM ' + Quotename(@Table) + ') WHERE TableName = ''' + @Table + ''';';
                IF @TestOnly = 1 RAISERROR (@cmd, 0, 1) WITH NOWAIT;
                IF @TestOnly = 0 EXEC sp_executesql @cmd;
            END
            ELSE
            BEGIN
                SET @cmd = @Table + ' does not require UPDATE STATISTICS';
                RAISERROR (@cmd, 0, 1) WITH NOWAIT;
            END
        END
        ELSE
        BEGIN
            SET @cmd = @Table + ' does not have a TIMESTAMP field.';
            RAISERROR (@cmd, 0, 1) WITH NOWAIT;
        END
        FETCH NEXT FROM cur INTO @Table, @Threshold;
    END
    CLOSE cur;
    DEALLOCATE cur;
END
GO

This stored procedure could be added to a SQL Server Agent job and ran nightly, or weekly or whatever fits your needs.

I have tested the above code on a test database, however you assume all risks and agree to only USE AT YOUR OWN RISK! Specifically, the code will add a TIMESTAMP field to ALL tables in your database. This MAY CAUSE YOU GREAT PAIN if your system does not allow fields to be added to tables without reworking other code.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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