SQL Server Index statistic

 Run below query. If any sampling percentage is between 0.1 and 5, then re-build the index and run the statistic

DECLARE @DatabaseName NVARCHAR(255) = 'Database_Name';

 

-- Create a temporary table to store results

CREATE TABLE #StatsUpdateInfo

(

   TableName NVARCHAR(255),

   StatsName NVARCHAR(255),

   RowsModified INT,

   RowsSampled INT,

   SamplingPercentage FLOAT

);

 

-- Cursor to iterate through each table in the database

DECLARE @TableName NVARCHAR(255);

DECLARE table_cursor CURSOR FOR

   SELECT t.name

   FROM sys.tables t

   WHERE t.is_ms_shipped = 0;

 

-- Variables to store dynamic SQL

DECLARE @SQL NVARCHAR(MAX);

 

OPEN table_cursor;

FETCH NEXT FROM table_cursor INTO @TableName;

 

WHILE @@FETCH_STATUS = 0

BEGIN

   -- Dynamic SQL to gather statistics update information for each table

   SET @SQL = '

       INSERT INTO #StatsUpdateInfo

       SELECT

           ''' + @TableName + ''' AS TableName,

           s.name AS StatsName,

           us.rows AS RowsModified,

           us.rows_sampled AS RowsSampled,

           CASE WHEN us.rows_sampled > 0 THEN CAST(us.rows_sampled * 100.0 / us.rows AS FLOAT) ELSE 0 END AS SamplingPercentage

       FROM

           ' + QUOTENAME(@DatabaseName) + '.sys.tables t

           JOIN ' + QUOTENAME(@DatabaseName) + '.sys.stats s ON t.object_id = s.object_id

           OUTER APPLY sys.dm_db_stats_properties(t.object_id, s.stats_id) us

       WHERE

           t.name = ''' + @TableName + ''';

   ';

 

   -- Execute dynamic SQL

   EXEC sp_executesql @SQL;

 

   FETCH NEXT FROM table_cursor INTO @TableName;

END

 

CLOSE table_cursor;

DEALLOCATE table_cursor;

 

-- Select the results

SELECT *

FROM #StatsUpdateInfo;

 

-- Drop the temporary table

DROP TABLE #StatsUpdateInfo;