SQL2005 Performance Dashboard:how to reset counters

kriki

I am using this tool to tune the performance. And it is really helpfull.
Especially the missing indexes part and the statements that request a lot of resources.
So sometimes I decide to create a SIFT to avoid heavy "SELECT SUM(*" queries.
And indeed, after doing that, the heavy "SELECT SUM(*" isn't executed anymore, but now I would like that it disappears from the reporting.
The best way, I suppose, is to reset the counters in SQL.

In the SQL Server books Online I found some statements, tried them but they don't reset the counters for the SQL2005 Performance Dashboard.
What statement(s) must I use to reset the counters?

colin_leversuch-roberts

the problem, if you like, with the dm views is that they store data from server service start, so generally the only way to "clear" the counters is to stop and start the service.
There are a couple of options that also clear the views, detach database and an auto shrink of the database if auto shrink is turned on.
You'll generally need to write your own routines to extract data from the dm's to use in real time, the dashboard is a step forward but is still a very crude implementation, I'd expect it to improve with sql2008. I can recommend SQL Diagnostic Manager from Idera, which will do much the same and sometimes better.

cnicola

A very dumb question: where do you get dashboard from? :oops:

colin_leversuch-roberts

It's a free download from microsoft SQL server 2005. It requires sp2 to be applied to sql server 2005 first.
There are two parts to the msi, a sql file which installs the various functions and views to power the dashboard; and the reports which you can view.
You have to install the sql parts on your server but can install the reports on workstation only, if you so wish.

http://www.microsoft.com/downloads/deta ... laylang=en

ara3n

I've read somewhere that the dashboard can show you unused keys.
What are the steps to see the list from performance_dashboard_Main report?

Or is there another report that will give me this?

colin_leversuch-roberts

there are a series of views and functions through which you can analyse index usage, I'm about to blog an article about this so when I've finished I'll add a link.
The dashboard does indeed give information on missing indexes and such, however, great care should be taken when using this information as it doesn't have any configerable granularity as such.
My investigations with NAV 4 on sql server show that out of the box for the implementation I support the indexing is largely less than optimal, in fact it's pretty dire.
However analysis must take into account the application usage cycle, e.g. month end, so at least a month's data is required .. be aware that a service restart will clear the counters so you really need to make snapshots of the data to work on.

kriki

I was already afraid that I needed to do a restart.

colin leversuch-roberts wrote:

there are a series of views and functions through which you can analyse index usage, I'm about to blog an article about this so when I've finished I'll add a link.

Don't forget it!

colin_leversuch-roberts

http://sqlblogcasts.com/blogs/grumpyold ... art-1.aspx

There are a few further posts to follow. This is all the DBA view btw. but it is based upon working on a Navision database which has prompted the postings as they form the basis of documentation for my client.