What’s all the Wait about…
I have been meaning to publish this post for a long long time. I have no idea what I have been waiting on. As a DBA, that isn’t necessarily a good thing. As a DBA, we would generally like to know what is causing the delay or what the wait is being caused by etc etc etc.
It’s even a bit of coincidence because the topic today would have also worked very well for the TSQL Tuesday topic this month. Robert Davis invited all to participate by writing about waits in SQL Server in some fashion or another. You can read a bit about that from his roundup, with all of the necessary links, here.
Today, I only hope to be able to do a minor justice to the topic.
Since DBAs really do not like to be caught off-guard, it is very common practice to monitor the waits on the server(s) under his/her domain. If the waits are not monitored, then the DBA at least should know how to check the waits and determine what may be helping cause the delays and/or procrastination in SQL Server.
I want to share a tool that I have been impressed with for several years. The tool should be pretty popular by now. Not only do I want to share a bit about that tool, but I will show how to become a bit more efficient through the use of the tool and trying to have the tool help you before you have to turn to the tool to start hunting.
Let’s just say this is a small gift from me to you for this Holiday Season.
What is it?
I was introduced to this tool 6+ years ago. I was happy with it then and started to use it where I could at my employer. After moving on, I have made a consistent recommendation with regards to it. That said, I like the tool for the very precise design of monitoring and inspecting waits on the server. That tool of course is – Ignite for SQL Server by Confio. I will be writing about Ignite 8 and not as much about Ignite Central.
Before getting too far into, I want to say that like many worthwhile tools, Ignite gets better with each release. For me, that speaks to the company and their willingness to listen to their constituents. Take the feedback – make the tool better. Know what you do, what you do well and continue to make it better. I think Confio does a fine job at that.
What you see now is a quick screenshot with a stacked bar chart showing some information that Ignite might present to you. In this case, I have a monthly trend report for a specific server showing the top x waits and how each of those waits stacks up in the grand scheme of things.
Now, at a glance, this is great information. It is enough to get you started. You can see a trend, or maybe the absence of a trend. You can identify at a glance which waits are reportedly problematic in your server. From here you can even drill in and get more information. You would do that by clicking a section of one of the stacked bars to determine what might be related to that wait type on the day related to the stacked bar you clicked.
That is all great. It’s even better when in the middle of troubleshooting (you just have to remember to use the tool).
But what if you are off-site and can’t get to the server housing the reports? What if you are a Consultant and don’t necessarily need/want to login to the client server each day just to check this information? The simple solution is to have the report emailed, right?
Well, with Ignite, that is a possibility too. Confio has created several canned reports that are (rare species here) useful out of the box. To help make it easier for all of us, a link has been created in the application on the Home Page. It is real easy to get to the reporting module and to see all of the possible reports that can be viewed.
With that, we are starting to get somewhere. If you click the Reports link on the home page, you will be presented with two list boxes from which you can pick some reports.
I can run any of those reports from that prompt. That’s good news. But that is not quite yet our final destination. We want to have these reports run auto-magically and be emailed to us. If you look around a bit more on the Reports screen, you will find a “Report Schedules” button. Once the new page loads, you will find there is a Create Schedule Button. By clicking this button, you will permit yourself the opportunity to create a schedule to email a report or group of reports automatically to a group of people or to just yourself – your choice. Following the prompts is very straight forward and worth the five minutes or so to create the schedule.
Here’s a bit of a caveat. You must execute and save the report before you can add it to a schedule. Once you have done these few simple steps, you can have access to the reports from your favorite tablet or mobile device. Better yet, should you see something out of line, you could take an action on it (call somebody and have them fix it, or remote in and fix it for those taking vacation 😉 ).
This was a bit of a short and sweet introduction into just one feature of a really good tool. As a DBA, I like to automate what I can. I also like to monitor what I can. Then there is an aspect of automation and monitoring called reporting and free time. If I can automate and implement a solution with minimal time that provides information that I need – I am usually in favor of that. DBAs need reports on how SQL Server is performing. Without those, you are just waiting to fight fires rather than be proactive. So I hope this simple gift of automated reporting from a great SQL tool can give you more time in the future to be a better DBA.