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.
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 < 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)