This is an incredibly valuable, and free, tool you can use with SQL Server in order to get a real feel as to how your server is performing, where to look for issues, and miscellaneous other nuggets of information.
I have been using this ever since it came out for SQL Server 2005 and there have been a few changes made along the way, but nothing too detrimental and therefore I’m now quite happily using the latest version instead.
To get these all you need to do is download an installer from the Microsoft website. You can find that here
Once downloaded you simply run the installer…
Basically just click your way through and it will start to install:
Once completed, go to the directory in which the Installer placed your files (in my case it was C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Performance Dashboard), connect to a SQL Server instance, and run the code inside SETUP.sql.
This will provide the Performance Dashboard with the information it needs in order to work correctly.
Now we can open the report itself… right click your instance name, click Reports, then Custom Reports…
Navigate to the install folder and select “performance_dashboard_main”. Once you’ve used this once, it will then be remembered in your Reports Menu:
You should see something similar to the following:
This gives you an instant overview with regard to CPU usage and Waits. Invaluable when quickly checking to see if your server is in trouble or not.
Beyond that though, the best recommendation I could give would be to just play with all the links and see what information you can find.
The best place to start is with Expensive Queries.
I tend to stick with CPU and Logical Reads as a baseline but all are important. With these I’m looking for high averages, not total cost. Those are the queries I would expect to look at for quick wins as high figures can usually be tuned more easily.
Don’t discount the others though. A small query with 0.1ms cost that is called 6 billion times an hour could still provide you with huge gains if you can knock just 0.05ms off it. Just that queries with larger averages are usually easier to tweak.
Also have a look at the Missing Index tab. That’s a fun one.
Basically 99% of these are useless (that’s a little harsh, but PLEASE don’t just create everything on this page!!!!!!!), but it’s still worth looking through. What you’re looking for are HIGH User Seeks, HIGH Avg Total User Cost, and HIGH Avg User Impact… BUT the other thing to look for are similar indexes (same columns, different order… same columns but with additional column in one)… also to check for any that are just amendments (eg. added column) to existing indexes.
Therefore by all means have a look in this tab for some ideas, but make sure you do some research rather than just creating them all by default as they may simply be highlighting a tweak to your existing strategy and not require being a whole new standalone index.
There’s a whole lot of information besides this brief overview, but it’s so easy to use that you’ll find it all out yourself in no time. Happy playing.