Simple monitoring of SQL Server activity. Who is active?

What am I talking about?

Any database administrator probably had to deal with the fact that everything works slowly, or does not work at all. The first thing you need to do is figure out what is going on in SQL Server at the moment. It seemed that there would be so many useful pieces in the administrator’s arsenal: a guided Activity Monitor, a bunch of Dynamic Management Views (dmv), sp_who and sp_who2 stored procedures, inherited from the days of SQL Server 7 and SQL Server 2000.
But let's see ...

Monitoring tools

Activity Monitor

It would seem that a great thing, is engaged in just what you need - monitors the activity. I run a heavy accounting report and watch what the Activity Monitor will show me.
The screenshots monitor activity from SQL Server 2005:

and from SQL Server Denali (2012) CTP 3.

M-yes. And if a dozen people run such reports? But this is not a rarity ... It will be rather inconvenient to deal with it, although, of course, progress is obvious. In the Denali Activity Monitor it shows a lot more useful information (for example, on which particular resource is waiting), plus, we can, for example, run the profiler directly from the monitor for the necessary session and track it already in the profiler, but, damn it, it additionally loads and already loaded server. In addition, the problem with the brakes is already there, and those requests that at the time of launching the profiler have already started to be executed, we will not see.
And I want to see exactly this - who and what is performing right now.

sp_who and sp_who2

In the screenshot, the result of executing sp_who (above) and sp_who2 (below), executed while building the same unfortunate report:

Yeah. Very informative. Looking at sp_who we can see only that something is being executed. Of course it is executed - we are looking at it, and we see that some SELECT is executed. Or some SELECT'ov. Great.
sp_who2 shows already more information. Now we can see how much processor time was spent by the session (and probably add up the total time, probably) the number of i / o operations, the name of the database in which all this is done and who blocked this session (if it is locked).
Activity Monitor, as we see, gives more information.

DMV

Beginning with SQL Server 2005, we received a new opportunity to obtain information about the state of the server - Dynamic Management Views . MSDN says this: “Dynamic administrative views and functions return server state data that can be used to monitor the health of a server instance, diagnose problems, and tune performance.”
Indeed, in 2005, SQL Server had a set of views related to the execution of queries at the moment (however, there are also views to view the “history”): here they are . And their number, from version to version continues to increase!
Surely, mastable administrators have a lot of scripts at their disposal to get information about the current state of the server, but what to do if there is no experience with DMV yet, and there are already problems?
')

sp_WhoIsActive

Adam Machanic (SQL Server MVP and MCITP) has developed and is constantly refining the stored procedure sp_WhoIsActive, which relies on these very DMVs and is pretty darn easy to use. Download the latest version of sp_WhoIsActive here . Adam himself has a series of articles devoted to sp_WhoIsActive, consisting of as many as 30 (thirty!) Pieces, you can read it here , and I will try to interest you in reading this material :).
So, we will assume that you downloaded and launched this script on one of the test servers (on any version from 2005 to Denali). Adam advises to store it in the master system database so that it can be called in the context of any database, but this is not necessary, just when calling it in the context of another database, you have to write the name completely - BD.schema.sp_whoIsActive.
So, let's try. In the screenshot, the result of its execution at the time of building the same report:

The result of the exec sp_whoIsActive query is, alas, not in one screen, so here’s a text description of the output of the stored procedure called without parameters.

