BLOB Report T-SQL Tuesday #005- Reporting

Comments: 6 Comments
Published on: April 13, 2010

This month for my second submission into T-SQL Tuesday, I decided to go with something that provides some insight into the data that is being stored and the impact it may have on storage.  If you haven’t seen it by now, I made a bone-headed mistake last week by submitting my entry one week early.  That entry was about trying to create a report that would join information between some DMVs and the Default Trace.  I posted an admission about the mistake as well.  That brings us to the current post.

This month T-SQL Tuesday (brainchild of Adam Machanic (blog | Twitter) is being hosted by Aaron Nelson, and the topic is reporting.  It looks like there are a lot of submissions this month, and I am a little behind at this point.  I hope this is useful to somebody.  I know from reading the submission of Brad Schulz, I knew of some immediate uses for his handy little report.

Background

This new idea was born out of necessity for me about a year ago.  I was having a hard time finding why my primary file group was still so large after moving all of the User objects out of that filegroup and into new filegroups.  I couldn’t find the tying factor – until I checked for BLOBs that were created in the Primary Filegroup by default.  To move them was a manual process and is a different topic from the crux of this one.  Since the initial report to find the BLOBs, I have evolved it somewhat to try and find the size of each object, as well as to report on the usage related with each.  To do this, I needed to use a method I posted about in a different post about finding some Index Information.

Pre-Req

This is a simple requirement for later consumption by the reporting queries.  In favor of time, I am creating a populating a staging table that can be disposed of later, after the queries have been executed and the necessary information has been gleaned from them.

[codesyntax lang="tsql" title="Table Create and Populate"]

Base Query

To get things rolling I want to show what the initial query looked like.  The only point here was to show what objects held BLOB data.

[codesyntax lang="tsql" title="Base Query"]

[/codesyntax]

The query requires a union select in order to find all of the BLOBS involved.  Depending on whether it is LOB or it is OverFlow_Data, the query will be different and needs to account for both types.  The views relevant to finding this information and being queried are sys.partitions and sys.allocation_units.  This query was adequate for the initial needs in determining what was causing the bloat in my primary filegroup.  This is why it is also the base query.  Now, I would like to build on it and find more information concerning these BLOBs.

Up a Step

Using the same views as the base query and just expanding the results returned, I can calculate an approximate size for each BLOB.  This additional information is more useful to me.  This gives me a greater insight into my database objects.  The changes are quite simple, just to iterate, since the pertinent information is obtainable from the present structure of the query.

[codesyntax lang="tsql" title="Step-Up"]

[/codesyntax]

Besides the additional fields pulled into the query, you can see that I also left a couple of comment lines in the query.  This is for informational purposes.  Sometimes, I like to filter out the BLOBs by specific filegroup or by whether or not they have any data in them.

Taking it to the Xtreme

OK, cheesy subheading but it has been getting ingrained in my head from Tony Horton.  This is not an advertisement for that product, but it is kinda like how I feel about the next query.  In this query I will be querying that table that was pre-populated as well as adding a view.  The view that has been added is sys.dm_db_index_usage_stats.  I am also querying these via a Left Outer Join.  I want to return the additional reporting fields for the BLOB related items even if there is not index statistics associated with it.  With these additional objects added to the base query, I have also added a handful of fields to return in the report.

And now the beef of the query.

[codesyntax lang="tsql" title="Prima Query"]

[/codesyntax]

The fields that I added to the above query are listed below.

[codesyntax lang="tsql" title="Added Fields"]

[/codesyntax]

The field that I considered omitting is the ps.IndexSizeMB.  This field reports the size of the Index and not necessarily the size of BLOB data.  I decided to keep it because it can prove useful when trying to determine where the bloat may or may not be.  I also added the type_desc field so I could determine the type of data reported in the query.  Without this field, I think it would be too easy to dismiss some of the entries as duplicates when they really should be present in the report.  The others are information on the usage of the data related to the index and BLOB data.

Conclusion

As you run these queries you will find that there are useful tidbits of information for administrative reporting purposes.  It should also be noted that running the final query will produce the same index usage info for an Index involved in the two types of BLOB data being reported.  It will not split out the UserRequests (for instance) that are performed against LOB_DATA versus ROW_OVERFLOW_DATA.  This can create a reporting problem / mis-perception if one is not aware of it first.

Scripts can be downloaded here.

Edit: Added the scripts for download.

6 Comments - Leave a comment
  1. [...] This post was mentioned on Twitter by Jason Brimhall. Jason Brimhall said: New post: BLOB Report T-SQL Tuesday #005- Reporting (http://bit.ly/caYgNs) [...]

  2. [...] Jason Brimhall who technically did two posts on this topic – shows us how he answered the question “why my primary file group was still so large after moving all of the User objects out of that filegroup and into new filegroups?”  I actually posed a very similar question to Paul Randal during the BOF lunch at the 2009 PASS Summit.  Maybe I should finally get around to blogging everything I wrote down from his answer. [...]

  3. I can’t seem to run the 3rd query. The temp table creates and fills with 12 rows of data but the 3rd query state above 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’.

    What I was really looking for thou was a query that would show me all BLOB columns that are indexed so I can plan for the offline BLOB index rebuilds. Any help would be nice. Thanks.

  4. [...] the April T-SQL Tuesday, I blogged about a report to find information on the BLOBs in a database.  I have since seen a [...]

  5. [...] again it looks like I was early for the T-SQL Tuesday event.  Last month I submitted my entry on reporting as was the topic last month.  If you recall, last month I was a week early.  As it [...]

  6. [...] That script can be found in my post here. [...]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
April 2010
M T W T F S S
« Mar   May »
 1234
567891011
12131415161718
19202122232425
2627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, October 25, 2014