Reset SQL Server Performance Statistics

I'm using the sql performance dashboard to monitor performance concerns on a
development sql server. We've made some substantial changes to the server
configuration and I'd like to start with a clean slate of performance stats -
for example we have some missing indexes that have racked up some impressive
Total User Cost and User Impact stats. Is there any way to clear the
historical statistics for these reports?
--
Thanks;
coriNot generally, I'm afraid. I suggest you post this as a wish to
http://connect.microsoft.com/sqlserver. The reports collect most of the information from the dynamic
management views, and there's no coherent way of resetting this statistics with less than restarting
the SQL Server instance. You can reset a couple of these, check out DBCC SQLPERF.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cori" <cori@.nospam.nospam> wrote in message
news:C3137CA2-15BA-4C51-8D1E-1DEA9771CFAF@.microsoft.com...
> I'm using the sql performance dashboard to monitor performance concerns on a
> development sql server. We've made some substantial changes to the server
> configuration and I'd like to start with a clean slate of performance stats -
> for example we have some missing indexes that have racked up some impressive
> Total User Cost and User Impact stats. Is there any way to clear the
> historical statistics for these reports?
> --
> Thanks;
> cori|||Hi Cori,
I understand that you would like to know how to clear the historical
statistics for those reports from SQL Performance Dashboard since you had
made substantial changes to your SQL Server.
If I have misunderstood, please let me know.
Since those reports statistics are calculated from many system views
(snapshots from many system tables beneath) which cannot be modified, it is
hard to start with a clean slate of performance stats. I believe that there
is a limitation here. I also recommend that you refer to Tibor's suggestion
to give Microsoft feedback on this issue.
Since server configurations are recorded in master database, as a possible
work around, you may try rebuilding your master database to see if it
helps. Anyway, I recommend that you perform a test on your test environment
first. It is recommended that you backup all of the databases before this
action.
To rebuild master database, you can refer to:
Rebuilding the master Database
http://msdn2.microsoft.com/en-us/library/aa213831(SQL.80).aspx
How to: Rebuild the Master Database for SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143269.aspx
Hope this helps. Please feel free to let me know if you have any other
questions or concerns.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||> Since server configurations are recorded in master database, as a possible
> work around, you may try rebuilding your master database to see if it
> helps.
I must be missing something here. DMVs don't persist any information so why would you want to
rebuild master when all you need to do is to restart SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:X9nrgUh5HHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hi Cori,
> I understand that you would like to know how to clear the historical
> statistics for those reports from SQL Performance Dashboard since you had
> made substantial changes to your SQL Server.
> If I have misunderstood, please let me know.
> Since those reports statistics are calculated from many system views
> (snapshots from many system tables beneath) which cannot be modified, it is
> hard to start with a clean slate of performance stats. I believe that there
> is a limitation here. I also recommend that you refer to Tibor's suggestion
> to give Microsoft feedback on this issue.
> Since server configurations are recorded in master database, as a possible
> work around, you may try rebuilding your master database to see if it
> helps. Anyway, I recommend that you perform a test on your test environment
> first. It is recommended that you backup all of the databases before this
> action.
> To rebuild master database, you can refer to:
> Rebuilding the master Database
> http://msdn2.microsoft.com/en-us/library/aa213831(SQL.80).aspx
> How to: Rebuild the Master Database for SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143269.aspx
> Hope this helps. Please feel free to let me know if you have any other
> questions or concerns.
> Have a good day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Hi Tibor,
I did not say DMVs will persist data. The records are in inherent system
tables which could not be modified. Restarting SQL Server may resolve some
report results, however restarting SQL Server may not resolve all the
report statistics if some of reports came from the data which has been
recorded.
That is why I recommended him for a test.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Charles,
It just seems to me a very brutal and drastic measure to rebuild the master database. I don't have
the time to go through all the DMV's to see whether there is anyone that uses data persisted in the
master database. The vast majority uses data from internal structures, which do not survive a
restart of the SQL Server service. Some extracts information from the user database (like
sys.dm_db_partition_stats and sys.dm_db_index_physical_stats) but these are not affected by rebuild
of master.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:GM921Gj5HHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hi Tibor,
> I did not say DMVs will persist data. The records are in inherent system
> tables which could not be modified. Restarting SQL Server may resolve some
> report results, however restarting SQL Server may not resolve all the
> report statistics if some of reports came from the data which has been
> recorded.
> That is why I recommended him for a test.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||> ..., however restarting SQL Server may not resolve all the
> report statistics if some of reports came from the data which has been
> recorded.
That's odd. Could you say which statistics?
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/|||Hi Tibor,
I agree that he should try restarting SQL Server first. Rebuilding master
database may not be a good idea, even it is rebuilt, I am afraid that there
are still many jobs left for user databases.
Anyway since this is a specific consulting issue regarding Performance
Dashboard, it is better to sumbit a feedback or post questions to the SQL
Server Tools General forum as mentioned in this page:
SQL Server 2005 Performance Dashboard ReportsBrief Description
http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-
8204-e419218c1efc&displaylang=en
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||> I agree that he should try restarting SQL Server first.
I concur. And if the re-start doesn't clear out some important statistics, then this particular
statistics could be discussed as a special case.
Either the statistics comes from the user database and isn't "clearable" in the first case (like
fragmentation).
Or it might come from the master database (even though I think it is rare, I recognize that such
might exist), then we could discuss this here and see what can be done. And I also agree regarding
feedback specifically to the Dashboard report team.
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:vBctN9l5HHA.2340@.TK2MSFTNGHUB02.phx.gbl...
> Hi Tibor,
> I agree that he should try restarting SQL Server first. Rebuilding master
> database may not be a good idea, even it is rebuilt, I am afraid that there
> are still many jobs left for user databases.
> Anyway since this is a specific consulting issue regarding Performance
> Dashboard, it is better to sumbit a feedback or post questions to the SQL
> Server Tools General forum as mentioned in this page:
> SQL Server 2005 Performance Dashboard ReportsBrief Description
> http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-
> 8204-e419218c1efc&displaylang=en
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Thank you all for your responses. For my needs restarting SQL Server (in
this case at least, which is on a dev server) is sufficient, although I will
file a request with the team.
--
Thanks;
cori
"Tibor Karaszi" wrote:
> > I agree that he should try restarting SQL Server first.
> I concur. And if the re-start doesn't clear out some important statistics, then this particular
> statistics could be discussed as a special case.
> Either the statistics comes from the user database and isn't "clearable" in the first case (like
> fragmentation).
> Or it might come from the master database (even though I think it is rare, I recognize that such
> might exist), then we could discuss this here and see what can be done. And I also agree regarding
> feedback specifically to the Dashboard report team.
> :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
> news:vBctN9l5HHA.2340@.TK2MSFTNGHUB02.phx.gbl...
> > Hi Tibor,
> > I agree that he should try restarting SQL Server first. Rebuilding master
> > database may not be a good idea, even it is rebuilt, I am afraid that there
> > are still many jobs left for user databases.
> >
> > Anyway since this is a specific consulting issue regarding Performance
> > Dashboard, it is better to sumbit a feedback or post questions to the SQL
> > Server Tools General forum as mentioned in this page:
> > SQL Server 2005 Performance Dashboard ReportsBrief Description
> > http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-
> > 8204-e419218c1efc&displaylang=en
> >
> >
> > Best regards,
> > Charles Wang
> > Microsoft Online Community Support
> > =====================================================> > When responding to posts, please "Reply to Group" via
> > your newsreader so that others may learn and benefit
> > from this issue.
> > ======================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > ======================================================> >
>