April 26, 2010 at 3:41 pm
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.
April 26, 2010 at 4:19 pm
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
April 26, 2010 at 4:26 pm
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
April 26, 2010 at 4:36 pm
yes, I did read your artical at:
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'.
April 26, 2010 at 6:07 pm
toddrharrison (4/26/2010)
yes, I did read your artical at: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
April 26, 2010 at 10:50 pm
Michelle Ufford's index rebuilding/defragging script might be of interest to you; it has great flexibility and handles LOBs correctly:
April 27, 2010 at 11:15 am
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'.
April 27, 2010 at 1:22 pm
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
April 27, 2010 at 1:33 pm
yes, that worked this time.
Now, to figure out how to make this into a report of blob columns that have indexes.
April 27, 2010 at 1:46 pm
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
April 27, 2010 at 4:06 pm
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
April 27, 2010 at 5:01 pm
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.
April 27, 2010 at 5:24 pm
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
April 27, 2010 at 5:27 pm
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
April 28, 2010 at 9:37 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy