The term “dashboards” originated from the automobile world, where it referred to the instrumentation or controls for operating a vehicle. In the IT world, dashboards are used to monitor the state or health of systems or organizations. While running a business is not the same as running a vehicle, the concept of knowing the health of a system is similar. In this blog post, we will explore the Server Dashboard Report in SQL Server Management Studio.
In a previous post, we discussed where to find all the SQL Server Management Studio reports. Today, we will focus on the first and most basic report that all SQL Server users should be familiar with – the “Server Dashboard” report. This report provides an overview of various settings in a SQL Server instance and shows the health of the SQL Server instance at a high level.
Configuration Details
The first section of the Server Dashboard Report is the Configuration Details. This section provides answers to some interesting questions, such as:
- When was the SQL Server started/restarted?
- What edition and version of SQL Server are we running?
- Is the instance a clustered instance?
- How many logical processors are being used by the SQL Server instance?
As a DBA, when asked to performance tune a system, these basic questions can provide valuable insights. Instead of querying or relying on Excel sheets, this report can instantly provide this information. Most of the other values in this section are self-explanatory. It is important to note that the “Scheduler Agent Jobs” value shows the number of jobs created, not the number of jobs currently running or enabled.
Non Default Configuration Options
The next section of the report is the Non Default Configuration Options. This section is useful for administrators who want to know the server-level settings that have been changed from their default values in a given instance. By default, these values are not visible and need to be expanded by clicking on the “+” symbol. This section of the report displays various non-default configuration settings that have been modified using the sp_configure stored procedure. It is particularly important to check if the memory allocated to SQL Server on a given box has been limited, which is shown under the “Max server memory(MB)” section. This section also displays if any trace flags are enabled on the server, which is not easily visible when working with sp_configure.
Activity Details
The busiest section of the Server Dashboard Report is the Activity Details. This section provides a visual graph showing the CPU and IO performance of the SQL Server instance. Additionally, there are several tables that provide valuable information:
- Number of active sessions and transactions in the system
- Active databases available in the instance
- Total server memory in the system
- Logins connected to the server
- Number of blocked transactions in the system
It is worth noting that the report also displays the server-side traces running in the system. While this information is available in the report, it can also be obtained using various Dynamic Management Views (DMVs) in SQL Server. Here are some DMVs that can be used to retrieve similar information:
Title | DMV and Filter |
---|---|
Active session | sys.dm_exec_sessions where is_user_process = 1 and status = ‘running’ |
Active Transactions | sys.dm_tran_active_transactions where transaction_state = 2 or (transaction_type = 4 and dtc_state = 1) |
Total Server Memory | sys.dm_os_performance_counters where counter_name like ‘%Total Server Memory (KB)%’ |
Idle Sessions | sys.dm_exec_sessions where is_user_process = 1 and status = ‘sleeping’ |
Blocked Transactions | sys.dm_tran_locks where request_status = ‘WAIT’ |
Distinct Connected Logins on Sessions | sys.dm_exec_sessions where is_user_process = 1 |
Traces Running | sys.traces where status = 1 |
The queries to generate the graph for CPU and IO performance are also provided in the example article.
While the Server Dashboard Report provides a visually appealing way to view this data, it is important to understand the power of DMVs and how they can be used with SQL Server. All the information displayed in the report is available out-of-the-box, but DMVs allow for more flexibility and customization.
In conclusion, the Server Dashboard Report in SQL Server Management Studio is a valuable tool for monitoring the health and performance of a SQL Server instance. It provides essential configuration details, non-default configuration options, and activity details in a visually appealing format. Additionally, the use of DMVs allows for further exploration and analysis of the data. As a SQL Server user, it is important to familiarize yourself with this report and leverage its capabilities to optimize your SQL Server environment.