SQL SERVER – SSMS: Server Dashboard Report

The term dashboards come from the automobile world where it meant as instrumentation or controls for operating a vehicle. In the IT parlance, the use of dashboards meant as the state or health of system or organizations. Though this analogy cannot be taken too far because running a business is not same as running a vehicle. But the similarities of knowing the health of the system is bang-on. Let us learn about Server Dashboard Report in this blog post.

In our previous post, I gave an introduction on where to find all the SQL Server Management Studio reports. Today we will look at the first and the most basic report all of us must be working with. It is called as “Server Dashboard” report under the Server node, standard reports node.

SQL SERVER - SSMS: Server Dashboard Report ssmsreport-1

This report gives and overview of various settings in a SQL Server instance. As the name suggests, it shows the health of SQL Server instance level and has three sections to examine. We will investigate each of these sections next.

SQL SERVER - SSMS: Server Dashboard Report ssmsreport-2

Configuration Details

This section is interesting because it can answer some interesting questions:

  • When was the SQL Server started / restarted?
  • What Edition of product are we running and version?
  • If the instance is a Clustered instance or not.
  • Number of logical processors used by the SQL Server instance.

When someone asks me to performance tune a system, I generally tend to ask some of these basic questions to customers. Lesser known to DBA is that instead of querying or relying on their Excel sheets, this report can give this information in an instant.

Most of the other values are self-explanatory. It should be noted that “Scheduler Agent Jobs” shows the number of jobs created, not what is running or enabled.

Non Default Configuration Options

Let us move to the next section. As an administrator, if you want to know the server level settings that were changed from default in a given instance, then this is the section to note.

By default the values are not visible. We need to click on (+) symbol to toggle it.

SQL SERVER - SSMS: Server Dashboard Report ssmsreport-3

This part of the report can show various non-default configuration settings done via the executing sp_configure stored procedure. You can read about enabling / disabling sp_configure basics from my previous blog posts.

I always look into this section to see if the administrator or someone else in the system has limited the memory allocated to SQL Server on a given box. This comes under the “Max server memory(MB)” section as shown above.

This section also shows if any trace flag is enabled on the server. This information is normally not available when you just play around with sp_configure.

Activity Details

Next the busier part section with Activity Details come. This has a number of interesting information. A visual graph to show the CPU and IO performed on the instance. But most importantly there are sections on the top as a table which is worth a note.

  1. Shows the number of Active sessions and transactions in the system at the moment.
  2. Active databases available in the instance.
  3. Total server memory in the system.
  4. Logins connected to the server.
  5. Number of blocked transactions in the system
  6. Finally, worth to note is the server side traces running in the system.

SQL SERVER - SSMS: Server Dashboard Report ssmsreport-4

Though the information is available in a nice report, the same can be got via various DMVs from SQL Server. I am outlining some of these for reference.

TitleDMV and filter
Active sessionsys.dm_exec_sessions where is_user_process = 1 and status = ‘running’
Active Transactionssys.dm_tran_active_transactions where transaction_state = 2 or( transaction_type = 4 and dtc_state = 1)
Total Server Memorysys.dm_os_performance_counters where counter_name like ‘%Total Server Memory (KB)%’
Idle Sessionssys.dm_exec_sessions where is_user_process = 1 and status = ‘sleeping’
Blocked Transactionssys.dm_tran_locks where request_status = ‘WAIT’
Distinct Connected Logins on Sessionssys.dm_exec_sessions where is_user_process = 1
Traces Runningsys.traces where status = 1

The query to generate the graph is shown below. This is based on historical data using sys.dm_exec_query_stats to show the graph for IO and CPU.

Query 1:

SELECT total_worker_time,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
ELSE DB_NAME(dbid)
END AS DB_NAME,
dbid
FROM sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Query 2:

SELECT total_logical_reads + total_logical_writes AS total_io,
CASE WHEN DB_NAME(dbid) IS NULL THEN 'Adhoc Queries'
ELSE DB_NAME(dbid)
END AS DB_NAME
FROM sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

Though the reports are a great way to see this data visually, it is nice to see the power of DMVs and how they can be used with SQL Server. All these information are available out-of-box but these are visually represented nicely using the SQL Server Management studio dashboards.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Reports, SQL Server, SQL Server Management Studio
Previous Post
SQL SERVER – Introduction to SQL Server Management Studio Reports
Next Post
SQL SERVER – Automate Database Operations for Success – Notes from the Field #035

Related Posts

8 Comments. Leave new

  • Hi, I wanted to customize this Server dashboard . where can I find the .rdl file in server?

    Reply
    • I think they are embedded Reports (no RDL file). You can’t modify it.

      Reply
      • Thanks Pinal for your reply.
        Yes, But found a link to download RDL files of all the Standard Reports.

      • he is a Microsoft Employee and would have access to SQL Server Source Code.

  • question: I see there is a section called “Traces Running”, what is this trace? Is that a SQL trace someone setup or is that an internal one? Just curious as I know the using the SQL profiler can cause performance issues and I want to make sure this is not one of those traces.

    Reply
  • GUSTAVO DE CARVALHO MELO
    April 1, 2016 7:44 pm

    Dave, how you handle the jobs you have installed? I want to monitor my jobs, see if someone have failed and other things like time running etc.

    Reply
  • This seems to be buggy, at least when it comes to servers with lots of usage. I ran this on a production server and the dashboard is showing the top CPU consumer as being the database that comes in at #16 in terms of CPU usage – using your query and summing it up. I ran a trace and saw the same queries being captured as you posted so something is off – I think the report part of it isn’t too happy with the numbers coming out of this server – adhoc queries (top one) are at “23519536955” probably exceeding some int variable limit… (Even though total_worker_time is a bigint data type)

    SELECT SUM(Total_worker_time), [db_name]
    FROM (SELECT total_worker_time,
    CASE WHEN DB_NAME(dbid) IS NULL THEN ‘Adhoc Queries’
    ELSE DB_NAME(dbid)
    END AS DB_NAME,
    dbid
    FROM sys.dm_exec_query_stats s1
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2) DerT
    GROUP BY [db_name]
    ORDER BY SUM(Total_worker_time) DESC

    Reply

Leave a Reply