I am bringing an oldie back with another twist. I recently ran into the need to correlate information between a couple of different queries that I like to use. The two scripts returned different pieces of data about tables in a database. This information was table size and missing foreign key indexes.
I needed to combine the two queries due to a desire to create indexes on foreign keys based on table size. The premise behind this was to get the biggest bang for the buck initially as we work toward optimizing a database. We happened to know heading into this that some of the larger tables are the most heavily queried tables as well.
So, here is what I did to get that information quickly.
[codesyntax lang=”tsql”]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 |
/* Part I */ --Drop Table #indstats IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%') BEGIN DROP TABLE tempdb.dbo.#indstats END BEGIN CREATE TABLE #indstats ( IndStatsID INT PRIMARY KEY CLUSTERED ,database_id BIGINT ,index_id BIGINT ,IndexSizeMB DECIMAL(16,1) ,OBJECT_ID BIGINT ); END INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB) SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID ,database_id,index_id,OBJECT_ID ,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'SAMPLED') ps GROUP BY database_id,OBJECT_ID,index_id; /* Part II */ DECLARE @dbsize DECIMAL(19,2) SET NOCOUNT ON /* ** Summary data. */ BEGIN SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024 FROM sys.database_files END /* Part III */ BEGIN WITH RegData AS ( SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id ,FileGroupName = FILEGROUP_NAME(a.data_space_id) ,TableName = OBJECT_NAME(p.OBJECT_ID) ,NumRows = p.ROWS ,UsedPages = IsNull(a.used_pages,0) ,TotalPages = IsNull(a.total_pages,0) ,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024 ,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END ,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) ,UserUpdates = IsNull(us.user_updates,0) ,LastUpdate = IsNull(us.last_user_update,null) ,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL) / CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) FROM sys.allocation_units a INNER Join sys.partitions p ON p.hobt_id = a.container_id And a.type = 1 LEFT Outer Join sys.dm_db_index_usage_stats us ON us.OBJECT_ID = p.OBJECT_ID And us.index_id = p.index_id And us.database_id = DB_ID() LEFT Outer Join #indstats ps ON p.index_id = ps.index_id And ps.database_id = DB_ID() And p.OBJECT_ID = ps.OBJECT_ID --WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0 ) , LOBData AS ( SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id ,FileGroupName = FILEGROUP_NAME(a.data_space_id) ,TableName = OBJECT_NAME(p.OBJECT_ID) ,NumRows = p.ROWS ,UsedPages = IsNull(a.used_pages,0) ,TotalPages = IsNull(a.total_pages,0) ,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024 ,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END ,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) ,UserUpdates = IsNull(us.user_updates,0) ,LastUpdate = IsNull(us.last_user_update,null) ,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL) / CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) FROM sys.allocation_units a INNER Join sys.partitions p ON p.partition_id = a.container_id And a.type = 2 LEFT Outer Join sys.dm_db_index_usage_stats us ON us.OBJECT_ID = p.OBJECT_ID And us.index_id = p.index_id And us.database_id = DB_ID() LEFT Outer Join #indstats ps ON p.index_id = ps.index_id And ps.database_id = DB_ID() And p.OBJECT_ID = ps.OBJECT_ID --WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0 ) , OverFlowData AS ( SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id ,FileGroupName = FILEGROUP_NAME(a.data_space_id) ,TableName = OBJECT_NAME(p.OBJECT_ID) ,NumRows = p.ROWS ,UsedPages = IsNull(a.used_pages,0) ,TotalPages = IsNull(a.total_pages,0) ,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024 ,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END ,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) ,UserUpdates = IsNull(us.user_updates,0) ,LastUpdate = IsNull(us.last_user_update,null) ,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL) / CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) FROM sys.allocation_units a INNER Join sys.partitions p ON p.hobt_id = a.container_id And a.type = 3 LEFT Outer Join sys.dm_db_index_usage_stats us ON us.OBJECT_ID = p.OBJECT_ID And us.index_id = p.index_id And us.database_id = DB_ID() LEFT Outer Join #indstats ps ON p.index_id = ps.index_id And ps.database_id = DB_ID() And p.OBJECT_ID = ps.OBJECT_ID --WHERE OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0 ), IndexSum AS ( SELECT a.OBJECT_ID ,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1) THEN IsNull(a.DataSizeMB,0) + IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0) ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0) END) FROM RegData a LEFT Outer Join LOBData p2 ON p2.container_id = a.container_id LEFT Outer Join OverFlowData p3 ON p3.container_id = a.container_id GROUP BY a.OBJECT_ID ), SummaryInfo AS ( SELECT TableName = MAX(a.TableName) ,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0)) ,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0)) ,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0)) ,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0)) ,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)) ,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0)) ,FreeDataSpace = CONVERT(DECIMAL(19,2), SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0)) - SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024 ,AllDataSizeMB = MAX(ids.AllDataSizeMB) ,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0)) + SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0)) ,UserRequests = AVG(IsNull(a.UserRequests,0) + IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0)) ,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0)) ,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null)) ,DatabaseSize = @dbsize FROM RegData a LEFT Outer Join LOBData p2 ON p2.container_id = a.container_id LEFT Outer Join OverFlowData p3 ON p3.container_id = a.container_id LEFT Outer Join sys.indexes i ON i.OBJECT_ID = a.OBJECT_ID And i.index_id = a.index_id LEFT Outer Join IndexSum ids ON i.OBJECT_ID = ids.OBJECT_ID GROUP BY a.OBJECT_ID ), TotalUnused AS ( SELECT SUM(FreeDataSpace) AS UnusedSpace FROM SummaryInfo ) SELECT TableName,ChildTableColumn as MissingFKIndexColumn,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB ,AllUsedPages,AllPages ,FreeDataSpace,AllDataSizeMB,IndexSizeMB ,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace ,UserRequests,UserUpdates,LastUpdate ,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100 ,DatabaseSize ,DataUsedSize = DatabaseSize - TU.UnusedSpace ,PercentofDataFileUsed = ((IndexSizeMB + AllDataSizeMB) / (DatabaseSize - TU.UnusedSpace)) * 100 FROM SummaryInfo SI CROSS APPLY TotalUnused TU Cross Apply (SELECT OBJECT_NAME(FK.parent_object_id) AS FKtable ,OBJECT_NAME(Fk.referenced_object_id) AS ReferencesThisTable ,OBJECT_NAME(FK.constraint_object_id) AS FKName ,C.name ChildTableColumn FROM sys.foreign_key_columns FK INNER JOIN sys.columns C ON FK.parent_object_id = C.object_id AND FK.parent_column_id = C.column_id INNER JOIN sys.objects O On FK.parent_object_id = o.object_id LEFT OUTER JOIN sys.index_columns ic ON FK.parent_object_id = ic.object_id AND FK.parent_column_id = ic.column_id WHERE ic.object_id IS NULL) FK Where Fk.fktable = SI.TableName ORDER BY PercentofDB DESC END |
[/codesyntax]
The change is not overly much. I added a subquery via a cross apply to get the missing foreign key info. Then I return the pertinent columns back to the Select query.