SQL SERVER – 3 Different Usage of DBCC SQLPERF

Have you ever wondered what does DBCC SQLPERF do in SQL Server?

Well, I have often thought about it and here is the most simple answer for the same.

SQL SERVER - 3 Different Usage of DBCC SQLPERF dbccsqlperf-800x304

Method 1: Displays the LogSpace

This method displays the current size of the transaction log and the percentage of log space used for the database. Here is a simple example of the code.


Here is the result set of the above query.

Method 2: Resetting Wait Statistics

Wait statistics are very important element of SQL Server and I have previously written complete 30 days series on the SQL Wait Statistics. Here is the link to refer the same SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28.

However, if you want to reset the wait statistics without restarting the SQL Server, you can use the following syntax.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR); 

Method 3: Resetting Latch Statistics

Everybody has heard about Locks but not everyone knows that latches. Here is the blog post where I describe what is latches in details and how they are different from locks: What is the Difference Between Latches and Locks. Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency.

SQL Server internally record all the details about latch statistics. If you want to reset the value for the latch, you can run following script.

DBCC SQLPERF("sys.dm_os_latch_stats",CLEAR); 

It will reset all the values for latches without restart of the SQL Server.

Well, that’s it. The above mentioned are three primary usage scenarios for DBCC SQLPERF. I assume that in the future they will introduce a few more examples.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Latch, SQL Lock, SQL Scripts, SQL Server, SQL Server DBCC, SQL Wait Stats
Previous Post
SQL Server – Configuration Manager Console Running Very Slow and Time Out
Next Post
SQL SERVER – Get Wait Stats Related to Specific Session ID With sys.dm_exec_session_wait_stats

Related Posts

2 Comments. Leave new

Leave a Reply