TSQL Tuesday Indexes and Blobs

Woohoo – chugga chugga chugga chugga – woo woo. The train is pulling into town once again for another installment in the TSQLTuesday Blog party. Michael Swart is hosting and has asked us to post something ado about indexes.

What to do What to do

I thought long and hard about the topic this month. I really wanted to discuss something that would be of use. I also wanted to talk about something that may not have been covered by somebody else. The topic of indexes and combination of blog entries this month should be comprehensive and covering in many aspects of Indexes. We all know that there are Clustered Indexes and Non-clustered indexes, and then the other subtypes of indexes that I am sure have been covered by others in this party.

Then it hit me. One day while helping in the forums I ran across a post requesting information about how to find what columns were LOB columns in the database. I knew I had some past blog posts about various aspects of LOBs, but I had never broken it down to find the columns in the LOB. Even better was that I wanted to know what columns were in what index that were also a BLOB. Oooohh, spine tingling idea – I could cover Blob Index Columns.

Restore from Backup

Not really, but every once in a while we have to dig back in the backups to recover data from the past. I had to pull up an article from the past in order to get to the script that would benefit me in this venture.

Before I go to lengths to post that script, let’s start with the script that was provided as a response to that initial question. This is a straight-forward script that provides the answer to the question posed.

SELECT OBJECT_NAME(c.object_id), c.name, t.name 
  FROM sys.columns c 
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE t.name IN ('text','ntext','image','xml') 
    OR (t.name IN ('varchar','nvarchar','varbinary') AND c.max_length = -1)
    And OBJECTPROPERTY(c.object_id,'ismsshipped') = 0

SELECT OBJECT_NAME(c.object_id), c.name, t.name

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE t.name IN ('text','ntext','image','xml')

OR (t.name IN ('varchar','nvarchar','varbinary') AND c.max_length = -1)

And OBJECTPROPERTY(c.object_id,'ismsshipped') = 0

And here is the script that I thought could prove useful for this situation:

drop table #indstats

CREATE TABLE #indstats (
	indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,database_id BIGINT
	,Object_id BIGINT
	,index_id BIGINT
	,IndexSizeMB DECIMAL(16,1)
)

INSERT INTO #indstats (database_id,object_id,index_id,IndexSizeMB)
	SELECT database_id,object_id,index_id
		,CONVERT(DECIMAL(16,1)
		,(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 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
;

Select FileGroupName = filegroup_name(a.data_space_id)
	,TableName = object_name(p.object_id)
	,p.object_id
	,IndexName = i.name
	,LOBUsedPages = a.used_pages
	,LOBTotalPages = a.total_pages
	,LOBDataSizeMB = a.used_pages * 8/1024
	, ps.IndexSizeMB
	, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests
	, (us.user_updates) AS UserUpdates
	, us.last_user_update AS LastUpdate
	, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL) 
		/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates
	,a.type_desc as AllocUnitType
	From sys.allocation_units a
		Inner Join sys.partitions p
			On p.partition_id = a.container_id
			And a.type = 2					--LOB data is stored in pages of type Text/Image
		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 us.index_id = ps.index_id
			And us.database_id = ps.database_id
			And us.object_id = ps.object_id
		Left Outer Join sys.indexes i
			On i.object_id = p.object_id
			And i.index_id = p.index_id
	Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
		--And a.data_pages &gt; 0
		--And filegroup_name(a.data_space_id) = 'Primary'
Union
Select FileGroupName = filegroup_name(a.data_space_id)
	,TableName = object_name(p.object_id)
	,p.object_id
	,IndexName = i.name
	,LOBUsedPages = a.used_pages
	,LOBTotalPages = a.total_pages
	,LOBDataSizeMB = a.used_pages * 8/1024
	, ps.IndexSizeMB
	, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests
	, (us.user_updates) AS UserUpdates
	, us.last_user_update AS LastUpdate
	, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL) 
		/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates
	,a.type_desc as AllocUnitType
	From sys.allocation_units a
		Inner Join sys.partitions p
			On p.hobt_id = a.container_id
			And a.type = 3					--Overflow data is stored in pages of type Text/Image
		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 us.index_id = ps.index_id
			And us.database_id = ps.database_id
			And us.object_id = ps.object_id
		Left Outer Join sys.indexes i
			On i.object_id = p.object_id
			And i.index_id = p.index_id
	Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
		--And filegroup_name(a.data_space_id) = 'Primary'
		--And a.data_pages &gt; 0

drop table #indstats

CREATE TABLE #indstats (

indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

,database_id BIGINT

,Object_id BIGINT

,index_id BIGINT

,IndexSizeMB DECIMAL(16,1)

)

INSERT INTO #indstats (database_id,object_id,index_id,IndexSizeMB)

SELECT database_id,object_id,index_id

,CONVERT(DECIMAL(16,1)

,(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 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

;

Select FileGroupName = filegroup_name(a.data_space_id)

,TableName = object_name(p.object_id)

,p.object_id

,IndexName = i.name

,LOBUsedPages = a.used_pages

,LOBTotalPages = a.total_pages

,LOBDataSizeMB = a.used_pages * 8/1024

, ps.IndexSizeMB

, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests

, (us.user_updates) AS UserUpdates

, us.last_user_update AS LastUpdate

, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL)

/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates

,a.type_desc as AllocUnitType

From sys.allocation_units a

Inner Join sys.partitions p

On p.partition_id = a.container_id

And a.type = 2 --LOB data is stored in pages of type Text/Image

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 us.index_id = ps.index_id

And us.database_id = ps.database_id

And us.object_id = ps.object_id

Left Outer Join sys.indexes i

On i.object_id = p.object_id

And i.index_id = p.index_id

Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0

--And a.data_pages > 0

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

Union

Select FileGroupName = filegroup_name(a.data_space_id)

,TableName = object_name(p.object_id)

,p.object_id

,IndexName = i.name

,LOBUsedPages = a.used_pages

,LOBTotalPages = a.total_pages

,LOBDataSizeMB = a.used_pages * 8/1024

, ps.IndexSizeMB

, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests

, (us.user_updates) AS UserUpdates

, us.last_user_update AS LastUpdate

, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL)

