The month of June till mid-July has been filled with sports fever. From Wimbledon Tennis to the Soccer World Cup, fans around the world have been captivated by these events. As a sports enthusiast, I couldn’t help but draw parallels between the statistics used in sports and the reports available in SQL Server Management Studio.
Just like team performance against another team and individual player stats against a particular opponent, SQL Server Management Studio provides two types of reports: Object Execution Statistics and Batch Execution Statistics. These reports offer valuable insights into the performance of SQL Server queries and can help identify areas for performance tuning.
Batch Execution Statistics
The Batch Execution Statistics report can be accessed by navigating to Server Node -> Reports -> Standard Reports -> Performance – Batch Execution Statistics. This report utilizes data from DMVs (Dynamic Management Views) such as sys.dm_exec_query_stats and sys.dm_exec_sql_text(sql_handle).
The report is divided into three sections:
- Section 1: This section displays a graphical bar graph representation of Average CPU Time, Average Logical Reads, and Average Logical Writes for individual batches. The batch numbers are indicative, and more detailed information about each batch can be found in section 3.
- Section 2: This section presents a pie chart showing the distribution of Total CPU Time (%) and Total Logical IO (%) by batches. This graphical representation helps identify the batches that consume the highest CPU and IO resources.
- Section 3: This section provides the SQL statements associated with each batch number. It also includes details such as Average CPU, Average Logical Reads, Average Logical Writes, # Executions, and # Plans Generated for each query.
Object Execution Statistics
The Object Execution Statistics report is similar to the Batch Execution Statistics report but focuses on SQL Server objects. It can be accessed through the same path as the previous report.
Like the Batch Execution Statistics report, this report is divided into three sections:
- Section 1: This section displays bar charts for Average CPU and Average IO for specific objects.
- Section 2: This section presents a graphical representation of Total CPU and Total Logical IO by objects.
- Section 3: This section provides detailed information about various objects, including Average CPU, Average IO, and other relevant details.
Both reports are based on queries on two DMVs: sys.dm_exec_query_stats and sys.dm_exec_sql_text. The values in the reports are calculated using columns from these DMVs.
It’s important to note that these reports rely on data from the plan cache. If a particular query has consumed a significant amount of resources in the past but its plan is not available in the cache, the reports will not show that query.
In future blog posts, we will explore more complex reports that can provide deeper insights into SQL Server performance. These reports can be valuable tools for database administrators and developers to identify potential areas for performance tuning.
Remember, just like sports statistics, these reports are indicators and should be used as a starting point for further investigation. The performance of a SQL Server system is influenced by various factors, and a holistic approach is necessary for effective performance tuning.