• [dd hh: mm: ss.mss] - shows the execution time for the active query, the sleep time for the sleeping session;
• [session_id] - actually, spid;
• [sql_text] - shows the text of the query being executed now, or the text of the last executed query, if the session is sleeping;
• [login_name] - well, you understand;
• [wait_info] is a very interesting column. It is output in the format (Ax: Bms / Cms / Dms) E. A is the number of outstanding tasks on E. B / C / D is the wait time in milliseconds. If there is only one session waiting for a resource to be released (as in the screenshot), its wait time will be shown, if 2 sessions are their wait times in B / C format. If they expect 3 or more - we will see the minimum, average and maximum waiting time on this resource in the format B / C / D;
• [CPU] - for an active request - the total CPU time spent by this request, for a sleeping session - the total CPU time for the “entire life” of this session;
• [tempdb_allocations] - for an active query - this is the number of write operations to TempDB during the query execution time; for a sleeping session - the total number of records in TempDB for the entire lifetime of the session;
• [tempdb_current] - for active request - the number of pages in TempDB allocated for this request; for a sleeping session - the total number of pages in TempDB allocated for the entire lifetime of the session;
• [blocking_session_id] - if we are suddenly blocked by someone, it will show the spid (session_id) of who we are blocked by;
• [reads] - for an active request - the number of logical reads performed when this request is executed; for a sleeping session - the number of read pages for the entire life of this session;
• [writes] - all the same, but about the record;
• [physical_reads] - for an active request — the number of physical reads executed during the execution of this request; for a sleeping session - traditionally, the total number of physical readings for the entire life of the session;
• [used_memory] - for active request — the number of eight-kilobyte pages used in the execution of this request; for a sleeping session - how many total memory pages were allocated to her for all her life time;
• [status] - session status - executed, asleep, etc .;
• [open_tran_count] - shows the number of transactions opened by this session;
• [percent_complete] - indicates, if there is such an opportunity, the process of performing the operation (for example, BACKUP, RESTORE) will never show how many percent the SELECT is performed .

The remaining columns in the standard sp_WhoIsActive output are of little interest, and I will not describe them - I think their purpose is clear to everyone (host_name, database_name, program_name, start_time, login_time, request_id, collection_time).

And what? It's all?

No, that's not all. I will also talk about with what (the most interesting and useful, from my point of view) parameters you can call sp_WhoIsActive and what will come of it.

• @help is a terribly useful option. When sp_whoIsActive @help = 1 called, we get information about ALL parameters and output columns on the screen. So if something remains unclear, you can always see the "help"
• @filter_type and @filter - allow you to filter the result of execution. @filter_type can be 'session', 'program', 'database', 'login' and 'host'. In the filter parameter, we specify which object of the selected type interests us. For example, we want to see all the sessions running in the master database, for this we call exec sp_whoIsActive @filter_type = 'database', filter = 'master' . In the filter parameter it is allowed to use "%"
• @not_filter_type and @not_filter - allow us to filter "vice versa". Ie, for example, we want to see everything except those sessions that have 'master' in the “database” field, for this we execute exec sp_WhoIsActive @not_filter_type = 'database', @not_filter = 'master' . Well, or, we want to see what all users do except the sa user ... There can be many uses. The @not_filter parameter allows the use of "%";
• @show_system_spids = 1 - show information about system sessions;
• @get_full_inner_text = 1 - in the sql_text field there will be not just the text of the current request (state) in the package (batch), but the text of the whole batch;
• @get_plans - add a column with query plans to the output;
• @get_transaction_info = 1 - will add to the output the number and volume of entries in the transaction logs, as well as the start time of the last transaction;
• @get_locks = 1 - adds to the output information about all locks imposed during the execution of the query;
• @find_block_leaders = 1 - @find_block_leaders = 1 chain of locks and shows the total number of sessions waiting for the current session to be unlocked;
• @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]' - what if you don’t want to see information about tempDB in the sp_whoIsActive output? With this parameter you can control what it displays;
• @destination_table = 'table_name' - will try to insert the result of the execution to write to the table, but will not check whether this table exists and whether there is enough rights to insert into it.

Now everything

As a result, we have another extremely convenient and flexible tool for tracking current activity on SQL Server. For its normal operation, the permission of VIEW SERVER STATE and the rights to access dmv are sufficient.
It is also worth adding, in the case when the server can only be connected via Dedicated Admin Connection , the sp_whoIsActive call goes off with a bang, while the Activity Monitor, alas, cannot be started.