Have you ever wondered how to monitor the current activity happening on your SQL Server system? Just like checking your speed while driving on the highway or keeping an eye on your kid’s activities on your cell phone, it’s important to have visibility into what’s happening in your SQL Server environment.

In this blog post, we will explore the Performance Dashboard feature in SQL Server that provides a glimpse into the current activity happening on your system. This feature is divided into two sections: User Requests and User Sessions.

User Requests

The User Requests section of the Performance Dashboard provides a live report on the requests in the queue in your system at any given moment. It displays various columns such as Session ID, Request ID, Start Time, Query Text, Status, Wait Type, Wait Time, CPU Time, and more.

By clicking on the “Query Text” column, you can get more details about the specific query, including the Query Plan, Showplan XML, and other vital parameters. This information can also be obtained using DMVs (Dynamic Management Views) such as sys.dm_exec_text_query_plan().

To view the User Requests report, you can execute the following stored procedure:

EXEC msdb.MS_PerfDashboard.usp_RequestDetails 0

User Sessions

The User Sessions section of the Performance Dashboard provides a report on all the active sessions in your live system. It includes information such as Session ID, Login Time, Program Name, NT User Name, CPU Usage, Memory Usage, Logical Reads, Physical Reads, Writes, and more.

To view the User Sessions report, you can execute the following stored procedure:

EXEC msdb.MS_PerfDashboard.usp_SessionDetails @include_system_processes = 0

It’s important to note that the Performance Dashboard Reports are designed to provide a snapshot of the current system activity and should not be seen as proactive reports. They are useful for quickly checking vital parameters interactively.

Quick Tips about Dashboard Reports

Here are some quick tips for using the Performance Dashboard Reports:

• Use the back button available on the top of each report to navigate.
• The client report viewer control used by SSMS does not support copy and paste. To copy the text, right-click on the report and choose “export to Excel”.

In conclusion, the Performance Dashboard feature in SQL Server provides valuable insights into the current activity happening on your system. By utilizing the User Requests and User Sessions reports, you can effectively monitor and analyze the performance of your SQL Server environment.

Start using these powerful DMVs (Dynamic Management Views) to gain a deeper understanding of your SQL Server system and optimize its performance.