Partition of a table - how to minimize the time offline? How to check if all the statistics were updated? 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?




  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 &lt; 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)
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