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 |