As was recently promised, I have an update to an old tried and true script for calculating the size of an object.
That promise was doled out in the previous article when I discussed the internal table types. You can read the article here. Information from that article was used in this script – so it could be of use prior to taking a gander at this new revision.
And of course, previous releases can be found through the link chain here.
The script was in need of an update the last time around due to the internal table types that had been updated in SQL Server. Those internal tables haven’t really changed in the latest release (at least as far as calculating space is concerned). But, I didn’t like needing to update multiple places in the script for one. For the other part of it, I did not like that It was difficult to know what exactly the internal table numbers actually represented. So I added that documentation to this script.
In addition to that little change, I also wanted to figure out if a table might have been spread across multiple filegroups. Due to this, I added in a delimited list of filegroups.
And the last minor update is that the script now has an option to exclude MS Shipped objects, or to include them, or to look just at the MS Shipped objects.
All of this is included in the effort of trying to get a more complete view of the size of the objects within a database and to be able to do so without needing to loop through the objects.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; /* Purpose: To Evaluate table sizes combined with index space consumption to determine higher cost tables in terms of storage, resources and maintenance needs. ModifiedDateModifiedByDescription 2013-11-21JBTables without Indexes had a Null Value in the Output Fixed the output to sum properly for the NULLS in absence of an index 2015-04-17JBAdded Filegroup information Updated calcs - little more accuracy Updated internal tables exclusions 2015-07-24JBAdded Server Version Check and Internal Tables to pull from temp table Added MSShipped Logic */SET NOCOUNT ON; DECLARE @dbsize DECIMAL(19,2) ,@logsize DECIMAL(19,2) ,@IsMSShipped TINYINT = 0 /*NULL = all objects 1 = MS Shipped Objects 0 = User Created Objects */; --DECLARE @FileGroup VARCHAR(128) = NULL; --Null for all filegroups --contributes TO SPACE IN what versions OF SQL --2012(202,204,207,211,212,213,214,215,216,221,222,236) --2008(202,204,211,212,213,214,215,216) --2008R2(202,204,211,212,213,214,215,216) --2014(202,204,207,211,212,213,214,215,216,221,222,236) --2005(202,204) --2016(202,204,207,211,212,213,214,215,216,221,222,236) DECLARE @ServerMajorVersion DECIMAL(4, 2) SELECT @ServerMajorVersion = CONVERT(DECIMAL(4, 2), PARSENAME(dt.fqn, 4) + '.' + PARSENAME(dt.fqn, 3)) FROM ( SELECT CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion')) ) dt ( fqn ); IF OBJECT_ID('tempdb.dbo.#InternalTables') IS NOT NULL BEGIN DROP TABLE #InternalTables; END CREATE TABLE #InternalTables ( [internal_type] [TINYINT] NULL --INDEX CI_InternalType CLUSTERED , [internal_type_desc] [VARCHAR](60) NULL , [DBSource] [VARCHAR](16) NULL ) ON[PRIMARY]; CREATE CLUSTERED INDEX CI_InternalType ON #InternalTables (internal_type); INSERT INTO #InternalTables ( [internal_type], [internal_type_desc], [DBSource] ) VALUES( 201, N'QUEUE_MESSAGES', N'system database' ) ,( 202, N'XML_INDEX_NODES', N'user database' ) ,( 203, N'FULLTEXT_CATALOG_FREELIST', N'User Database' ) ,( 204, N'FULLTEXT_CATALOG_MAP (BOL)/FULLTEXT_INDEX_MAP (REALITY)', N'User Database' ) ,( 205, N'QUERY_NOTIFICATION', N'User Database' ) ,( 206, N'SERVICE_BROKER_MAP', N'system database' ) ,( 207, N'EXTENDED_INDEXES', N'user database' ) ,( 208, N'FILESTREAM_TOMBSTONE', N'system database' ) ,( 209, N'CHANGE_TRACKING', N'User Database' ) ,( 210, N'TRACKED_COMMITTED_TRANSACTIONS', N'system database' ) ,( 211, N'FULLTEXT_AVDL', N'user database' ) ,( 212, N'FULLTEXT_COMP_FRAGMENT', N'user database' ) ,( 213, N'FULLTEXT_DOCID_STATUS', N'user database' ) ,( 214, N'FULLTEXT_INDEXED_DOCID', N'user database' ) ,( 215, N'FULLTEXT_DOCID_FILTER', N'user database' ) ,( 216, N'FULLTEXT_DOCID_MAP', N'user database' ) ,( 217, N'FULLTEXT_THESAURUS_METADATA_TABLE', N'system database' ) ,( 218, N'FULLTEXT_THESAURUS_STATE_TABLE', N'system database' ) ,( 219, N'FULLTEXT_THESAURUS_PHRASE_TABLE', N'system database' ) ,( 220, N'CONTAINED_FEATURES', N'system database' ) ,( 221, N'SEMPLAT_DOCUMENT_INDEX_TABLE', N'user database' ) ,( 222, N'SEMPLAT_TAG_INDEX_TABLE', N'user database' ) ,( 223, N'SEMPLAT_MODEL_MAPPING_TABLE', N'system database' ) ,( 224, N'SEMPLAT_LANGUAGE_MODEL_TABLE', N'system database' ) ,( 225, N'FILETABLE_UPDATES', N'system database' ) ,( 236, N'SELECTIVE_XML_INDEX_NODE_TABLE', N'user database' ) ,( 240, N'QUERY_DISK_STORE_QUERY_TEXT', N'system database' ) ,( 241, N'QUERY_DISK_STORE_QUERY', N'system database' ) ,( 242, N'QUERY_DISK_STORE_PLAN', N'system database' ) ,( 243, N'QUERY_DISK_STORE_RUNTIME_STATS', N'system database' ) ,( 244, N'QUERY_DISK_STORE_RUNTIME_STATS_INTERVAL', N'system database' ) ,( 245, N'QUERY_CONTEXT_SETTINGS', N'system database' ); IF OBJECT_ID('tempdb.dbo.#SpaceVersions') IS NOT NULL BEGIN DROP TABLE #SpaceVersions; END CREATE TABLE #SpaceVersions ( Product VARCHAR(32) , ServerMajorVersion DECIMAL(4, 2) --INDEX CI_ServerMajorVer CLUSTERED , TypesList VARCHAR(256) ); CREATE CLUSTERED INDEX CI_ServerMajorVer ON#SpaceVersions (ServerMajorVersion); INSERT INTO#SpaceVersions ( Product , ServerMajorVersion , TypesList ) VALUES ( 'SQL Server 2005', 9.00 , '202,204') ,( 'SQL Server 2008', 10.00 , '202,204,211,212,213,214,215,216') ,( 'SQL Server 2008R2', 10.50 , '202,204,211,212,213,214,215,216') ,( 'SQL Server 2012', 11.00 , '202,204,207,211,212,213,214,215,216,221,222,236') ,( 'SQL Server 2014', 12.00 , '202,204,207,211,212,213,214,215,216,221,222,236') ,( 'SQL Server 2016', 13.00 , '202,204,207,211,212,213,214,215,216,221,222,236'); IF OBJECT_ID('tempdb.dbo.#PreselTypes') IS NOT NULL BEGIN DROP TABLE #PreselTypes; END SELECT sv.Product, myit.internal_type, myit.internal_type_desc INTO #PreselTypes FROM #SpaceVersions sv CROSS APPLY AdminDB.dbo.stringsplitter(sv.TypesList,',') ss /* must have DelimitedSplit8K installed http://bit.ly/Moden8KDL *//* Change database name in accordance with 8k splitter location */INNER JOIN #InternalTables myit ON ss.item = myit.internal_type WHERE sv.ServerMajorVersion = @ServerMajorVersion; /* ** Summary data. */BEGIN SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN size ELSE 0 END)) / 128.0 , @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN size ELSE 0 END)) / 128.0 FROM sys.database_files END; WITH FirstPass AS ( SELECT p.object_id, p.partition_id, ReservedPage = CONVERT(DECIMAL(19,2),SUM(a.total_pages)) / 128.0, UsedPage = CONVERT(DECIMAL(19,2),SUM(a.used_pages)) / 128.0, PageCnt = SUM( CONVERT(DECIMAL(19,2),CASE WHEN ISNULL(svj.internal_type,1) <> 1 THEN 0 WHEN (a.type <> 1 AND p.index_id < 2) THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 --lob_used_page_count + row_overflow_used_page_count END )) / 128.0 ,RowCnt = SUM( CASE WHEN (p.index_id < 2) THEN ps.row_count ELSE 0 END ) FROM sys.allocation_units a INNER JOIN sys.partitions p ON a.container_id = p.partition_id INNER JOIN sys.dm_db_partition_stats ps ON p.object_id = ps.object_id AND p.index_id = ps.index_id LEFT OUTER JOIN sys.internal_tables it ON p.object_id = it.object_id LEFT OUTER JOIN #PreselTypes svj ON it.internal_type = svj.internal_type WHERE 1 = 1 AND OBJECTPROPERTY(p.object_id,'IsMSShipped') = ISNULL(@IsMSShipped,OBJECTPROPERTY(p.object_id,'IsMSShipped')) AND p.index_id IN (0,1,255) GROUP BY p.object_id, p.partition_id ), FileGroupNames AS ( SELECT (STUFF( (SELECT DISTINCT ', <' + FILEGROUP_NAME(au.data_space_id) + '>' FROM sys.allocation_units au INNER JOIN sys.partitions pu ON au.container_id = pu.partition_id WHERE pu.object_id = a.object_id FOR XML PATH(''), ROOT('FileGroupNames'), TYPE ).value('/FileGroupNames[1]','varchar(max)') , 1, 2, '')) AS FGList, a.object_id FROM FirstPass a ),IndexPass AS ( SELECT ps.object_id, iReservedPage = CONVERT(DECIMAL(19,2),SUM(CASE WHEN (index_id NOT IN (0,1,255)) THEN (ps.reserved_page_count) ELSE 0 END)) / 128.0 ,iUsedPage = CONVERT(DECIMAL(19,2),SUM(CASE WHEN (index_id NOT IN (0,1,255)) THEN ps.used_page_count ELSE 0 END)) / 128.0 ,iPageCnt = SUM( CONVERT(DECIMAL(19,2),CASE WHEN (index_id NOT IN (0,1,255)) THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count ELSE 0 END )) / 128.0 ,RowCnt = SUM( CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) FROM sys.dm_db_partition_stats ps WHERE 1 = 1 AND OBJECTPROPERTY(ps.object_id,'IsMSShipped') = ISNULL(@IsMSShipped,OBJECTPROPERTY(ps.object_id,'IsMSShipped')) --AND index_id NOT IN (0,1,255) GROUP BY ps.object_id ),InternalTables AS ( SELECT ps.object_id, ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0 ,UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0 FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON it.object_id = ps.object_id INNER JOIN #PreselTypes svj2 ON it.internal_type = svj2.internal_type WHERE it.parent_id = ps.object_id AND OBJECTPROPERTY(ps.object_id,'IsMSShipped') = ISNULL(@IsMSShipped,OBJECTPROPERTY(ps.object_id,'IsMSShipped')) GROUP BY ps.object_id ),Summary AS ( SELECT OBJECT_NAME (F.object_id) AS ObjName ,SCHEMA_NAME(o.schema_id) AS SchemaName ,CASE WHEN OBJECTPROPERTY(F.object_id,'IsMSShipped') = 1 THEN 'YES' ELSE 'NO' END AS IsMsShipped ,NumRows = MAX(F.RowCnt) ,ReservedPageMB = SUM(ISNULL(F.ReservedPage,0) + ISNULL(i.ReservedPage,0)) ,DataSizeMB = SUM(F.PageCnt) --,IndexSizeMB = SUM(CASE WHEN (F.UsedPage + ISNULL(i.UsedPage,0)) > F.PageCnt -- THEN ((F.UsedPage + ISNULL(i.UsedPage,0)) - F.PageCnt) ELSE 0 END) -- Equivalent of max_record_size from sys.dm_db_index_physical_stats /* this seems more accurate */,IndexSizeMB = SUM(ISNULL(ip.iPageCnt,0)) ,UnusedSpace = SUM(CASE WHEN (F.ReservedPage + ISNULL(i.ReservedPage,0)) > (F.UsedPage + ISNULL(i.UsedPage,0)) THEN ((F.ReservedPage + ISNULL(i.ReservedPage,0)) - (F.UsedPage + ISNULL(i.UsedPage,0))) ELSE 0 END) + (SUM(ISNULL(ip.iReservedPage,0)) - SUM(ISNULL(ip.iUsedPage,0)))/128 ,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0)) ,dbsizeMB = @dbsize ,LogSizeMB = @logsize --,FileGroupName = MAX(F.FileGroupName) ,FGList = MAX(fg.FGList) FROM FirstPass F INNER JOIN sys.objects o ON F.object_id = o.object_id INNER JOIN FileGroupNames fg ON fg.object_id = F.object_id LEFT OUTER JOIN InternalTables i ON i.object_id = F.OBJECT_ID LEFT OUTER JOIN IndexPass ip ON F.OBJECT_ID = ip.OBJECT_ID --WHERE F.FileGroupName = ISNULL(@FileGroup,F.FileGroupName) GROUP BY F.object_id,o.schema_id ),TotalUnallocated AS ( SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace ,SUM(ISNULL(Summary.ReservedPageMB,0))+SUM(ISNULL(Summary.IndexReservedMB,0)) AS Usedr FROM Summary ) SELECT ObjName,SchemaName --, S.FileGroupName ,S.FGList ,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) AS DataSizeMB, ISNULL(IndexSizeMB,0) AS IndexSizeMB ,ISNULL(S.UnusedSpace,0) AS UnusedTableSpace, dbsizeMB, LogSizeMB ,TU.UnusedSpace AS TotalTableFreeSpace ,dbsizeMB - TU.Usedr AS DataFileFreeSpace /*within 1.5mb on a 1.76tb database or .000085% variance or 99.999915% accuracy */ ,PercentofDBPhysFile = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / @dbsize) * 100 ,PercentofDBUsedSpace = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / (@dbsize - TU.UnusedSpace)) * 100 FROM Summary S CROSS APPLY TotalUnallocated TU ORDER BY PercentofDBUsedSpace DESC;
Note: A quick update to move the index creation outside of the table creation instead of inline since that is a feature not supported in older versions of SQL Server.