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.


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.


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.

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.

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.

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.

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

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.


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.

Blog Changes

Categories: Blogging
Comments: No Comments
Published on: April 12, 2010

I have made some small but hopefully effective updates to my blog.  The changes are in an effort to make the Blog a little more visible.

I added an SEO plugin.  This should have make the blog more visible in web searches (Google, Bing).

In coordination with that, I changed the naming strategy for my pages.  The pages are now more search friendly rather than the WP default numbering system.  This should be a good thing too.

I also added a MobilePress plugin.  This should help make my Blog more visible to the portable devices.

I have also added the Social Links plugin.  This provides little icons to various social type sites.  This just so happens to be one of the types of plugins that I had installed that was deleted upon theme change.

I hope this makes the site better for the readers.

I have one more Plugin in mind but cannot find a good plugin for it yet.  The plugin would place a FeedBurner Icon so the user can subscribe to my feedburner feeds.  I have seen a redirect plugin.  The problem with that is that I do not want all of my feeds to be redirected to the same feedburner feed.  If anybody knows a good plugin, let me know.

WP and Themes

Categories: Blogging
Comments: No Comments
Published on: April 12, 2010

I have been meaning to write another article on Blogging for some time now.  The intent has been that I wanted to write about my discoveries concerning WordPress and blogging as time elapsed.  For this time around though, I will only discuss one topic (and some correlating subtopics) about my Blog.

I recently changed the theme to my blog because I have never been 100% satisfied with the theme I have picked.  So far, I am getting closer – but have not been completely satisfied with any theme that I have chosen.  How hard could it be?  I remember reading a note from Brent Ozar about making sure you liked your theme  (it is part 4 amongst the Blogging articles he wrote).

When I first read that, I interpreted it as – find a theme and stick with it.  After going back and reading the statement again, it seemed more along the lines of finding a theme you liked for certain so you would not have to deal with editing it.  I think both interpretations work well.  Find a theme and stick with it.

After changing my theme this last weekend, I found a lot of annoyances – and none of them should have been theme related.  I had several plugins that were installed and configured (not theme related plugins) that just disappeared.  Now I have to find the plugin again and reinstall it.  Then there are the problems with widgets not being properly lined up and so you have to tweak with all of them for a bit to get it looking good again.  Add to that, those settings are supposed to be saved but seldom are.

Despite these annoyances, I will deal with it.  Doing the blog is much more rewarding than the pains that come with it.

S3OLV April Meeting Recap

Categories: News, Professional, SSSOLV
Tags: ,
Comments: 2 Comments
Published on: April 9, 2010

We held our meeting last night and had a good time.  Thanks to those that were able to make it out.  Comments made after the meeting were favorable.  People enjoyed the meeting, there was a lot of discussion, and some good information disseminated.  Oh, and we had PIZZA too.

Our first presentation was a product review of a RedGate tool.  RedGate was kind enough to provide the software and information so Kerry could demo it for us.  The tool is called SQL Source Control.  RedGate has integrated SVN with their product line to be able to use a source control for SQL Server.  This tool will also integrate nicely with SQL Compare (allowing you to compare from Source to a Database – very handy).  The tool is pretty easy to use and will probably gain some popularity in the coming months.  I don’t see it on RedGates product page currently though.

The second presentation was by Stacia Misner.  She gave us an informal presentation on some of the new BI features in R2.  It looks to be more productive and will be good for the BI arena.

Thanks to all who helped pull the meeting together last night (RedGate, presenters, attendees).

How Silly

Categories: News, Professional
Comments: 1 Comment
Published on: April 7, 2010

Have you ever done anything that made you feel like a dunderhead?  Maybe a little bone-headed, thick, numb-skullish?

Here is my grin and bear it oops post about a recent post I wrote.  I somehow believed that 6 April 2010 was the second tuesday of the month.  I wasn’t paying attention to the dates, otherwise I would have caught it.  Since I thought it was the second tuesday of the month I got in a hurry to put out my T-SQL Tuesday Submission or the syndicated form of it at SSC.  This is a part of the meme started by Adam Machanic and being hosted this month by Aaron Nelson.

I enjoy participating in this meme.  I learn something new each month revolving around the topic presented each month.  I think some pretty cool stuff is demonstrated and it helps me to learn.

The end result is I was a bit early this month since the real date for submission is not until 13 April 2010.  I had the option of removing my post and delaying the publication until next week.  I felt I had better take my lumps rather than republish the same thing in a week.  Especially after enough people had already read the article.

Sorry for any confusion this mistake may have caused people that read the article.  The content is still good, but just don’t think of it as a TSQL Tuesday submission.

LV April SQL Users Group Meeting

Categories: News, Professional, SSSOLV
Tags: ,
Comments: No Comments
Published on: April 6, 2010

S3OLV or SSSOLV will be holding our meeting Thursday April 8th.  The User Group is in affiliation with PASS and meets monthly typically on the second thursday of the month.

This month we will be hearing from Stacia Misner (blog |blog 2 | @StaciaMisner).  She will be discussing some more information about SQL 2008 R2 and the Businesss Intelligence suite.  She may even be doing the presentation in French ;).

We are also planning on a presentation about some RedGate tools.

Hope to see you at the Learning Center @ 777 Rainbow.

If you have any questions you can contact me through the the comments here or through the group website.

IP and Default Trace…T-SQL Tuesday #005

Comments: 7 Comments
Published on: April 6, 2010

As chance would have it, I had been checking Adam’s blog daily for the last few days to find the next T-SQL Tuesday.  Not having seen it, I started working on my next post yesterday evening.  The fortunate thing is that the post I was working on fits well with this months topic.  T-SQL Tuesday #005 is being hosted by Aaron Nelson.  And now I have one more blog to bookmark since I didn’t have his bookmarked previously.

This month the topic is Reporting.  Reporting is a pretty wide open topic but not nearly as wide open as the previous two months.  Nonetheless, I think the topic should be handled pretty easily by most of the participants.  My post deals with reporting on User connections.  I have to admit that this one really just fell into my lap as I was helping somebody else.

The Story

Recently I came across a question on how to find the IP address of a connection.  From experience I had a couple of preconceptions on what could be done to find this information.  SQL server gives us a few avenues to be able to find that data.  There are also some good reasons to try and track this data as well.  However, the context of the question was different than I had envisioned it.  The person already knew how to gain the IP address and was already well onto their way with a well formed query.  All that was needed was just the final piece of the puzzle.  This final piece was to integrate sys.dm_exec_connections with the existing query which pulls information from trace files that exist on the server.

After exploring the query a bit, it also became evident that other pertinent information could prove quite useful in a single result set.  The trace can be used to pull back plenty of useful information depending on the needs.  Here is a query that you can use to explore that information and determine for yourself the pertinent information for your requirements, or if the information you seek is even attainable through this method.

Now, I must explain that there is a problem with joining the trace files to the DMVs.  The sys.dm_exec_connections maintains current connection information as does the sys.dm_exec_sessions DMV.  Thus mapping the trace file to these DMV’s could be problematic if looking for historical information.  So now the conundrum is really how to make this work.  So that the data returned in the report, some additional constraints would have to be placed on the query.  But let’s first evaluate the first go around with this requirement.

Query Attempts

While evaluating this query, one may spot the obvious problem.  If not seen at this point, a quick execution of the query will divulge the problem.  SPIDs are not unique, they are re-used.  Thus when querying historical information against current running information, one is going to get inaccurate results.  Essentially, for this requirement we have no better certain knowledge what the IP Address would be for those connections showing up in the trace files.  The IP Addresses of the current connections will cross populate and render inaccurate results from the historical information.

My next step gets us a little closer.  I decided to include more limiting information in the Join to the sys.dm_exec_connections view.  The way I determined to do this was that I needed to also include the loginname as a condition.  Thus in order to get that, I need to include sys.dm_exec_sessions in my query.  To make it all come together, I pulled that information into a CTE.  Here is the new version.

The information pulled back is much cleaner now.  But wait, this mostly reflects current connections or connections from the same person who happens to have the same SPID as a previous time that person connected.  Yes, an inherent problem with combining historical information to the current connection information in the server.


My recommendation to solve this need for capturing IP address information along with the person who connected, their computer hostname, and the time that they connected is to do so pre-emptively.  (I know this diverges from the report for a minute, but is necessary to setup for the report).  A solution I have implemented in the past is to use a logon trigger that records the pertinent information to a Table.



This solution gets the IP Address from the ClientHost Field of EventData() which is where the logon occurred.  That should be enough to trace back to the source of the connection.  Also note that the application was not included in this, that is information that could be gained from the trace files.  What this does is create a structure for logging logon events as well as prevent logons from unauthorized sources using an account that has been compromised (while logging that action as well).  With this, I now have a solution that complements the trace file.  I could query both and create a more accurate report of the logon activity that has occurred historically (depending on retention policy etc.).  This last method is somewhat limited by the EventData function and the xml schema that comes with it.  You can check it out here.


So here are a couple of parting solutions that would be able to give us the relevant data for tracking these historical logons.

The above is a simple query to pull back the information from both the violations and successful logon audit tables.

The following is a solution for combining the previous requirement (as I have translated it to truly correlate to Host rather than IP).

The last such report that I wish to share related to this topic is to find current activity.  Since the original idea was to combine the trace with the DMV, I decided that I needed to at least look at the DMV for a relevant query.  These DMV’s provide current activity and need to be combined to provide a quick snapshot of the activity that is occuring on the server at a given moment.  One quick snapshot can tell me who is logged in, from what IP Address, the application, read and write activity, and what the SQL is that is being executed.

There are several queries out there similar to this last one.  The idea is to capture a quick glimpse based on the DMVs which in essence are a report of a subset of server relevant information.


When it is necessary to provide reports on activity occurring on the server, it pays to do a little prep work.  Be Prepared.  It is not an easy task to be able to go back in time and report on data that isn’t captured.  The little prep work that one may need to do is well worth the effort in the end.

Edit:  Corrected 1 detail and fixed some formatting.

Breaking Dawn

Categories: Book Reviews
Comments: No Comments
Published on: April 2, 2010

Finally I have made it through the agony of this series of books.  This one took longer to finally get around to due to the foul taste left by Book 3.  I do have to say that I am mildly surprised by the book and found it considerably more entertaining and worthwhile than the rest of the series combined.

The book ended on a completely happy ending with some sappiness – but that is expected from knowing the rest of the books and story.  This book had a lot less predictability than the prior novels which tells me that the author has learned a thing or two about writing since starting her career.  The end result though was completely predictable and the imprinting was also a no-brainer without any twist.

The story was a lot less sappy – once you got past the honeymoon.  I do wish that a whole lot more was divulged to Charlie about the situation but can conceded the author point of view on it.

I enjoyed this book and find it to be on a much higher par level.  This one was written more for the general public and not just your teeny bopper girl cross-section.

Overall Grade B – worth reading.

page 2 of 2»

April 2010
« Mar   May »

Welcome , today is Saturday, December 7, 2019