Problem

Transaction log is one of the main aspect of all databases. In some circumstances, the transaction logs get large and not know how much space is utilized by it, become a major issue. It effects the performance of SQL Server because of which user faces problem while working on it. So, how to resolve this issue to improve the SQL Server performance?

How to Use DBCC SQLPERF to Check Transaction Log Performance ?

User can overcome from the problem by checking the transaction log performance. They can use DBCC SQLPERF (logspace) syntax as it helps to give the information of size of transaction logs. It is used to manage various things such as it shows a list of all transaction logs as well the space that is used in transaction log. It clears the wait and latch stats. It also lists all the wait stats on SQL Server.

NOTE: Before using DBCC SQLPERF syntax, some permissions are required as mentioned:

• To run this syntax on SQL Server, View Server State permission is required.
• Alter Server State approval is needed to reset wait and latch statistics on SQL Server.
• View Database State permission is required on SQL Database premium tiers.
• SQL Database admin account is needed on Basic Tiers and SQL Database Standard.

There are some options which user can use to run DBCC SQLPERF syntax according to their requirement. Each option has its own importance as discussed below.

WAITSTATS

When there are some troubleshooting performance issue in SQL Server and it is difficult to understand from where the check. Then, it is recommended to look at high level on your SQL Server and after that find out the root cause. User can use the DBCC SQLPERF WAITSTATS; it gives the high-level information that is required to succeed. Each time a process, which is inside the SQL Server that has to wait for anything, a time that is spend on it is tracked. The wait could be tracked because of the delays in reading data, writing data or some external way. The waiting procedure is allocated a wait type. The wait types are not offensively descriptive. It requires a translation into something that is more meaningful. If the waits are reduced in SQL Server then, user can attain a better overall performance.

LOGSPACE

User can use DBCC SQLPERF LOGSPACE, as it helps to give the details of the Log Space that is used to increase or decrease on database. It gives the information of the used recovery models and transaction log backups that user is using. Even it also gives the details of the transaction logs for auto grow and auto shrink. Users can also check the size of file that has been increased or decreased. It helps to resolve the problem that is faced by them.

‘sys.dm_os_latch_stats’,CLEAR

DBCC SQLPERF (‘sys.dm_os_latch_stats’ clear) is lightweight synchronization mechanism, which defend the access to read as well as change in memory structure. It is held only for the duration of operation. It acts as the synchronization mechanism that helps to avoid the updation of two threads at the same time. However, if the thread requires a latch it will be moved from the running to suspended mode. Later on, it is placed on the wait list to await for the notification, which the latch has acquired in the mode of request.

‘sys.dm_os_wait_stats’,CLEAR

DBCC SQLPERF (sys.dm_os_wait_stats’ clear) offers a necessary metrics for analyzing SQL Server performance issues. Users can gathering all the information about the completed waits that are encountered at time of executing threads. It is a useful way to identify the issues such as waits on locks or IO latency issues. All the counters are reset when the SQL Server is restarted or when the commands are run.

Conclusion

In the above blog, a way to improve the SQL Server performance by using DBCC SQLPERF is discussed. There are various options being discussed that helps user to overcome from the problem. User can utilize any of the mentioned option to increase performance of SQL Server.