TSQL Tuesday Indexes and Blobs

Comments: 1 Comment
Published on: September 14, 2010

Woohoo – chugga chugga chugga chugga – woo woo.  The train is pulling into town once again for another installment in the TSQLTuesday Blog party.  Michael Swart is hosting and has asked us to post something ado about indexes.

What to do What to do

I thought long and hard about the topic this month.  I really wanted to discuss something that would be of use.  I also wanted to talk about something that may not have been covered by somebody else.  The topic of indexes and combination of blog entries this month should be comprehensive and covering in many aspects of Indexes.  We all know that there are Clustered Indexes and Non-clustered indexes, and then the other subtypes of indexes that I am sure have been covered by others in this party.

Then it hit me.  One day while helping in the forums I ran across a post requesting information about how to find what columns were LOB columns in the database.  I knew I had some past blog posts about various aspects of LOBs, but I had never broken it down to find the columns in the LOB.  Even better was that I wanted to know what columns were in what index that were also a BLOB.  Oooohh, spine tingling idea – I could cover Blob Index Columns.

Restore from Backup

Not really, but every once in a while we have to dig back in the backups to recover data from the past.  I had to pull up an article from the past in order to get to the script that would benefit me in this venture.

Before I go to lengths to post that script, let’s start with the script that was provided as a response to that initial question.  This is a straight-forward script that provides the answer to the question posed.

And here is the script that I thought could prove useful for this situation:

That script can be found in my post here.

With that script in hand I figured a few short customizations could prove useful to help find the additional column info.  Now, I must admit, this is still a work in progress for which I want to tune it and tidy up the script substantially – where possible.  My point for now is the concept of using it to retrieve the indexed columns that are blobs and to see that extra detail about it.

I am going to make a few modifications that include adding a CTE, as well as adding the following snippet to both sides of the union, as well as a few additional columns.

This is what I came up with:

This script will not return as many rows as the first script shared.  That should be an indicator that not all LOB columns are in an index.  Here we are looking for those that may be causing an extra performance impact due to the inclusion in an index.  It is always good to know what is going on in a database.  Something like this is very helpful for the newly hired DBA when trying to get to understand the databases which need to be supported.  This is also helpful when the need is to document a database.  This script may also spit out multiple rows for the same column in the same index.  This is due to that column being split into two different allocation unit types.  I feel this is good to know as well.

Here we can see just a small sample of the output from this script.

This little snippet is from a CRM database, and you can see that the clustered index in this image has three columns in it that are BLOBS and they are all three being stored as an LOB_Data allocation unit.


Even though this was a quick and dirty entry on the topic, there is much to be gained from the little insight this script can provide.  I would recommend that people find out what indexes are holding that LOB data.  It’s better to know than to not know.

And I go by Indexes – not indices.  Indices for me represent more of a financial term than a logical lookup term.

page 1 of 1

September 2010
« Aug   Oct »

Welcome , today is Tuesday, March 31, 2020