Table Size meets Missing FK Indexes

I am bringing an oldie back with another twist. I recently ran into the need to correlate information between a couple of different queries that I like to use. The two scripts returned different pieces of data about tables in a database. This information was table size and missing foreign key indexes.

I needed to combine the two queries due to a desire to create indexes on foreign keys based on table size. The premise behind this was to get the biggest bang for the buck initially as we work toward optimizing a database. We happened to know heading into this that some of the larger tables are the most heavily queried tables as well.

So, here is what I did to get that information quickly.

[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,'SAMPLED') 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,ChildTableColumn as MissingFKIndexColumn,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
			Cross Apply (SELECT
								OBJECT_NAME(FK.parent_object_id) AS FKtable
								,OBJECT_NAME(Fk.referenced_object_id) AS ReferencesThisTable
								,OBJECT_NAME(FK.constraint_object_id) AS FKName
								,C.name ChildTableColumn
							FROM sys.foreign_key_columns FK
								INNER JOIN sys.columns C
									ON FK.parent_object_id = C.object_id 
									AND FK.parent_column_id = C.column_id
								INNER JOIN sys.objects O
									On FK.parent_object_id = o.object_id
								LEFT OUTER JOIN sys.index_columns ic
									ON FK.parent_object_id = ic.object_id
									AND FK.parent_column_id = ic.column_id 
							WHERE ic.object_id IS NULL) FK
		Where Fk.fktable = SI.TableName
		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

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

/* 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,'SAMPLED') 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

GROUP BY a.OBJECT_ID

), TotalUnused AS (

SELECT SUM(FreeDataSpace) AS UnusedSpace

FROM SummaryInfo

)

SELECT TableName,ChildTableColumn as MissingFKIndexColumn,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

Cross Apply (SELECT

OBJECT_NAME(FK.parent_object_id) AS FKtable

,OBJECT_NAME(Fk.referenced_object_id) AS ReferencesThisTable

,OBJECT_NAME(FK.constraint_object_id) AS FKName

,C.name ChildTableColumn

FROM sys.foreign_key_columns FK

INNER JOIN sys.columns C

ON FK.parent_object_id = C.object_id

AND FK.parent_column_id = C.column_id

INNER JOIN sys.objects O

On FK.parent_object_id = o.object_id

LEFT OUTER JOIN sys.index_columns ic

ON FK.parent_object_id = ic.object_id

AND FK.parent_column_id = ic.column_id

WHERE ic.object_id IS NULL) FK

Where Fk.fktable = SI.TableName

ORDER BY PercentofDB DESC

END

[/codesyntax]

The change is not overly much. I added a subquery via a cross apply to get the missing foreign key info. Then I return the pertinent columns back to the Select query.

Events By Feature August 30, 2016
Mass Backup All Sessions May 23, 2019
Common Tempdb Trace Flags - Back to Basics January 5, 2018
SSRS Subscription Schedules - Enhanced May 25, 2017
Drop That Schema October 27, 2017

SQL RNNR

Table Size and Missing FK Indexes

Related Posts:

Leave a comment Cancel reply

Now Reading (2)

In Queue (1)

Recently Read (16)

M	T	W	T	F	S	S
« Feb				Apr »
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31