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_IDBIGINT
);
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
GOIn 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_IDBIGINT
);
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
GOThe 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_idIn 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.
