Data World

SQL Server Tuning: Quick overview of SQL Server performance using Activity Monitor

Posted by Pramod Singla on March 12, 2017

Question:How to get a quick overview of SQL Server performance.

Ans: Use activity monitor in SSMS to get the quick overview of SQL server performance.To run the report right click the server name in SSMS>Activity Monitor

Figure 5: Activity Monitor

  • This shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.
  • Processes:-This shows the list of all the active users which are connected to SQL Server Database Engine. Here, you can right click any of the Session IDs which you think are problematic and can run a SQL Server Profiler Trace to capture all its activities; you can also see the Session Details or can even KILL a process.
  • Resource Waits:-This helps to identify potential bottlenecks with respect to Memory, CPU, and Network I/O etc.
  • Data File I/O:-This displays disk level I/O information related to all the data and log files of user and system databases
  • Recent Expensive Queries:-In this section, database administrators can quickly identify poorly performing queries in an SQL Server Instance. With the feature, which I like the most, you can right click any of the problematic queries and choose the “Edit Query Text” option to edit the query. Moreover, you can also see the execution plan of the poorly performing query by right clicking the problematic query and by selecting the option “Show Execution Plan”.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s