· 7 years ago · Jan 10, 2019, 03:34 PM
1DROP TABLE /*IF EXISTS */ dbo.dm_db_stats_histogram
2DROP TABLE /*IF EXISTS */ dbo.dm_db_stats_properties
3go
4CREATE TABLE dbo.dm_db_stats_properties(
5 dm_db_stats_propertiesID INT IDENTITY(1,1) NOT NULL constraint PK_dm_db_stats_properties PRIMARY KEY CLUSTERED,
6 DatabaseId INT NOT NULL,
7 object_id int NOT NULL,
8 stats_id int NOT NULL,
9 last_updated DATETIME2 NOT NULL,
10 rows BIGINT NOT NULL,
11 rows_sampled BIGINT NOT NULL,
12 steps int NOT NULL,
13 unfiltered_rows BIGINT NOT NULL,
14 modification_counter BIGINT NOT NULL,
15 persisted_sample_percent FLOAT NULL
16 , SampleDate DATETIME2 NOT NULL CONSTRAINT df_dm_db_stats_properties_SampleDate DEFAULT SYSUTCDATETIME()
17)
18GO
19ALTER TABLE dbo.dm_db_stats_properties ADD StatsName NVARCHAR(128) NOT NULL CONSTRAINT df_dm_db_stats_properties_StatsName DEFAULT ('')
20
21GO
22CREATE TABLE dbo.dm_db_stats_histogram(
23 dm_db_stats_histogramID INT IDENTITY(1,1) NOT NULL constraint PK_dm_db_stats_histogram PRIMARY KEY CLUSTERED,
24 dm_db_stats_propertiesID INT NOT NULL,
25 object_id int NOT NULL,
26 stats_id int NOT NULL,
27 step_number int NOT NULL,
28 range_high_key sql_variant NOT NULL,
29 range_rows real NOT NULL,
30 equal_rows real NOT NULL,
31 distinct_range_rows bigint NOT NULL,
32 average_range_rows REAL NOT NULL
33)
34go
35ALTER TABLE dbo.dm_db_stats_histogram ADD CONSTRAINT fk_dm_db_stats_properties FOREIGN KEY(dm_db_stats_propertiesID) REFERENCES dbo.dm_db_stats_properties(dm_db_stats_propertiesID)
36ALTER TABLE dbo.dm_db_stats_histogram ALTER COLUMN range_high_key SQL_VARIANT NULL
37GO
38
39SET NOCOUNT ON
40BEGIN TRY
41 DROP TABLE #Stat_Header
42END TRY
43BEGIN CATCH
44END CATCH
45CREATE TABLE #Stat_Header (Name sysname, Updated DATETIME, Rows BIGINT, Rows_Sampled BIGINT, Steps SMALLINT, Density REAL, AverageKeyLength INT, StringIndex varchar(10)
46, FilterExpression varchar(8000), unfiltered_rows bigint, persisted_sample_percent float)
47
48BEGIN TRY
49 DROP TABLE #Histogram
50END TRY
51BEGIN CATCH
52END CATCH
53CREATE TABLE #Histogram (Step_Number INT IDENTITY(1,1), range_high_key SQL_VARIANT, range_rows REAL NOT NULL, equal_rows REAL NOT NULL, distinct_range_rows BIGINT NOT NULL, average_range_rows REAL NOT NULL)
54
55
56DECLARE TableCursor CURSOR LOCAL STATIC FOR
57 SELECT t.name AS TableName, sc.name AS SchemaName
58 FROM sys.tables t
59 INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
60 ORDER BY sc.name, t.name
61
62DECLARE @sql NVARCHAR(MAX) = '', @TableName VARCHAR(100), @SchemaName VARCHAR(100), @loopCounter INT =0
63SELECT GETDATE() AS StartDate
64OPEN TableCursor
65WHILE 1 =1 BEGIN
66 FETCH TableCursor INTO @TableName, @SchemaName
67 IF @@fetch_status <> 0 BREAK
68
69 SELECT @sql = 'declare @Scope_Identity int = 0, @RowCount int
70 SET NOCOUNT ON' + CHAR(13)
71 SELECT @sql += '
72 TRUNCATE TABLE #Stat_Header
73 TRUNCATE TABLE #Histogram
74 INSERT INTO #Stat_Header(Name, Updated, Rows, Rows_Sampled, Steps, Density, AverageKeyLength, StringIndex, FilterExpression, unfiltered_rows/*, persisted_sample_percent*/)
75 exec (''DBCC SHOW_STATISTICS ("' + @SchemaName + '.' + @TableName + '", "' + s.name +'") with STAT_HEADER'')
76 INSERT INTO dbo.dm_db_stats_properties(databaseid, object_id, stats_id, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter, persisted_sample_percent, SampleDate, StatsName)
77 SELECT db_id(), ' + LTRIM(t.object_id) + ', ' + LTRIM(s.stats_id) +', coalesce(sh.Updated, ''2000-01-01''), isnull(sh.rows,0), isnull(sh.Rows_Sampled,0), isnull(sh.steps,0), isnull(sh.unfiltered_rows,0), 0, sh.persisted_sample_percent, cast(''' + LTRIM(SYSUTCDATETIME()) + ''' as datetime2(7)), ''' + s.name + '''
78 FROM #Stat_Header sh
79 LEFT JOIN dbo.dm_db_stats_properties sp ON sp.object_id=' + LTRIM(t.object_id) + ' AND sp.stats_id=' + LTRIM(s.stats_id) + ' AND sh.Updated=sp.last_updated
80 WHERE sp.dm_db_stats_propertiesID IS NULL
81 SELECT @Scope_Identity = SCOPE_IDENTITY(), @RowCount=@@ROWCOUNT
82 IF @RowCount>0 BEGIN
83 --raiserror (''here'', 10, 1) with nowait
84 INSERT INTO #Histogram(range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
85 exec (''DBCC SHOW_STATISTICS ("' + @SchemaName + '.' + @TableName + '", "' + s.name +'") with HISTOGRAM'')
86 INSERT INTO dbo.dm_db_stats_histogram(dm_db_stats_propertiesID, object_id, stats_id, step_number, range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
87 SELECT @Scope_Identity, ' + LTRIM(t.object_id) + ', ' + LTRIM(s.stats_id) +', h.Step_Number, h.range_high_key, h.range_rows, h.equal_rows, h.distinct_range_rows, h.average_range_rows
88 FROM #Histogram h
89 END
90 raiserror (''table = ' + @TableName + ', ' + s.name + ', rc= %i '', 10, 1, @RowCount) with nowait
91 waitfor delay ''00:00:01''
92 '
93 FROM sys.stats AS s
94 INNER JOIN sys.tables t ON t.object_id = s.object_id
95 INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
96 WHERE t.name=@TableName
97 AND sc.name = @SchemaName
98 IF @loopCounter < 1 EXEC dbo.LongPrint @String=@sql
99 SET @loopCounter +=1
100 EXEC sp_executesql @sql
101 --BREAK
102END
103DEALLOCATE TableCursor
104SELECT GETDATE() AS StopDate
105
106drop table /* if exists */ #inv
107create table #inv(dm_db_stats_propertiesID int, DatabaseID int, object_id int, stats_id int, Steps varchar(100), dm_db_stats_propertiesIDs varchar(400)
108, DatabaseName sysname, StatsName varchar(100) null, TableName varchar(100) null, ColumnName varchar(100) null, minSteps int, maxSteps int)
109
110insert into #inv( dm_db_stats_propertiesID, DatabaseID, object_id, stats_id, Steps, dm_db_stats_propertiesIDs, DatabaseName, StatsName, minSteps, maxSteps)
111SELECT dm_db_stats_propertiesID, DatabaseID, object_id, stats_id, Steps, dm_db_stats_propertiesIDs, a.Name as DatabaseName, StatsName, minSteps, maxSteps
112from (
113 SELECT top (10000) L.Name, sp.*
114 , stuff((select ', ' +ltrim(steps) from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id for xml path('')),1,2,'') as Steps2
115 , stuff((select ', ' + ltrim(dm_db_stats_propertiesID) from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id for xml path('')),1,2,'') as dm_db_stats_propertiesIDs
116 , (select count(*) as rowcnt from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id ) as rowcnt
117 , (select min(Steps) as minSteps from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id ) as minSteps
118 , (select max(Steps) as maxSteps from dbo.dm_db_stats_properties s2 where s2.DatabaseID=sp.DatabaseID and s2.object_id = sp.object_id and s2.stats_id=sp.stats_id ) as maxSteps
119 FROM dbo.dm_db_stats_properties sp
120 inner join #List L on sp.DatabaseID=L.DatabaseID and sp.SampleDate>=L.SampleDate
121) as a
122where a.Steps2 like '%,%' --',%'
123
124exec sp_foreachdb 'update #inv set TableName = t.name /* select * */ from #inv i inner join ?.sys.tables t on i.object_id = t.object_id and (''['' + i.DatabaseName + '']'') = ''?'''
125
126exec sp_foreachdb 'update #inv set ColumnName = c.name from #inv i
127inner join ?.sys.columns c on i.object_id=c.object_id and c.column_id = convert(int, convert(varbinary, SUBSTRING(i.StatsName, 9, 8),2)) and (''['' + i.DatabaseName + '']'') = ''?''
128where i.StatsName like ''_WA_Sys%''
129'
130
131select * , (0.0+maxSteps-minSteps)/maxSteps * 100 as PctChange
132from #inv
133where minSteps <> maxSteps
134and DatabaseName <> 'master'
135AND TableName NOT LIKE 'dm_db_stats%'
136order by PctChange desc