Friday , 20 October 2017

Crucial SQL Server Activity Monitor Features To Identify Bottlenecks

If you are new to the SQL server environment, it can be a bit daunting trying to figure out all the features and tools on your own just as it would be to figure out how to mirror image Photoshop projects on your own. This is especially true if you are just entering into the field of server management for the first time. But, as long as you know the key features and tools provided by SQL servers, you will find that it is much easier to operate than you had imagined. Find out all you need to know about the SQL server activity monitor tool in this post. This way, you will be on your way to mastering SQL server management tasks in no time at all.

Overview

Once you enter into the SQL Activity Monitor panel, you will encounter the Overview section. This section provides you all the information you need to determine server performance with just a single glance. It includes the data in the form of graphical displays, making it easier to witness changes as they occur. The overview pane includes graphical displays of percentage processor time, waiting tasks, database I/O and batch requests/second. This is all crucial information if you plan to make effective use of the SQL server activity monitor feature.

Processes

The next section that you will find beneath the Overview is the Processes section. This drop-down panel presents users with a list of all active users connected to an SQK server instance at a given time. Under this section, you will find many different columns. These columns include session ID, user process, login, database, task state, command, application, wait time in milliseconds, wait type and wait resource. You will also find columns for information regarding blocked by, head blocker, memory use in kilobytes, host name and workload group. Here, you can right click session IDs to run SQL server profiler traces. This allows you to capture activities, see session details, or kill processes which will be necessary in your years in server management.

Resource Waits

Next up in the SQL activity monitor tool is the Resource Waits panel. This pane shows information about existing waits for resources from any micro PC or regular computer. In includes information about the wait category, wait time in milliseconds or seconds, recent wait time in milliseconds or seconds, average waiter count and cumulative wait time in seconds. The section makes it possible to understand where bottlenecks are happening so that you can figure out why. Then, it is up to you to figure out how to fix bottlenecks that you discover.

Data File I/P

The Data File I/P section is the next panel you will encounter following Resource Waits. This panel includes information regarding which database and files produce the most physical I/O. It allows you to see information about database files on the SQL server instance you are responsible for. The database files of all your databases will be listed. This includes MDF, LDF or NDF. It also includes information about their paths and names. Under this section you will also find columns about MB/second read, MB/second written and response time in milliseconds. Make sure you play around in the Data File I/P section of your SQL server activity monitor if you want to get a feel for it yourself.

Recent Expensive Queries

Finally, the last pane you can find on the server activity monitor is the Recent Expensive Queries panel. This section will show you queries that are currently running within the last thirty seconds from tablets, PCs or any other device. It also shows those with significant resource consumption that have run within the last four hours of when you are viewing them. This chart will include information about the top queries by a number of different metrics. You can view consumption information regarding executions/minute, CPU consumption rate, physical reads rate, logical writes rate, logical reads rate, average duration or plan count. In this menu, you can select specific queries to open in Query Editor and find its execution plan. You will certainly need to do this in the future, so do not forget it.

If you are an SQL server beginner, there is a lot you will need to learn. The SQL Server Activity Monitor is probably one of the most important SQL tools for you to learn first. Make sure you familiarize yourself with the panes and sections mentioned above. Otherwise, your foray into server management will not be a successful one.

Photo from https://www.lynda.com/SQL-Server-tutorials/Find-bottlenecks-Activity-Monitor/534417/570954-4.html

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Scroll To Top