I just had the coolest experience today, I had to share it with as many people that I can. For readers of this blog you may have picked up on that fact that I like SQL Sentry Performance Advisor. There are a number of reasons why I like this tool, the cool dashboard, the ease of being able to see the history and change back to what is going on right now, the ability to see if you have a bottle neck on your disk drives. There is some really great stuff in there. I have been using the tool now for about six months, and I thought that I knew all there was to know about the tool, all the little features that can make life easier.
Well I ran into a problem today and it caused me a ton of heart burn. I took a look at the dash board and saw that there was a huge amount of transactions that were going thought the server. There was no way that the server could be processing this many transactions. We are talking 30 to 40k in the time of a refresh. I started to look at the Top SQL (The SQL Statements that are causing the most issues by processor or disk or many other counters that I can sort on). My view was configured to look at queries that were taking longer than 5 seconds. But as I watched the transactions climb I was not seeing a single thing on the server that was showing up on the top SQL. I was at a loss, I could not believe that I was seeing this many transactions and I could not find one that was taking longer than five seconds.
I was left with one choice; there must have been something wrong with the tool. I called up Support and asked them what they thought. It was not long before we were on a Goto Meeting and I was showing them what I was looking at. I changed my counter to show me anything that was greater the one millisecond and I was still not seeing what was causing the transactions. Could it be so… I had a server that was processing that many transactions and each one was taking less than a millisecond. We were left to use a feature of the tool called Quick Trace. I have to admit that this is now my favorite part of the tool. The quick trace by default will grab 15 seconds of everything that is going on the server. We found the problem. It looked like there was a look that was just issuing a begin and then a commit, both within less the 1 millisecond. They were coming all from the SPID. They would last for a few min and then they would start up again under a different SPID.
The cool part about this is when I started to look at the Quick Trace results I could see that several SPID’s were doing it. With Tech support on the phone they showed me how to drag and drop the column of SPID to the grouping and within seconds I could order by the SPID. I was able to see that it was doing nothing but issuing a Begin statement and then issuing a second statement of Commit, nothing in between. The end result was I could see that the application was sending statements to the SQL Server that the only other way I would have been able to see would be to start a Trace, Save it to a file and then sort on the SPID. This would have taken a much longer period of time then what I was able to do in Performance Advisor.
I never knew I could do this and was amazed at the flexibility. It saved me hours of chasing the problem down and it will save me even more hours in the future. The most important part is that we found the cause and were able to turn it over to the right team to start looking at why this was happening. When you are billing by the hour, the honorable thing to do is to complete the work as quick as you can without making mistakes. Performance Advisor not only saved me time today but saved my client money.
The point of the blog mention today is twofold. One to let you know how Performance Advisor has made my life easier, but second of all is that I am noticing a trend with 3rd party tools. The trend that I am seeing is that people purchase the tool because it helps them with on problem or another. Once they get the tool installed configured and the problem resolved then they do not pay much attention to the tool from there on out. I have seen this not only with the tool that I have mentioned but also with the tools that I have used before. If you are going to spend the money spend the time to learn the tool. Learn all that it can do and your life as a DBA can be made so much easier.