DMV Stats by Microsoft, a Hit or Miss?
Hi again,
A few months back our firm decided that performance was enough of a problem for our product that we figured hiring a Microsoft consultant to come out would be a good idea. We learned a lot from the analyst, not so much in terms of things we didn’t already know, but really, useful tools to help make our performance tuning easier. Along with the Performance Dashboard, which I ultimately wrote an entire training manual on, Mr. Microsoft Guy also told us about DMV Stats. If you are already aware of this tool, please stop reading now. However, if you are interested in knowing what it is, check out this link…
That link takes you to Codeple, where Microsoft has uploaded a series of reports that run within SSRS (SQL Server Reporting Services). First thing first, you need SSRS installed and running! Also, it needs to be patched up to the same level as the rest of your SQL Server 2005 system (assuming you are running SQL 2005). May I suggest not using RTM? I had a nasty time of it at my last job when it came to installing and running SSRS. For the love of all that is good and right in the world, download your service packs and apply them.
Also, before you install SQL DMV Stats you’ll need to set up SSRS. Never done it before? Follow this link to determine how:
Really, it is simple once you have the service packs installed.
Other gotchas include setting up DMV Stats after it is installed. Make sure to look at the zip file after you download DMV Stats. There is a read me file in there that explains a few problems. For example, if you have a non-default server install, which could be as little as using a named instance, then you’ll need to address that within a DOS batch file.
Next, after you get the DMV Stats installed you’ll need to configure it. How do you configure that within SSRS you ask? Simple, the reports that run are interactive. There are a few concepts you should be aware of so read the documentation carefully. There are snapshots, samples and baselines. Baselines are uber importante! Make sure you have them. Also, you need to turn on the sql agent jobs. That means, if you are like me and are searching for every ounce of memroy you can muster up on your PC, then you have SQL Agent turned off…. ha, that day has come and gone.
You’ll need to have SQL Agent turned on in order for DMV Stats to work. However, you can adjust the timing of when the jobs are run, check the documentation. Also, each database on your server has to be turned on for collection. That’s the Configure Baselines report (check the name on that, I’m not close to my work PC at the moment).
That leads me to another gotcha… don’t turn on too many databases for monitoring, your IO will go crazy! I frequently had system freezes as I run Virtual PC, SQL Server (both in and outside of the virtualized system) and so you can see how much IO there is. The procedures tied to DMV Stats are somewhat IO intensive so I would suggest you keep a handle on exaclty how much of your available disk IO you have.
Lastly, I’ll state my opinion on DMV Stats. I liked it, but let’s be honest, it’s not Quest Software’s Performance Analyzer tool. I like that it is a free tool, but the resources required to run it on a single box were pretty intensive. I saw the ASP.Net worker process tied to the report generation reach over 400 Mb. Furthermore, the IO quickly rose to the top of my demand, leaving me with little resources to perform other activities (this was clearly because I configured too many databases for it to monitor).
I’ll say one thing for certian, the Performance Dashboard is quite competitive, but the DMV Stats leave some room for improvement. I might blog some more on this later when I get some more time, but that’ll have to do for now. Hope this helps.
Cheers,
Bob
Leave a Reply
You must be logged in to post a comment.
