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”.
