Help with query to find all BLOB columns that have an index.

  • I'm looking for a query that would show me all BLOB columns that are indexed so I can plan for offline BLOB index rebuilds.

    BLOB indexes must be rebuild offline so I use this SQL to find all tables that have a BLOB column, but it does not do me any good to identify which BLOBs have indexes. I currenlty must do the check by looking at he table indexes.

    SELECT FileGroupName = FILEGROUP_NAME(a.data_space_id)

    ,TableName = OBJECT_NAME(p.OBJECT_ID)

    ,IndexName = i.name

    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.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

    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.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

    We have hundreds of BLOBs across 1000s of tables in 100s of database so you can see my need to simplify this search.

    Any help would be nice.

    Thanks.

  • I have an article about indexes and BLOBs on my Blog. I wrote an article about finding the BLOBs[/url] (which is the query you posted), and then how to find Index info pertaining to them.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • One more note, with the index name (as illustrated in my final query), you can then get the columns involved in the blob index. By just having the index name though, I think you would have plenty of information in regards to which indexes you want to defrag offline.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • yes, I did read your artical at:

    http://www.sqlservercentral.com/blogs/sqlrnnr/archive/2010/04/13/blob-report-t_2D00_sql-tuesday-_2300_005_2D00_-reporting.aspx

    and picked up the SQL from there but I posted asking for help on why the final SQL would not work. I never got a replay so I thought I would post it here for help. If you could help me get the final SQL working then it may just help me resolve my problem.

    My problem with the final SQL was:

    The temp table creates and fills with 12 rows of data but the 3rd query only gives me errors on SQL Server 2005

    Msg 207, Level 16, State 1, Line 15

    Invalid column name 'OBJECT_ID'.

    Msg 207, Level 16, State 1, Line 15

    Invalid column name 'OBJECT_ID'.

  • toddrharrison (4/26/2010)


    yes, I did read your artical at:

    http://www.sqlservercentral.com/blogs/sqlrnnr/archive/2010/04/13/blob-report-t_2D00_sql-tuesday-_2300_005_2D00_-reporting.aspx

    and picked up the SQL from there but I posted asking for help on why the final SQL would not work. I never got a replay so I thought I would post it here for help. If you could help me get the final SQL working then it may just help me resolve my problem.

    My problem with the final SQL was:

    The temp table creates and fills with 12 rows of data but the 3rd query only gives me errors on SQL Server 2005

    Msg 207, Level 16, State 1, Line 15

    Invalid column name 'OBJECT_ID'.

    Msg 207, Level 16, State 1, Line 15

    Invalid column name 'OBJECT_ID'.

    I just got the message with your question. I apologize for not seeing that comment.

    If you copied that query from here (the third query that is failing) there is likely the presence of invisible characters in it. If you copy it to word and then to the query window - you will probably see better results.

    If not, I would verify SP level that you are running on your server.

    As for finding all of the columns that have blobs and indexes, I will work on that a bit and get back to you.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Michelle Ufford's index rebuilding/defragging script might be of interest to you; it has great flexibility and handles LOBs correctly:

    http://sqlfool.com/2010/04/index-defrag-script-v4-0/

  • I re-copied the SQL from your blog, pasted it into notepad and then copied it out to SMSS.

    When I run this code I get the below errors on the 3rd SQL statement.

    I am a sysadmin on the box.

    CREATE TABLE #indstats (

    indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

    ,database_id BIGINT

    ,index_id BIGINT

    ,IndexSizeMB DECIMAL(16,1)

    );

    INSERT INTO #indstats (database_id,index_id,IndexSizeMB)

    SELECT database_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,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

    (12 row(s) affected)

    Msg 207, Level 16, State 1, Line 16

    Invalid column name 'OBJECT_ID'.

    Msg 207, Level 16, State 1, Line 16

    Invalid column name 'OBJECT_ID'.

  • I see the problem.

    I thought I fixed that with the post.

    Here is the corrected 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

    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

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • yes, that worked this time.

    Now, to figure out how to make this into a report of blob columns that have indexes.

  • toddrharrison (4/27/2010)


    yes, that worked this time.

    Now, to figure out how to make this into a report of blob columns that have indexes.

    I should be posting something on my blog within the day. Writing it up currently.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Todd,

    I have posted the new article[/url]. It is rather short (in comparison). I wanted to put the information there so I could be a little more verbose with it and keep things together.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks but non of the SQL works for me again.

    I get this error now.

    Msg 6850, Level 16, State 1, Line 16

    Column name 'TEXT()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

    Msg 3701, Level 11, State 5, Line 2

    Cannot drop the table '#indstats', because it does not exist or you do not have permission.

  • The word TEXT should be lower case. My script plugin converted it to all-caps (lovely). I am fixing that now. You can change the word TEXT to text and it will work.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Arrgghh. I had to append a note to the script. The word text is lower case behind the scenes but transposes to Upper when viewing.

    Here is the full 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;

    ;

    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

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Yes, that does work now. Thanks.

    I get duplicates now. See attached.

    The results from your SQL have a lot of great details but not exactly what I'm looking for.

    All the indexes in this report are for the PK column of the tables that have a blob column somewhere else in the table. The blob column is of course not the PK column. I can rebuild these listed PK indexes online just fine.

    With SQL 2005 Enterprise Edition all indexes on blob columns must be rebuild offline.

    What I was looking for was a simple list of blob columns that are directly indexed. I need this list so I can schedule such offline index rebuilds over downtime.

    I should note: I'm not familiar enough with this offline blob index rebuild requirement to know if the table's clustered index would also have to be included with the offline blob index rebuild.

    I maybe able to build my needed SQL from what you have so kindly given already but I'm an Oracle guy and not too good at SQL Server yet. I will get there but until then any help would be appreciated by me and the community at large. Thanks again.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply