2

As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records.

Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were updated.

I am considering partitioning this table because I parts of it are never updated, just need to be there for reading purposes.

The primary key is an identity field, as you can see on the picture below.

questions:

1) this is a very busy table, what can I do to minimize the impact of having to lock the table and rebuild the clustered index?

2) if I leave the table as it is, how can I be sure that all the statistics for all the index were updated? Is there a script for this?

enter image description here

This is the way I am currently checking when the statistics where last updated.

 declare @dbname nvarchar(128)

   select @dbname = db_name()

    DECLARE @SQL NVARCHAR(MAX)
    --DECLARE @ParamDefinition NVARCHAR(MAX)
    DECLARE @TABLEID INT  
    DECLARE @TABLEID_STR VARCHAR(15) 

    DECLARE  @log NVARCHAR(MAX)
            ,@vCrlf CHAR(2);


SELECT @vCrlf = CHAR(13)+CHAR(10);


select @TABLEID = object_id from sys.tables where name = 'MYTABLE'

SELECT @TABLEID      = COALESCE(@TABLEID,0)
SELECT @TABLEID_STR  = CAST( @TABLEID AS VARCHAR)

SELECT @SQL = ' USE ' + @dbName + ';' + @vCrlf  +
              ' SET NOCOUNT ON '  + @vCrlf  +
              ' SET DATEFORMAT DMY '  + @vCrlf  +
              ' SET DEADLOCK_PRIORITY NORMAL; '  + @vCrlf  +
              ' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ' + @vCrlf +

'SELECT o.object_id,
       s_name=s.name,
       o.name,
       ddps.row_count 
       ,[Statistics_Updated]=STATS_DATE(I.OBJECT_ID,I.INDEX_ID)
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  ' + @vCrlf +

    CASE WHEN @TABLEID = 0 THEN '' + @vCrlf  
                           ELSE ' AND ( i.object_id = ' + @TABLEID_STR + ') ' + @vCrlf 
    END +

' ORDER BY o.NAME ' + @vCrlf 

    PRINT CAST (@SQL AS NTEXT) 
    --EXEC (@SQL)
2

Migrate piece-wise. Rename your table to tblsapdispatch_old. Create a new table called tblsapdispatch_new. This new table has the partitioning you want. Create a view called tblsapdispatch which unions the two together. This way the application is agnostic to the change.

Move data from _old to _new in batches. The batch size will be found by testing. Move data one partition at a time. Rebuild that partition's index once full. You may be able to make the historical partitions read-only so you'll never have to rebuild those indexes again.

When all's done drop the view and rename _new.

You should only require an outage at the beginning and end, when the table names change.

2

from the technical article Statistics Used by the Query Optimizer in Microsoft SQL Server 2008statistics are out of date when:

  • The table size has gone from 0 to >0 rows.

  • The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the
    statistics object has changed by more than 500 since then.

  • The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has
    changed by more than 500 + 20% of the number of rows in the table
    when the statistics were gathered.

Moreover you can use dmv sys.dm_db_stats_properties to check when stats were last updated

SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'table_name_here');

Also suggest you to go through article Understanding When Statistics Will Automatically Update

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.