October 2, 2015 at 9:07 am
WayneS (10/2/2015)
Lynn Pettis (10/2/2015)
I love the code that looks like this:WHERE ISNULL(SomeColumn,'') > ''
What I love is when SomeColumn is defined as NOT NULL
I have seen that. Makes you wonder about some people and their code.
October 2, 2015 at 10:59 am
How do I clear the DMVs without restarting SQL?
October 2, 2015 at 11:13 am
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?
I found this StackOverflow article. Does it help?
DBCC SQLPERF('sys.dmv_name_here',CLEAR);
--EDIT: And then this if clearing plan cache too
DBCC SQLPERF('sys.dm_exec_cached_plans', CLEAR);
October 2, 2015 at 11:25 am
SQLRNNR (10/1/2015)
Ed Wagner (10/1/2015)
I'm headed to SQL Saturday Pittsburgh this weekend. I'll be my first visit to this location, but I've heard good things about it.If anyone lives in the area, the sessions are listed at http://www.sqlsaturday.com/440/.
Grumble Grumble
Wishing I could be there.
Ed failed to mention that he is also making his very first presentation at SQL Saturday Pittsburgh!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 2, 2015 at 11:42 am
Michael L John (10/2/2015)
SQLRNNR (10/1/2015)
Ed Wagner (10/1/2015)
I'm headed to SQL Saturday Pittsburgh this weekend. I'll be my first visit to this location, but I've heard good things about it.If anyone lives in the area, the sessions are listed at http://www.sqlsaturday.com/440/.
Grumble Grumble
Wishing I could be there.
Ed failed to mention that he is also making his very first presentation at SQL Saturday Pittsburgh!
Well, now all of "The Thread" hecklers know where to converge at on Saturday!
Go going Ed. Hope you have a great presentation.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2015 at 12:28 pm
Brandie Tarvin (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?I found this StackOverflow article. Does it help?
DBCC SQLPERF('sys.dmv_name_here',CLEAR);
--EDIT: And then this if clearing plan cache too
DBCC SQLPERF('sys.dm_exec_cached_plans', CLEAR);
DBCC SQLPERF only works for a limited number of DMVs
The provided example is not one of them. If you try to clear the cache using this method, you will receive this error:
Msg 2526, Level 16, State 12, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
To clear stats via sqlperf, you can clear wait stats and latch stats.
Some of the dmvs are cleared when a server setting is changed, others when the instance is restarted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2015 at 12:31 pm
SQLRNNR (10/2/2015)
Brandie Tarvin (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?I found this StackOverflow article. Does it help?
DBCC SQLPERF('sys.dmv_name_here',CLEAR);
--EDIT: And then this if clearing plan cache too
DBCC SQLPERF('sys.dm_exec_cached_plans', CLEAR);
DBCC SQLPERF only works for a limited number of DMVs
The provided example is not one of them. If you try to clear the cache using this method, you will receive this error:
Msg 2526, Level 16, State 12, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
To clear stats via sqlperf, you can clear wait stats and latch stats.
Some of the dmvs are cleared when a server setting is changed, others when the instance is restarted.
You do realize the provided DMV was basically an "insert your DMV name here" line, right? @=)
October 2, 2015 at 12:50 pm
SQLRNNR (10/2/2015)
Brandie Tarvin (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?I found this StackOverflow article. Does it help?
DBCC SQLPERF('sys.dmv_name_here',CLEAR);
--EDIT: And then this if clearing plan cache too
DBCC SQLPERF('sys.dm_exec_cached_plans', CLEAR);
DBCC SQLPERF only works for a limited number of DMVs
The provided example is not one of them. If you try to clear the cache using this method, you will receive this error:
Msg 2526, Level 16, State 12, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
To clear stats via sqlperf, you can clear wait stats and latch stats.
Some of the dmvs are cleared when a server setting is changed, others when the instance is restarted.
power cycle server
slip out back door
walk in front
look surprised.
Always recycles DMVs
October 2, 2015 at 12:54 pm
Brandie Tarvin (10/2/2015)
SQLRNNR (10/2/2015)
Brandie Tarvin (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?I found this StackOverflow article. Does it help?
DBCC SQLPERF('sys.dmv_name_here',CLEAR);
--EDIT: And then this if clearing plan cache too
DBCC SQLPERF('sys.dm_exec_cached_plans', CLEAR);
DBCC SQLPERF only works for a limited number of DMVs
The provided example is not one of them. If you try to clear the cache using this method, you will receive this error:
Msg 2526, Level 16, State 12, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
To clear stats via sqlperf, you can clear wait stats and latch stats.
Some of the dmvs are cleared when a server setting is changed, others when the instance is restarted.
You do realize the provided DMV was basically an "insert your DMV name here" line, right? @=)
Guess I should have clarified that the second DMV (which was the obvious example and not the "insert name here" version) example where you use sys.dm_exec_cached_plans will cause that error. The reason is as I previously stated - you can only clear wait stats and latch stats with SQLPERF.
Here is the reference and yes I have tested it.
https://msdn.microsoft.com/en-us/library/ms189768.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2015 at 12:55 pm
Steve Jones - SSC Editor (10/2/2015)
SQLRNNR (10/2/2015)
Brandie Tarvin (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?I found this StackOverflow article. Does it help?
DBCC SQLPERF('sys.dmv_name_here',CLEAR);
--EDIT: And then this if clearing plan cache too
DBCC SQLPERF('sys.dm_exec_cached_plans', CLEAR);
DBCC SQLPERF only works for a limited number of DMVs
The provided example is not one of them. If you try to clear the cache using this method, you will receive this error:
Msg 2526, Level 16, State 12, Line 1
Incorrect DBCC statement. Check the documentation for the correct DBCC syntax and options.
To clear stats via sqlperf, you can clear wait stats and latch stats.
Some of the dmvs are cleared when a server setting is changed, others when the instance is restarted.
power cycle server
slip out back door
walk in front
look surprised.
Always recycles DMVs
Just logon as the service account and do that remotely - buahahaha.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2015 at 12:59 pm
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?
Why?
😎
October 2, 2015 at 3:21 pm
Eirikur Eiriksson (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?Why?
😎
Because I don't necessarily want to restart SQL just to clear some of the DMVs I am using. If I must, I must.
October 2, 2015 at 5:18 pm
Don't clear them. If it's stuff like index usage, index operational stats, wait stats, latch stats, take a record of the DMV at regular intervals and query for the differences between two times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2015 at 11:31 pm
Lynn Pettis (10/2/2015)
Eirikur Eiriksson (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?Why?
😎
Because I don't necessarily want to restart SQL just to clear some of the DMVs I am using. If I must, I must.
Reason for asking is that I've never encountered the situation where the DMVs had to be cleared.
😎
October 2, 2015 at 11:52 pm
Eirikur Eiriksson (10/2/2015)
Lynn Pettis (10/2/2015)
Eirikur Eiriksson (10/2/2015)
Lynn Pettis (10/2/2015)
How do I clear the DMVs without restarting SQL?Why?
😎
Because I don't necessarily want to restart SQL just to clear some of the DMVs I am using. If I must, I must.
Reason for asking is that I've never encountered the situation where the DMVs had to be cleared.
😎
I will typically do like Gail suggested and just take a "baseline" with the current stats in the dmvs and then run the tests, then check stats in the dmvs again. Compare and contrast then I have what I need pertinent to the testing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 50,896 through 50,910 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply