SQL Server: Use DBCC SQLPERF statement to clear wait stats

home » blogs » Quick Tip » SQL Server: Use DBCC SQLPERF statement to clear wait stats

SQL Server: Use DBCC SQLPERF statement to clear wait stats

change text size: A A A

Comments (0)
Print
Rating: 0/5 0 Votes

posted 12/1/2011 4:37:50 AM by Quick Tip - Views: [3700]

You can use DBCC SQLPERF statement to clear wait & latch stats.

For example;

-- clear the wait stats
DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR);

You can use this statement to also clear latch stats.

For example;

-- clear the latch stats
DBCC SQLPERF('sys.dm_os_latch_stats',CLEAR);

If you like the tip, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks

@SQLServerGeeks

Rate This Blog Entry: register or login

Author

Quick Tip (Member since: 9/30/2011 3:53:08 PM)

View Quick Tip 's profile

Comments (no comments yet)

Email Subscriptions

Get the Most Recent Blogs in your inbox

Quick Tip's latest blog posts

SQL Server 2012 Feature Pack

4/23/2012 9:24:41 AM by Quick Tip - Views: 3265 - Comments: 0

Hi SQL Geeks, The Microsoft® SQL Server® 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2012. You can download the ...
SQL Server: Use DBCC SQLPERF statement to see LOG space usage

12/1/2011 4:41:51 AM by Quick Tip - Views: 4406 - Comments: 1

You can use DBCC SQLPERF statement to observe log space usage. For example; DBCC SQLPERF(LOGSPACE); GO Output: Database Name Log Size (MB) Log Space Used (%) Status -----------------------------------...
SQL Server: Use DBCC SQLPERF statement to clear wait stats

12/1/2011 4:37:50 AM by Quick Tip - Views: 3701 - Comments: 0

You can use DBCC SQLPERF statement to clear wait & latch stats. For example; -- clear the wait stats DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR); You can use this statement to also clear latch stat...
SQL Server: Fdhost.exe can be stopped to avoid a reboot while apply Service Pack 2 to SQL Server 2008

10/21/2011 10:08:22 AM by Quick Tip - Views: 8800 - Comments: 0

While apply Service Pack 2 update to a SQL Server 2008 instance, you might come across the following: This basically means that if fdhost.exe is stopped, than a reboot will not be required. Fdhost.exe...
SQL Server: Want to see the complete memory dump of the page - use DBCC PAGE command

10/7/2011 5:10:57 AM by Quick Tip - Views: 2858 - Comments: 1

DBCC PAGE is one of many undocumented and unsupported commands in SQL Server. However, it is safe to use it. It is widely used by SQL folks, Microsoft SQL engineers for troubleshooting purposes. For e...
Want to disable Lock Escalation in SQL Server?

10/3/2011 12:30:35 PM by Quick Tip - Views: 3998 - Comments: 0

Lock escalation in SQL Server happens automatically when the number of Locks threshold is reached orthe memory threshold of the lock manager is reached. Locks are escalated from ROWS/PAGE to TABLEor P...
Want to enable Partition-Level Locking in SQL Server 2008?

9/30/2011 4:14:43 PM by Quick Tip - Views: 2027 - Comments: 0

In SQL Server 2008, lock escalation to Table object is default. You can make partition-level lock escalation as default as follows: ALTER TABLE [Table_Name] SET (LOCK_ESCALATION = AUTO); For more deta...

Latest community blog posts

Parse DBCC MemoryStatus using Powershell

5/19/2013 10:58:46 AM by Ahmad Osama - Views: 236 - Comments: 0

A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...

DB-Migrate v2: Powershell Module to Migrate Databases

5/14/2013 8:11:24 PM by Ahmad Osama - Views: 301 - Comments: 0

This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...

The TSQL Classes Stored Procedures: Transaction Handling

5/12/2013 4:14:12 PM by Ahmad Osama - Views: 313 - Comments: 0

This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...

Reporting With SSRS Part 1 -With Sample Report.

5/9/2013 4:33:03 AM by Syed Asrar Ali - Views: 598 - Comments: 0

Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...

SQL Server – What is IDENTITY_INSERT?

5/8/2013 2:31:43 PM by Piyush Bajaj - Views: 1927 - Comments: 5

Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....

DB-Migrate: A PowerShell Module to Migrate Databases.

5/5/2013 8:48:46 PM by Ahmad Osama - Views: 963 - Comments: 0

Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...

SQL Server blogs from Sarabpreet Singh for the month of April

5/3/2013 10:28:49 AM by Admin Admin - Views: 368 - Comments: 0

Hi SQL Geeks, Here are the blog posts by Sarabpreet Singh for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook...

SQL Server blogs from Prince Rastogi for the month of April

