TableSpace Update

The last post in the series on finding the sizes of your tables showed us how we could find that size via a set-based method similar to using sp_MStablespace. In that post I showed you how to find the sizes using the SQL 2005 system objects rather than the scheduled to be deprecated objects. You can read more about it here.

I needed to go back and revisit that post and perform a little spring cleaning on it. I noticed after publication that the script had multiple columns by the same name that were unintended. I discovered this as I was prepping and reviewing for my presentation at our SSSOLV (PASS UG) meeting last week. During that review I came across another bug. The script was not consistently returning the index size for the tables. After reviewing the script more closely, against more databases, I found the problem and fixed it. The change was rather simple – I changed the Join from the #indstats table to go directly to sys.partitions rather than through sys.dm_db_index_usage_stats. I was able to spot this one due to running the query against one of my databases that is seldom used and thus the indexes were less likely to have statistics populated in that DMV.

So, here is the updated query. Rather than breaking out into sections like I did in that last article, I am just pasting the script in its entirety.

[codesyntax lang=”tsql”]

/* 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 &lt; 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 &lt; 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 &lt; 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
		--WHERE filegroup_name(a.data_space_id) = 'Primary'
		Group by a.object_id
	)
	Select TableName,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
			,AllUsedPages,AllPages
			,FreeDataSpace,AllDataSizeMB,IndexSizeMB
			,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
			,UserRequests,UserUpdates,LastUpdate
			,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
			,DatabaseSize
		From SummaryInfo
		Order By PercentofDB desc
End

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

/* 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

--WHERE filegroup_name(a.data_space_id) = 'Primary'

Group by a.object_id

)

Select TableName,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB

,AllUsedPages,AllPages

,FreeDataSpace,AllDataSizeMB,IndexSizeMB

,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace

,UserRequests,UserUpdates,LastUpdate

,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100

,DatabaseSize

From SummaryInfo

Order By PercentofDB desc

End

[/codesyntax]

At some point in the future, I intend on modifying this query to make it more flexible in output, as well as to make it into a stored procedure.

10 thoughts on “TableSpace Update”

Michael J Swart says:

May 20, 2010 at 05:56

Does sys.dm_db_partition_stats return a lot of this info? Is the data from that view ever inconsistent with the data from your script? Just curious

1. Jason Brimhall says:
  
  May 20, 2010 at 09:17
  
  Yes, the information is available there and is coming up in the next article that discusses my version of sp_spaceused.
  
Dugi says:

May 20, 2010 at 11:10

Jason,

I guess that SSOLV members enjoy your nice presentation, thanks for sharing and explanation!

1. Jason Brimhall says:
  
  May 20, 2010 at 11:13
  
  They did enjoy it. There will be a few follow-ups that will basically show the rest of the presentation.
  
Pingback: Space Used | SQL RNNR
Ninja says:

August 1, 2011 at 10:44

Just a small hint, I’ve noticed the {529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170} of DB is based on the data file size rather than the used space of the DB. So my total used space is like 56{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170} rather than 100{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}. Maybe a new column is in order…

1. Jason Brimhall says:
  
  August 1, 2011 at 12:26
  
  That is a good point. I believe I need to enhance it for updating rows – possibly
  
Ninja says:

August 3, 2011 at 07:48

Any way to remove the system tables from the list? Not sure it’s a usefull default :-P.

Ninja says:

August 3, 2011 at 09:12

Makes sense, I also saw the commented filters, so it’s easy enough to correct.

Jason Brimhall says:

August 3, 2011 at 08:34

Remi,
I thought about removing the system tables but decided to leave them. When I removed them I saw a difference in size of the total database size and wanted to make sure the size this script reported was consistent with the size in SSMS.

10 thoughts on “TableSpace Update”

Leave a Reply Cancel reply