Table Space Script

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”]

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

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