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.
[codesyntax lang=”tsql”]
|
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
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 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 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 |
[/codesyntax]
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
[codesyntax lang=”tsql”]
|
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
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()] --text should be lower case 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 |
[/codesyntax]
The guts of the change to add in the columns for this script comes with the following segment of code.
[codesyntax lang=”tsql”]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
With IndexStuff as ( SELECT icol.object_id ,i.name ,icol.index_id ,STUFF( ( SELECT ',' + c.name AS [text()] --text should be lower case 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 ) |
[/codesyntax]
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.
[codesyntax lang=”tsql”]
|
1 2 3 |
Left Outer Join IndexStuff s On s.object_id = i.object_id And s.index_id = i.index_id |
[/codesyntax]
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.
Edit: Made a correction to the first script