Another script, another day. And as promised, I am providing an update to the Table Space script that followed the sp_MStableSpace script. Not a lot more to be said about this one, since much was said already.
I updated the script for those CS collations. I also provided the update to show the % of DB column to be driven based on the data file usage information.
[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 |
/* 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,'DETAILED') 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,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 ORDER BY PercentofDB DESC END |
[/codesyntax]
Phew, I finally took care of some of those somedays that have been nagging me. Sure, there has been a someday that has evolved due to that – but that is a good thing.
It helps that I also need these scripts to be CS. Add to that, that I need to use them more frequently and it was a perfect opportunity to do a little housecleaning.