SQL Table Size | SQL RNNR

Today we have another update for an age old script on this site. You can find the last update to the script here.

This time we have a bit of a bug update. The details of that bug are in the notes for the script.

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.

ModifiedDate	ModifiedBy	Description
2013-11-21		JB			Tables without Indexes had a Null Value in the Output
							Fixed the output to sum properly for the NULLS in absence of an index

*/
BEGIN TRAN

DECLARE @dbsize DECIMAL(19,2)
        ,@logsize 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)) / 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 object_id,
                        ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0,
                        UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0,
                        PageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id < 2)
                                        THEN (used_page_count)
                                ELSE lob_used_page_count + row_overflow_used_page_count
                                END
                        )) * 8/1024,
                        iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (used_page_count)
                                ELSE 0
                                END
                        )) * 8/1024,
                        RowCnt = SUM(
                        CASE
                                WHEN (index_id < 2)
                                        THEN row_count
                                ELSE 0
                        END
                        )
                FROM sys.dm_db_partition_stats
                WHERE 1 = 1
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id < 2
                GROUP BY object_id
        ),IndexPass AS (
                SELECT object_id,
                        iReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
                        ,iUsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
                        ,iPageCnt = SUM(
                        CONVERT(DECIMAL(19,2),CASE
                                WHEN (index_id > 1)
                                        THEN (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
                Where 1 = 1 
					--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
					AND index_id > 1
                GROUP BY 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
                                And it.internal_type IN (202,204,211,212,213,214,215,216)
                WHERE it.parent_id = ps.object_id
					--And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0
                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
						,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)))
                        ,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0))
                        ,dbsizeMB = @dbsize
                        ,LogSizeMB = @logsize
                FROM FirstPass F
					INNER JOIN sys.objects o
						ON F.object_id = o.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
                GROUP BY F.object_id,o.schema_id
        ),TotalUnused AS (
				SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace
						,SUM(ISNULL(reservedpageMB,0))+SUM(ISNULL(IndexReservedMB,0)) as Usedr
					FROM Summary
		)
        SELECT ObjName,SchemaName,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) as DataSizeMB, ISNULL(IndexSizeMB,0) as IndexSizeMB
						,ISNULL(S.UnusedSpace,0) as UnusedSpace, dbsizeMB, LogSizeMB
						,TU.UnusedSpace AS TotalTableFreeSpace 
                        ,dbsizeMB - TU.Usedr as DataFileFreeSpace  
                        /*within 1.5gb 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 TotalUnused TU
        ORDER BY PercentofDBUsedSpace DESC

ROLLBACK

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

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.

ModifiedDate ModifiedBy Description

2013-11-21 JB Tables without Indexes had a Null Value in the Output

Fixed the output to sum properly for the NULLS in absence of an index

BEGIN TRAN

DECLARE @dbsize DECIMAL(19,2)

,@logsize 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)) / 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 object_id,

ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0,

UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0,

PageCnt = SUM(

CONVERT(DECIMAL(19,2),CASE

WHEN (index_id < 2)

THEN (used_page_count)

ELSE lob_used_page_count + row_overflow_used_page_count

END

)) * 8/1024,

iPageCnt = SUM(

CONVERT(DECIMAL(19,2),CASE

WHEN (index_id > 1)

THEN (used_page_count)

ELSE 0

END

)) * 8/1024,

RowCnt = SUM(

CASE

WHEN (index_id < 2)

THEN row_count

ELSE 0

END

)

FROM sys.dm_db_partition_stats

WHERE 1 = 1

--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0

AND index_id < 2

GROUP BY object_id

),IndexPass AS (

SELECT object_id,

iReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0

,iUsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0

,iPageCnt = SUM(

CONVERT(DECIMAL(19,2),CASE

WHEN (index_id > 1)

THEN (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

Where 1 = 1

--AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0

AND index_id > 1

GROUP BY 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

And it.internal_type IN (202,204,211,212,213,214,215,216)

WHERE it.parent_id = ps.object_id

--And OBJECTPROPERTY(ps.object_id,'IsMSShipped') = 0

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

,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)))

,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0))

,dbsizeMB = @dbsize

,LogSizeMB = @logsize

FROM FirstPass F

INNER JOIN sys.objects o

ON F.object_id = o.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

GROUP BY F.object_id,o.schema_id

),TotalUnused AS (

SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace

,SUM(ISNULL(reservedpageMB,0))+SUM(ISNULL(IndexReservedMB,0)) as Usedr

FROM Summary

)

SELECT ObjName,SchemaName,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) as DataSizeMB, ISNULL(IndexSizeMB,0) as IndexSizeMB

,ISNULL(S.UnusedSpace,0) as UnusedSpace, dbsizeMB, LogSizeMB

,TU.UnusedSpace AS TotalTableFreeSpace

,dbsizeMB - TU.Usedr as DataFileFreeSpace

/*within 1.5gb 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 TotalUnused TU

ORDER BY PercentofDBUsedSpace DESC

ROLLBACK

Alternatively, you can download the script from here (in case a plugin update breaks the script again).

2 thoughts on “Table Space updated again”

Alexander says:

September 15, 2014 at 12:52

READ UNCOMMITTED and BEGIN TRAN ??

1. Jason Brimhall says:
  
  September 15, 2014 at 13:25
  
  Yes. You can remove the begin tran and rollback if you like. The read uncommitted shouldn’t be an issue here because it is against metadata and does provide some benefit.

2 thoughts on “Table Space updated again”

Leave a Reply Cancel reply