/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates

,a.type_desc as AllocUnitType

From sys.allocation_units a

Inner Join sys.partitions p

On p.hobt_id = a.container_id

And a.type = 3 --Overflow data is stored in pages of type Text/Image

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 us.index_id = ps.index_id

And us.database_id = ps.database_id

And us.object_id = ps.object_id

Left Outer Join sys.indexes i

On i.object_id = p.object_id

And i.index_id = p.index_id

Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0

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

--And a.data_pages > 0

That script can be found in my post here.

With that script in hand I figured a few short customizations could prove useful to help find the additional column info. Now, I must admit, this is still a work in progress for which I want to tune it and tidy up the script substantially – where possible. My point for now is the concept of using it to retrieve the indexed columns that are blobs and to see that extra detail about it.

I am going to make a few modifications that include adding a CTE, as well as adding the following snippet to both sides of the union, as well as a few additional columns.

		Left Outer Join sys.index_columns ic
			On i.index_id = ic.index_id
			And i.object_id = ic.object_id
		Left Outer Join sys.columns c
			On ic.object_id = c.object_id
			And ic.column_id = c.column_id

Left Outer Join sys.index_columns ic

On i.index_id = ic.index_id

And i.object_id = ic.object_id

Left Outer Join sys.columns c

On ic.object_id = c.object_id

And ic.column_id = c.column_id

This is what I came up with:

CREATE TABLE #indstats (
	indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,database_id BIGINT
	,Object_id BIGINT
	,index_id BIGINT
	,IndexSizeMB DECIMAL(16,1)
)

INSERT INTO #indstats (database_id,object_id,index_id,IndexSizeMB)
	SELECT database_id,object_id,index_id
		,CONVERT(DECIMAL(16,1)
		,(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 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
;

With LOBCols as (
Select FileGroupName = filegroup_name(a.data_space_id)
	,TableName = object_name(p.object_id)
	,p.object_id
	,IndexName = i.name
	,ColumnName = c.name
	,c.column_id
	,LOBUsedPages = a.used_pages
	,LOBTotalPages = a.total_pages
	,LOBDataSizeMB = a.used_pages * 8/1024
	, ps.IndexSizeMB
	, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests
	, (us.user_updates) AS UserUpdates
	, us.last_user_update AS LastUpdate
	, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL) 
		/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates
	,a.type_desc as AllocUnitType
	From sys.allocation_units a
		Inner Join sys.partitions p
			On p.partition_id = a.container_id
			And a.type = 2					--LOB data is stored in pages of type Text/Image
		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 us.index_id = ps.index_id
			And us.database_id = ps.database_id
			And us.object_id = ps.object_id
		Left Outer Join sys.indexes i
			On i.object_id = p.object_id
			And i.index_id = p.index_id
		Left Outer Join sys.index_columns ic
			On i.index_id = ic.index_id
			And i.object_id = ic.object_id
		Left Outer Join sys.columns c
			On ic.object_id = c.object_id
			And ic.column_id = c.column_id
	Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
		--And a.data_pages &gt; 0
		--And filegroup_name(a.data_space_id) = 'Primary'
Union
Select FileGroupName = filegroup_name(a.data_space_id)
	,TableName = object_name(p.object_id)
	,p.object_id
	,IndexName = i.name
	,ColumnName = c.name
	,c.column_id
	,LOBUsedPages = a.used_pages
	,LOBTotalPages = a.total_pages
	,LOBDataSizeMB = a.used_pages * 8/1024
	, ps.IndexSizeMB
	, (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests
	, (us.user_updates) AS UserUpdates
	, us.last_user_update AS LastUpdate
	, CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL) 
		/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates
	,a.type_desc as AllocUnitType
	From sys.allocation_units a
		Inner Join sys.partitions p
			On p.hobt_id = a.container_id
			And a.type = 3					--Overflow data is stored in pages of type Text/Image
		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 us.index_id = ps.index_id
			And us.database_id = ps.database_id
			And us.object_id = ps.object_id
		Left Outer Join sys.indexes i
			On i.object_id = p.object_id
			And i.index_id = p.index_id
		Left Outer Join sys.index_columns ic
			On i.index_id = ic.index_id
			And i.object_id = ic.object_id
		Left Outer Join sys.columns c
			On ic.object_id = c.object_id
			And ic.column_id = c.column_id
	Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0
		--And filegroup_name(a.data_space_id) = 'Primary'
		--And a.data_pages &gt; 0

)

Select L.column_id,L.FileGroupName,L.TableName,L.IndexName,L.ColumnName,L.LOBUsedPages,L.LOBTotalPages,L.LOBDataSizeMB,L.IndexSizeMB
		,L.UserRequests,L.LastUpdate,L.RatioRequestsToUpdates,L.AllocUnitType,t.name
	From LOBCols L
		Inner Join sys.columns c
			On c.object_id = L.object_id
			And c.column_id = L.column_id
		Inner Join sys.types t
			On t.user_type_id = c.user_type_id
	Order By L.TableName asc,L.AllocUnitType

100

101

102

103

104

105

106

107

108

109

110

CREATE TABLE #indstats (

indstatsid INT IDENTITY(1,1