As a DBA or SQL Developer, working with SQL Server Management Studio (SSMS) is an essential part of our daily tasks. SSMS is a powerful tool that keeps improving with each release, offering new features and enhancements. The product team behind SSMS actively seeks feedback from users to prioritize and implement new features.
One of the hidden gems of SSMS is its collection of standard reports, which provide valuable insights into the performance and usage of SQL Server objects. In this blog post, we will explore two of these reports: Resource Locking Statistics by Objects and Object Execution Statistics.
Resource Locking Statistics by Objects
This report provides information about blocking within a specific database. It utilizes data from the DMV sys.dm_tran_locks and sys.dm_exec_sessions to display details about locks and waiting transactions. The report shows various types of locks, such as METADATA, DATABASE, FILE, TABLE, HOBT, EXTENT, PAGE, KEY, RID, ALLOCATION_UNIT, and APPLICATION.
When there is no blocking in the database, the report will display a message stating that there is no data to show. However, if there are blocking chains, the report will provide a detailed breakdown of the locks and the SPID associated with each lock.
Object Execution Statistics
This report offers insights into the execution of objects within a database. It utilizes data from the DMV sys.dm_exec_query_stats, which contains historical information about query executions. The report displays five graphs that provide information about execution counts, average worker time, total worker time, average logical reads, total logical reads, average logical writes, total logical writes, and average logical I/O.
If there are no plans available in the plan cache for the objects in the database, the report will appear empty. However, on a production server where plans are available, the report will show meaningful data. The report runs a query that retrieves information from various system catalog views, such as sys.objects and sys.schemas, to provide additional metadata about the objects.
By expanding the details for each object, you can see individual statements within the object and their corresponding statistics. This can be useful for identifying performance bottlenecks and optimizing queries.
Conclusion
SQL Server Management Studio offers a wealth of information through its standard reports. The Resource Locking Statistics by Objects report helps identify blocking issues within a database, while the Object Execution Statistics report provides insights into the performance of SQL Server objects. These reports can be valuable tools for database administrators and SQL developers in optimizing and troubleshooting their SQL Server environments.