Monitoring SQL Server performance is one of the most common tasks of DBA. A SQL Server Performances dashboard can be found in our SSMS. Microsoft embedded a very useful tool in SSMS that DBA can use to monitoring SQL Server performances: SQL server Activity Monitor.
What to monitor?
Well, as a DBA your first look should be given to the CPU usage. Once the CPU usage starts to get critical and reach constantly very high peak levels, you may need to investigate on what needs to be done. An high CPU usage can cause bottlenecks in any of the front end applications that may be connected to the SQL Db.
Where to find the SQL Activity Monitor?
The SQL Activity Monitor, as said, is a very useful tool that is available in any version of SSMS. This tool is actually composed by different parts and all of them can be giving important hints to the DBA.
Let’s have a look at how to reach the SQL Activity Monitor. First, we need to open the SSMS and then navigate to our database instance. Once we are connected we can then right-click on the name of the instance itself and select Activity Monitor from the Context Menu.
Once clicked, the Activity monitor tab will be displayed.
This Tab has different sections that are important for your investigations and monitoring of the SQL Server performances.
What to look in the SQL Server Activity Monitor?
The first section is a graphical overview of the SQL server performance. Here we have the processor time, the waiting tasks, the database I/O activities, and the Batch Requests. Already in this Overview, the user can get a fast idea if there are things to investigate. The processing time is the most used KPI here. If the processor time graph has peaked is possible that some query is not optimized.
If we have peaks in the processor time, then we need to look at the active expensive queries section. In this section, you’re able to check which query is consuming most of the processor time.