envstatsfull

Snippet Name: envstatsfull

Tags: helpful,scriptgeneration

Created Date: 2021-10-01 08:05:46
Last Modified Date: 2024-11-13 06:29:19

–desc: create commands to rebuild all statistics

Dependencies: none-standalone code

envstatsfull–envstatsfull: This script performs a series of updates to SQL Server statistics, creating commands to rebuild them. It first selects distinct schema and table names, then calculates rows modified and total rows for each table, and finally generates commands to update these tables with specific parameters, including sampling percentages and the modification counter.

DBA Notes: buidls the commands ot update every statisitc for each table

Sample Output:

--desc: create commands to rebuild all statistics
SELECT  QUOTENAME(&#91;X].&#91;SCHEMA Name]) + '.'
                   + QUOTENAME(&#91;X].&#91;TABLE Name])AS QualifiedObjectName,
                   MAX(&#91;X].&#91;RowsModified]) AS &#91;MaxRowsModified],
'--' + &#91;X].&#91;SCHEMA Name] + '.' + &#91;X].&#91;TABLE Name] +  '; RowsModified = ' + CONVERT(VARCHAR,MAX(&#91;X].&#91;RowsModified])) + ';TotalRows = ' + CONVERT(VARCHAR,MAX(&#91;Total ROWS])) + '
RAISERROR(''' + &#91;X].&#91;SCHEMA Name] + '.' + &#91;X].&#91;TABLE Name] +  ' Item ' + CONVERT(VARCHAR(30),ROW_NUMBER() OVER(ORDER BY MAX(&#91;X].&#91;RowsModified]) DESC)) +  '; RowsModified = ' + CONVERT(VARCHAR,MAX(&#91;X].&#91;RowsModified])) + ';TotalRows = ' + CONVERT(VARCHAR,MAX(&#91;Total ROWS]))  + ''',0,1)' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+' UPDATE STATISTICS ' + QUOTENAME(&#91;X].&#91;SCHEMA Name]) + '.'
                   + QUOTENAME(&#91;X].&#91;TABLE Name])
                   + ' WITH ALL,FULLSCAN' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) AS StatsCommand,
'--' + &#91;X].&#91;SCHEMA Name] + '.' + &#91;X].&#91;TABLE Name] +  '; RowsModified = ' + CONVERT(VARCHAR,MAX(&#91;X].&#91;RowsModified])) + ';TotalRows = ' + CONVERT(VARCHAR,MAX(&#91;Total ROWS])) + '
RAISERROR(''' + &#91;X].&#91;SCHEMA Name] + '.' + &#91;X].&#91;TABLE Name] +  ' Item ' + CONVERT(VARCHAR(30),ROW_NUMBER() OVER(ORDER BY MAX(&#91;X].&#91;RowsModified]) DESC)) +  '; RowsModified = ' + CONVERT(VARCHAR,MAX(&#91;X].&#91;RowsModified])) + ';TotalRows = ' + CONVERT(VARCHAR,MAX(&#91;Total ROWS]))  + ''',0,1)' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
+' UPDATE STATISTICS ' + QUOTENAME(&#91;X].&#91;SCHEMA Name]) + '.'
                   + QUOTENAME(&#91;X].&#91;TABLE Name])
                   + ' WITH ALL,SAMPLE 50 PERCENT' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) AS Stats50Command


				   FROM   (SELECT   DISTINCT 
      DB_NAME()   AS &#91;DATABASE],
        s.name AS &#91;SCHEMA Name],
        t.name AS &#91;TABLE Name],
        --a.name,
        --sp.last_updated,
        &#91;sp].&#91;modification_counter]  AS &#91;RowsModified],
        &#91;sp].&#91;rows] AS &#91;Total ROWS],
        CASE
              WHEN &#91;sp].&#91;modification_counter] &gt; &#91;sp].&#91;rows]
              THEN
                100
              ELSE
                CONVERT(DECIMAL(8, 2), ( (  &#91;sp].&#91;modification_counter] * 1.0 ) / &#91;sp].&#91;rows] * 1. ) * 100.0)
            END         AS &#91;PERCENT Modified]
  FROM  sys.schemas s
        join sys.tables t on s.schema_id = t.schema_id
        join sys.stats a on t.object_id = a.object_id
        --JOIN sys.indexes i ON t.&#91;object_id] = i.&#91;object_id]
        CROSS APPLY sys.dm_db_stats_properties (t.object_id, a.stats_id) sp
WHERE &#91;sp].&#91;modification_counter] &gt; 1000) &#91;X]
      WHERE  &#91;X].&#91;RowsModified] &gt; 1000
GROUP BY &#91;X].&#91;SCHEMA Name] , &#91;X].&#91;TABLE Name] 
ORDER BY &#91;MaxRowsModified] DESC

Stormrage

Senior SQL DBA and Code Monkey Script Dump

Snippet Name: envstatsfull

Leave a Reply Cancel reply