SQL Server Table Partitioning – Minimizing Downtime and Updating Statistics

migrationpartitioningsql serverstatistics

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)

Best Answer

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.

Related Question