Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

BLOB Index Columns

For the April T-SQL Tuesday, I blogged about a report to find information on the BLOBs in a database.  I have since seen a request to add to that script.  The addition would add some good information concerning the columns involved in a BLOB index.  This information is to find all of the columns that are involved in the index that includes a BLOB in the index.

Base Script

In that article I posted a script to help arrive at the final report.  There were a couple of things required for the setup.  I am including all of that information here in a single script.

CREATE TABLE #indstats (
        indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
 
INSERT INTO #indstats (database_id,index_id,OBJECT_ID,IndexSizeMB)
        SELECT database_id,index_id,OBJECT_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)
        ,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
        ,s.collist
        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)
        ,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
        ,s.ColList
        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
        ORDER BY TableName ASC,a.type_desc;
 
GO
 
DROP TABLE #indstats
GO

In this script, I made a slight alteration from the article I posted.  In that article, I somehow missed a change to the script I had been testing.  That change is in the Temp table that I created (to properly support the Join statements on each side of the Union Select statement).  I simply added the object_id.

The Change

CREATE TABLE #indstats (
        indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
 
INSERT INTO #indstats (database_id,index_id,OBJECT_ID,IndexSizeMB)
	SELECT database_id,index_id,OBJECT_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 IndexStuff AS (
    SELECT
      icol.OBJECT_ID
      ,i.name
      ,icol.index_id
      ,STUFF(
		(
			SELECT ',' + c.name AS [TEXT()]
				FROM sys.index_columns ic
					INNER Join sys.columns c
						ON ic.OBJECT_ID = c.OBJECT_ID
						AND ic.column_id = c.column_id
				WHERE ic.OBJECT_ID = icol.OBJECT_ID
					AND ic.index_id = icol.index_id
			ORDER BY ic.key_ordinal
			FOR XML PATH('')
	    ), 1, 1, '') AS ColList
	FROM sys.index_columns icol
		INNER Join sys.indexes i
			ON icol.OBJECT_ID = i.OBJECT_ID
			AND icol.index_id = i.index_id
    GROUP BY icol.OBJECT_ID, i.name, icol.index_id
)
 
SELECT FileGroupName = FILEGROUP_NAME(a.data_space_id)
		,TableName = OBJECT_NAME(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
		,s.collist	--Added for Column Output
	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 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 IndexStuff s
			ON s.OBJECT_ID = i.OBJECT_ID
			And s.index_id = i.index_id
	WHERE OBJECTPROPERTY(p.OBJECT_ID,'IsMSShipped') = 0
UNION
	SELECT FileGroupName = FILEGROUP_NAME(a.data_space_id)
		,TableName = OBJECT_NAME(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
		,s.ColList	--Added for Column output
	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 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 IndexStuff s
			ON s.OBJECT_ID = i.OBJECT_ID
			And s.index_id = i.index_id
	WHERE OBJECTPROPERTY(p.OBJECT_ID,'IsMSShipped') = 0
		--And filegroup_name(a.data_space_id) = 'Primary'
		--And a.data_pages > 0
	ORDER BY TableName ASC,a.type_desc;
 
GO
 
DROP TABLE #indstats
GO

The guts of the change to add in the columns for this script comes with the following segment of code.

WITH IndexStuff AS (
    SELECT
      icol.OBJECT_ID
      ,i.name
      ,icol.index_id
      ,STUFF(
		(
			SELECT ',' + c.name AS [TEXT()]
				FROM sys.index_columns ic
					INNER Join sys.columns c
						ON ic.OBJECT_ID = c.OBJECT_ID
						AND ic.column_id = c.column_id
				WHERE ic.OBJECT_ID = icol.OBJECT_ID
					AND ic.index_id = icol.index_id
			ORDER BY ic.key_ordinal
			FOR XML PATH('')
	    ), 1, 1, '') AS ColList
	FROM sys.index_columns icol
		INNER Join sys.indexes i
			ON icol.OBJECT_ID = i.OBJECT_ID
			AND icol.index_id = i.index_id
    GROUP BY icol.OBJECT_ID, i.name, icol.index_id
)

With that snippet, I also needed to Join it to the select statements, and thus it was added on both sides of the Union statement as illustrated in the next example.

LEFT Outer Join IndexStuff s
			ON s.OBJECT_ID = i.OBJECT_ID
			And s.index_id = i.index_id

In the above CTE, you will note that I used the STUFF function along with a FOR XML Path statement.  The combination of these statements allows one to concatenate a list into a comma separated list as I have done with the ColList (column list) column.  I also want to note here that I am using the TEXT() keyword along with the FOR XML Path.  There are other methods of returning information back to the screen when using FOR XML Path.  I chose to use the TEXT() in this case because I am just returning a concatenated list of columns that really only should read as text.  If I were returning a SQL statement, I would choose a different method to make the text more readable.

Conclusion

I chose to make this subtle change via the CTE due to the ease of understanding and readability of the code for me.  By illustrating the columns involved in an index that is on a BLOB column, one can gain greater insight into the use of the database.  I am glad that this change was requested because it makes sense to me.

I hope you find it useful.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.