Today I was looking around and was reviewing the newsletter that Stephen Wynkoop sends out every day. If you don’t subscribe to the newsletter (just become a guest member) I would recommend that you do. It has some great information and there is always an interesting topic of conversation. Stephen does a great job keeping up with what’s hot with SQL Server and passing that information on. Here is a clip-it from today’s newsletter.
I can’t tell how many times I’ve had this conversation:
Me: “When do you want to capture the load on SQL Server so we can see what’s happening, tune the system, etc.?”
Customer: “Well, our maintenance window is 2:00a, so let’s do it then.”
Me: “Um. . . well, you really need to do this during a load time that is representative of what we’re trying to troubleshoot. You know, get a look at things as the issues are happening.”
Customer: “But that will slow the system down! We really have to do it off-hours.”
So, here’s the bit of advice for Friday. When you’re looking to troubleshoot, tune or just do reasonable due diligence on your systems, make absolutely certain that you know usage patterns. You should capture performance data and transaction information during the times where you see the issues you’re trying to solve.
My favorite example is when we were working with an insurance company – they had significant spikes between 8:00a and 9:30a, and 12:00p to 1:00p. Why? These were the time periods where people would return to their desks (either at the start of the day or after lunch) to stacks of paperwork that had to be entered and processed. It was very important, then, that we capture information about what’s going on in those periods of time so we could accurately see what was happening.
The reason that I post this today is because I cannot tell you how many times I have had this discussion. I agree with what Stephen is saying but to take it one step further there are some things that you can do with tools out there that will help you get this information without having to just rely on the business day. The point made is a great one, and Stephen is talking about how to do this with Native Tools. I had this exact case not too long ago, I did some research and found a product that I have mentioned here before called Performance Advisor. I love this tool and I had to jump in and mention how that tool can help with this problem.
In the past or even just recently when I had a customer that was asking this question, they were validating that they were having performance problems by the processor or by the number of transactions that were occurring. What I was able to do is two things. One as the client called me I could open the tool, do a quick right click and I was able to do a 15 second quick trace. So if I did impact production it was only for 15 seconds, the tool also gave me the ability to set up a quick trace to kick off by itself without me even have to do a thing. So if I logged on to the system an hour later or a week later I could pull the old trace files.
I have been very reluctant in the past to use tools that were not supplied by SQL Server, there were a number of reasons for this, but when I found that this tool could actually save me hours a day in chasing down issues they had me sold.
So that is my .02 cents for the day. I hope you have a great weekend. From what I am hearing it’s going to snow this weekend where I was planning in going in the Mountains so I may just be staying home. May not be a bad Idea, I have a number of presentations to finish.