Activity Monitor SQL Server


Activity Monitor in SQL Server Management

Very many use Activity Monitor SQL Server and they don't know the Activity Monitor. Even though there are many smart things we can do with questions, to interrogate the health of the system, don't forget that there is enough useful information in the Activity Monitor, and it's easy to get it. There are two basic ways to launch an Activity Monitor. The first is to right-click the server in the Object Explorer.

Another common way to launch it is from the Toolbar: Note that if you are connected to more than one server in the Object Explorer, the Activity Monitor will be connected wherever you have selected any object from within. Activity Monitor puts a little burden on the connected server, but I usually don't feel too bad. However, please don't let it run and continue to use another tab. I have visited sites where many copies run all the time from several users. Do not do it.

I did not find most of the graphics at the top very useful, other than maybe the processor time. This will show you if the server is running flat. The list of processes is more interesting. If you right-click any session, you get this option. The Detail link displays the last command that was executed on that connection. Note that this does not mean it is still running. You can also turn off the process (obviously careful), and you can connect SQL Server Profiler to the server and filter as soon as possible, to see what it's doing with Activity Monitor SQL Server.

Columns can be Filtered

They show you a list of the current values ​​in that column, plus All, and Blank options (rows without values ​​in this column) or NonBlanks (rows with anything in this column). They started as All. For a simple example of this usage, we can choose a session that has all types of commands running, by selecting Running task status.

One that I often use to see this is to look for blocking problems. Every process that is blocked by another process will tell you that. In general, what I'm looking for is the head of the blocking chain which is: who is the culprit who blocks everyone. For that, I look for value 1 in the Head Blocker column. Unfortunately, this method is designed, you cannot choose that value until there is a line with that value. Application names, database names, and logins can all be very useful too.

Download SQL Server Port :

SQL Server Developer
Free Driver
SQL Server Express
Free Driver

The Resource Waits Section is Just a Little Interesting

The information at a rough level is very useful for me. Note that in this system, I / O Buffers are at the top, but the waiting time is cumulative (because the server is restarted) is small. Over time, if the system has been turned on for a long time, you can begin to feel the main wait here, but keep in mind that there are many values ​​that can appear here, without really being a problem Activity Monitor SQL Server.

The list of I / O Data Files is a Little More Interesting

This will show you how busy each data and log file is, for all databases. I usually sort it with Response Time (ms) down. The value here is basically latency for I / O in that file. In this example, 8 milliseconds. It is okay. Current list of expensive queries. This information is available from the DMVs system but this places some useful data in easily available locations.

It keeps updating this from time to time. Note that this will not show you queries that are currently running, only expensive requests and completed recently. If you right-click one, you can see the query text, or check the execution plan that is being used. The last part with the Active Expensive Query will only have data if you use Direct Query Statistics.I will write about SQL Server Activity Monitor on another day.

Discussion: