T-SQL Tuesday #005 – Reporting For Duty

Aaron Nelson is hosting this week’s T-SQL Tuesday. His topic, like mine from last week, has a lot of ways I could go. He basically said to talk about something that has to do with reporting. I decided to take the hodgepodge approach and talk about a couple things.

Disclaimer  – I am not syndicating this post because I am talking about a vendor’s product. I make no money from said vendor but enjoy working with their tools and have a complimentary license from them for use in consulting (mainly because when clients see the tool they want to buy it anyway). I also was invited to, and participated in, their first ever Product Advisory Board meeting, got some free food out of the deal (but not enough to leave my integrity at home). At the end of the day I like their product and get nothing if you select them and nothing less if you don’t but I think it is at least worth a side by side test drive with other such products.

SQL Sentry 5.5 And Letting Reports Save You

I have known about SQL Sentry for a long time, since hearing about them from a former manager of mine who happens to be a SQL MVP (regular readers already know who I am referring to). I have used their products at a couple of places I’ve worked and have enjoyed the experience. One thing I thought was lacking on the performance side was some underdeveloped reporting functionality but that has just changed. I downloaded their new version 5.5 when it was in Beta and I have loved their reports, even saved my bacon a couple of times.

Well, SQL Sentry 5.5 is in general release now and you can download a trial here. You can read about 5.5’s features in a blog post by Greg Gonzalez, their President and CEO.  I am not going to send screen shots of my environment’s reports because I don’t have a dummy environment setup and it would require too much Photoshop cleaning server names/etc but I’ll steal some of the screen shots Greg used in his post. I’ll also describe the importance of these reports in my environment.

While I think SQL Sentry is a great tool and would recommend it to anyone, I don’t really care what tool you use as long as you track history and can report off of it. It can save your butt, show negative trends and make life easier for you. There are lots of tools that can do this for you. You can buy a 3rd party product to handle monitoring, alerting and the reporting. You can play with the Management Data Warehouse and Data Collectors that Microsoft provides with SQL Server 2008 also.

Anyway… What are some questions that I have recently used these reports to answer?

Our New SAP GL system has been growing, we think. Can you tell us how much the SQL Server database grew since last Friday? So before I had SQL Sentry 5.5 I could have prepared for this question in advance by setting up jobs to track the data, configuring a SCOM SQL management pack to collect that data, etc. I didn’t have those things setup and the question caught me off guard, to be honest. I could go back and look at backup sizes and get a decent idea but I had SQL Sentry Eevent Manager/Performance Advisor running and had just recently installed 5.5 at the time. I clicked the Reports drop down knowing new reports were added when I upgraded to the beta version and saw a menu that looked a bit like this:

Nice! I saw one for SQL Server File Utilization – By Database… What the heck, I gave it a shot and sure enough. I selected the database I wanted, the date range I wanted (Friday to the Tuesday they were asking on) and poof! A pretty report showing the growth of used space in each data file in the SAP ECC database and a great graphical representation of how much free space remained. I was able to quickly tell them that each file had grown by about 12GB in that 5 day process. Graphical representation helped me explain the free space/used space concepts, the beginning/end text data helped me do a quick calculation. (The report had a percent change but they wanted the GB changed so I still had to do a little simple math. Maybe an enhancement request to put the MB changed also? Saves me from having to type calc ;-0 )

“The database server wasn’t running right last night!”

Another use of another report while still in beta mode. Came in to work and folks were complaining about performance the night before in the database server. Application team indicated it was probably a database issue and we should consider ways to improve that. It was an area we have had discussion in the past and there had been some DB issues but I got no alerts from SQL Sentry the night before, no alerts from SCOM and when talking it didn’t make sense that there was any DB issues.

Well before these reports, I could gather the data from a few sources. I could look at SCOM as we are tracking several performance counters, I could look at SQL Sentry’s Performance Advisor tool for that time frame and see the disk activity, TOP SQL commands issued, memory/CPU graphs, etc.

I decided to play with the Reporting menu again and discovered the Windows & SQL Server Performance report. Figured that might be a good way to quickly rule in or out DB server issues for further investigation. Ran the report and pretty much saw a quiet server that had a few spikes that were not correlated. Nice! I exported this report (like I did with the DB file growth report) to .pdf and sent it off with some thoughts. Turns out there were no issues at all (in any portion), just a bit of overreacting..

What’s the Moral?

As fun as it is when first starting out to build your own reports and monitoring processes, there is something nice about having everything you need a few clicks away. Consider looking at collecting your performance data in some form with some tool and have some reports ready to use to save you time in the rabbit hole later.

Mini Rant

I said it was a hodgepodge. This one is about SQL Server Reporting Services (full disclosure: I am in a SSRS 2005 environment, but I don’t believe this was changed for 2008). Now maybe it is only the environment I am in with some reports that need “help” and tuning work (some of which is in progress as time allows the development teams). Why can’t SQL Server write a permanent entry every time a report starts?! Sure there is some data in the Reporting Services TEMPDB but it is difficult, if not impossible, to make usable sense out of the data. Yes, if the report aborts or finishes a record is written to the Execution Log.

That being said, we occasionally have a report execution of one or a couple of our 300-450 reports that just brings the well provisioned SSRS box to its knees. We have no choice but to resort to service restart/iis reset/etc. Mixture of cube and Stored Procedure reports and it could be either. When trying to figure out what is causing the issue and seeing if we can just isolate the responsible dashboard, queries, etc. we can’t see anything. Why? Because there is no execution log entry until the report is done (with success or not).

I should (and will now that I am thinking of it) open a Connect item to suggest that possibly an Execution Log entry with a status of “in progress” be written when a report is FIRST requested. Then when it completes the status can change to completed,aborted,etc.

Is that so wrong? Do you bump into similar issues ever?

We have a plan to get moving to 2008 and that may even reduce the need for this functionality as SQL Server 2008 renders reports much more efficiently than 2005 and we likely won’t have these restart requiring incidents, hopefully.