When I need to get a detailed overview of my database objects there are a number of scripts that I have written or adapted with varying degrees of depth. Primarily I wrote these scripts as a way to determine rough rowcounts,database and data/index sizes, but as I went I added more and more bits of information.
So if I start off with where this all began… Trying to determine size / storage information. But before that… I better clarify my use of unit notation, which no doubt some people will find annoying. hehe
Units
When using units to denote binary size, most of the scripts I use will follow the standard which differentiates them from their decimal sibling by using an “i”.
So megabyte MB is shown as MiB when concerning binary sizes.
http://en.wikipedia.org/wiki/Binary_prefix
File System level
The easiest way to determine the size of a database is to look at the file system. Simply browse to your data directory which will look something like:
C:\Program Files\Microsoft SQL Server\<MSSQL10.MSSQLSERVER>\MSSQL\DATA
If you then add up the size information of the .mdf (data files) as well .ldf (log files) you will get the allocated size on disk of your database. Note that additional data files are usually suffixed with .ndf though you may find different naming conventions and oddities.
A default database called Test for example would show as Test.mdf 3072 KiB and Test_log.ldf 1024 KiB, so the database is 4096 KiB or 4 MiB.
SQL Server – Allocated File Sizes
If we check this via SSMS, Properties, Files we see that the data file is 3 MiB and the log file is 1 MiB so that ties in nicely to what we would have also seen at the file system level.
Here is a script to check file allocations of a database called Test:
SELECT DB_NAME(mf.[database_id]) [DatabaseName] ,CONVERT(DECIMAL(15,3),(SUM(mf.[size])*CONVERT(FLOAT,8)/1024)) [Size_MiB] ,mf.[type_desc] [FileType] FROM sys.master_files mf WHERE DB_NAME(mf.[database_id]) = 'Test' GROUP BY DB_NAME(mf.[database_id]),mf.[type_desc] ORDER BY DB_NAME(mf.[database_id])
Remove the WHERE clause from the above statement and we can get the database sizes for all the databases on the instance.
Here is a more detailed version:
SELECT
DB_NAME(mf.[database_id]) [DatabaseName]
,mf.[size] * CONVERT(FLOAT,8) [Size_KiB]
,CONVERT(DECIMAL(15,3),(mf.[size]*CONVERT(FLOAT,8)/1024)) [Size_MiB]
,CONVERT(DECIMAL (15,3),(mf.[size]*CONVERT(FLOAT,8))/1048576) [Size_GiB]
,mf.[size] * CONVERT(FLOAT,8192) [Size_bytes]
,LTRIM(CASE mf.[is_percent_growth] WHEN 1 THEN STR(mf.[growth]) +' %' ELSE STR(mf.[growth]*CONVERT(FLOAT,8)/1024)+' MiB' END) [AutoGrowth]
,CASE WHEN mf.[max_size]= -1 THEN -1 ELSE mf.[max_size] * CONVERT(FLOAT,8) END [MaxSize]
,mf.[type_desc] [FileType]
,CAST(CASE mf.[state] WHEN 6 THEN 1 ELSE 0 END AS BIT) [IsOffline]
,mf.[is_read_only] [IsReadOnly]
,mf.[name] [LogicalName]
,mf.[file_id] [FileID]
,RIGHT(mf.[physical_name],CHARINDEX('\',REVERSE (mf.[physical_name]))-1) [FileName]
,mf.[physical_name] [Path]
FROM sys.master_files mf
--WHERE DB_NAME(mf.[database_id]) = DB_NAME()
ORDER BY DB_NAME(mf.[database_id]),mf.[file_id]
SQL Server – Size Usage Detail
There is however one slight caveat with the above scripts, which is that there is no information relating to how the database is using that space. This is because this information is stored in each of the databases, so we need to query them individually.
If we had looked at SSMS, Properties, General we would have seen Size and Space Available.
Or we can reproduce via:
SELECT DB_NAME() [DatabaseName] ,CONVERT(DECIMAL (15,2),(SUM(sf.[size])*CONVERT(FLOAT,8)/1024)) [Size_MiB] ,CONVERT(DECIMAL (15,2), (SUM(CASE WHEN (sf.[status] & 64 = 0) THEN sf.[size] ELSE 0 END) - ( SELECT SUM(au.[total_pages]) FROM sys.partitions p INNER JOIN sys.allocation_units au ON p.[partition_id] = au.[container_id] LEFT JOIN sys.internal_tables it on p.[object_id] = it.[object_id]) )*CONVERT(FLOAT,8)/1024 ) [AvailableSpace_MiB] FROM sys.sysfiles sf
Although the above script usually works ok, I tend instead to use this next script which again is more detailed.
SELECT
DB_NAME() [DatabaseName]
,fg.[groupname] [FileGroupName]
,CONVERT(DECIMAL(15,3),(FILEPROPERTY(sf.[name],'SpaceUsed')*CONVERT(FLOAT,8)/1024)) [SpaceUsed_MiB]
,CONVERT(DECIMAL(15,3),((sf.[size]-FILEPROPERTY(sf.[name],'SpaceUsed'))*CONVERT(FLOAT,8)/1024)) [AvailableSpace_MiB]
,mf.[size] * CONVERT(FLOAT,8) [Size_KiB]
,CONVERT(DECIMAL(15,3),(sf.[size] * CONVERT(FLOAT,8)/1024)) [Size_MiB]
,CONVERT(DECIMAL (15,3),(sf.[size] * CONVERT(FLOAT,8))/1048576) [Size_GiB]
,mf.[size] * CONVERT(FLOAT,8192) [Size_bytes]
,LTRIM(CASE mf.[is_percent_growth] WHEN 1 THEN STR(mf.[growth]) +' %' ELSE STR(mf.[growth]*CONVERT(FLOAT,8)/1024)+' MiB' END) [AutoGrowth]
,CASE WHEN mf.[max_size]=-1 THEN -1 ELSE mf.[max_size] * CONVERT(FLOAT,8) END [MaxSize]
,mf.[type_desc] [FileType]
,CAST(CASE mf.[state] WHEN 6 THEN 1 ELSE 0 END AS BIT) [IsOffline]
,mf.[is_read_only] [IsReadOnly]
,sf.[name] [LogicalName]
,mf.[file_id] [FileID]
,RIGHT(mf.[physical_name],CHARINDEX('\',REVERSE (mf.[physical_name]))-1) [FileName]
,sf.[filename] [Path]
FROM sys.master_files mf
INNER JOIN sys.sysfiles sf ON mf.[file_id] = sf.[fileid] AND mf.[database_id] = DB_ID()
LEFT JOIN sys.sysfilegroups fg ON sf.[groupid] = fg.[groupid]
ORDER BY mf.[file_id]
My final script incorporates row counts, page count, partitioning, index and allocation break down.
After reading a really interesting article by Kimberly Tripp on the tipping point of an index I decided to incorporate that into the final script as well. Basically the article talks about when an index is no longer selective enough, and therefore is not used.
--http://jongurgul.com/blog/detailed-table-index-breakdown/ SELECT DB_NAME() [DatabaseName] ,ao.[object_id] [ObjectID] ,SCHEMA_NAME(ao.[schema_id]) [SchemaName] ,ao.[name] [ObjectName] ,ao.[is_ms_shipped] [IsSystemObject] ,i.[index_id] [IndexID] ,i.[name] [IndexName] ,i.[type_desc] [IndexType] ,au.[type_desc] [AllocationUnitType] ,p.[partition_number] [PartitionNumber] ,ds.[type] [IsPartition] --,p.[data_compression_desc] [Compression] ,ds.[name] [PartitionName] ,fg.[name] [FileGroupName] ,p.[rows] [NumberOfRows] ,CASE WHEN pf.[boundary_value_on_right] = 1 AND ds.[type] = 'PS' THEN 'RIGHT' WHEN pf.[boundary_value_on_right] IS NULL AND ds.[type] = 'PS' THEN 'LEFT' ELSE NULL END [Range] ,prv.[value] [LowerBoundaryValue] ,prv2.[value] [UpperBoundaryValue] ,CONVERT(DECIMAL (15,3),(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND p.[rows] >0 THEN p.[rows]/NULLIF(au.[data_pages],0) ELSE 0 END)) [RowsPerPage] ,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.20 ELSE NULL END) [TippingPointLower_Rows] ,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.30 ELSE NULL END) [TippingPointUpper_Rows] ,au.[used_pages][UsedPages] ,CONVERT(DECIMAL (15,3),(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END)*CONVERT(FLOAT,8)/1024) [DataUsedSpace_MiB] ,CONVERT(DECIMAL (15,3),(au.[used_pages]-(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END))*CONVERT(FLOAT,8)/1024) [IndexUsedSpace_MiB] ,au.[data_pages] [DataPages] FROM sys.partition_functions pf INNER JOIN sys.partition_schemes ps ON pf.[function_id] = ps.[function_id] RIGHT OUTER JOIN sys.partitions p INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id] INNER JOIN sys.allocation_units au ON au.[container_id] = p.[partition_id] INNER JOIN sys.filegroups fg ON au.[data_space_id] = fg.[data_space_id] INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id] INNER JOIN sys.all_objects ao ON i.[object_id] = ao.[object_id] ON ps.[data_space_id] = ds.[data_space_id] LEFT OUTER JOIN sys.partition_range_values prv ON ps.[function_id] = prv.[function_id] AND p.[partition_number] - 1 = prv.[boundary_id] LEFT OUTER JOIN sys.partition_range_values prv2 ON ps.[function_id] = prv2.[function_id] AND prv2.[boundary_id] = p.[partition_number] WHERE ao.[is_ms_shipped] = 0 AND au.[type_desc] = 'IN_ROW_DATA' --AND SCHEMA_NAME(ao.[schema_id]) ='dbo' --AND ao.[name] LIKE '%%' ORDER BY SCHEMA_NAME(ao.[schema_id]),ao.[name]
http://gallery.technet.microsoft.com/scriptcenter/Detailed-Table-and-Index-863aa291
Leave a Reply