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.

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.

DBCC SQLPERF(LOGSPACE);
GO 

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)