5/3/2013 10:27:25 AM by Admin Admin - Views: 362 - Comments: 0

Hi SQL Geeks, Here are the blog posts by Prince Rastogi for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook a...

SQL Server blogs from Manohar Punna for the month of April

5/3/2013 10:25:31 AM by Admin Admin - Views: 361 - Comments: 0

Hi SQL Geeks, Here are the blog posts by Manohar Punna for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at...

SQL Server blogs from Amit Karkhanis for the month of April

5/3/2013 10:23:49 AM by Admin Admin - Views: 349 - Comments: 0

Hi SQL Geeks, Here are the blog posts by Amit Karkhanis for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook a...

SQL Server blogs from Ahmad Osama for the month of April

5/3/2013 10:22:05 AM by Admin Admin - Views: 364 - Comments: 0

Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...

SQL Server 2012 - a strange issue with user mapping while creating a login

5/2/2013 5:14:46 AM by Amit Bansal - Views: 1344 - Comments: 3

Hi Friends, I observed a strange issue while creating a login in SQL Server 2012 SP1. While creating a new login… After entering some required info… When I click on user mapping and sele...

The TSQL Classes Stored Procedures: Error Handling

5/1/2013 9:05:11 PM by Ahmad Osama - Views: 1464 - Comments: 0

This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...

SQL Server: SSAS - Understanding and configuring Processing Settings

4/29/2013 7:27:36 AM by Amit Karkhanis - Views: 1073 - Comments: 0

Processing is an operation to keep an Analysis Services database (ASDB) up-to-date with the current data from an OLTP system or a data warehouse based on how an ASDB is designed. It is the responsibil...

SQL Server: Handling Hierarchical data inside the database Part3: Index on HierarchyID

4/21/2013 10:27:58 AM by prince rastogi - Views: 1194 - Comments: 2

Hi Friends, Today we will see how we can implement Index on HierarchyID Column, and what benefit we can achieve by using these indexes. In My previous blogs on HierarchyID we saw how can we use Hierar...

The TSQL Classes Stored Procedures: Parameter Sniffing Resolution

4/12/2013 11:55:30 PM by Ahmad Osama - Views: 1892 - Comments: 2

This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-proced...

SQL Server: Handling Hierarchical data inside the database Part2

4/11/2013 6:10:24 PM by prince rastogi - Views: 1882 - Comments: 0

Hi Friends, Today we will see how we can search ancestors and descendants in a HierarchyID column. In the previous blog you saw that how can we handle hierarchical data with HierarchyID data type. The...

Upgrade fails - "The Instance ID 'MSSQLSERVER' is already in use by SQL Server instance 'MSSQLSERVER.INACTIVE'."

4/10/2013 7:25:14 AM by Manohar Punna - Views: 2880 - Comments: 0

This is one of the very frequent errors we receive when we try to upgrade to 2008 or 2008 R2 or to that matter even to 2005. The upgrade may fail for any reason initially and later when you fix that i...

SQL Server: Using Powershell to Move SQL Agent Jobs Across Instances

4/9/2013 9:34:53 PM by Ahmad Osama - Views: 1301 - Comments: 2

Migrating jobs from one server to other can be easily done with powershell. Let’s look into the script. At first, include reference to the relevant SMO assemblies and then create the source and ...

Get free space in all files in an Instance

4/8/2013 9:01:44 AM by Manohar Punna - Views: 1693 - Comments: 1

As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scri...

SQL Server: Handling Hierarchical data inside the database Part1

4/7/2013 7:23:06 AM by prince rastogi - Views: 3187 - Comments: 2

Hi Friends, Sometimes we need to handle hierarchical data in databases like trees where data elements are having relationship like parent and child. Before SQL Server 2008 it was complicated to deal w...

Resolving – Disk Missing Issue for TempDB with a Twist

4/4/2013 9:49:40 AM by Sarabpreet Anand - Views: 1349 - Comments: 9

A not so common but major issue could be missing Disk\Drive issue for any SQL Server, If there were user DB files on that disk chances are you’ll lose some data if the same Disk is not allocated...

SQL Server: Shrink log files across databases using powershell

4/4/2013 8:57:46 AM by Ahmad Osama - Views: 2133 - Comments: 4

Here's a simple powershell script to shrink log files across database. Shrinking T-log isn't a good practise, however at times we might need to release space to OS in development/UAT servers. The scri...

SQL Server: Using Powershell to Fix Orphan Users

4/2/2013 7:05:50 PM by Ahmad Osama - Views: 1774 - Comments: 0

One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell ...

The TSQL Classes Stored Procedures: Parameter sniffing

4/1/2013 10:55:43 PM by Ahmad Osama - Views: 2482 - Comments: 3

This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-cachin...