One of the most important things to know as a database administrator when you get that dreaded call from someone in your organization or a client that "my database is slow," is to understand what is currently running on the server in question. (This, of course, after identifying which database is being referenced and what server it's hosted on of course.)
Related: Policing the Use of SQL Server Management Studio
The information about what is currently running on a server is fairly easy to collect from a singled Dynamic Management Function (DMF): sys.dm_exec_sql_text; specifically its text column.
SQL Handles and sys.dm_exec_sql_text Explained
The DMF object sys.dm_exec_sql_text is a Dynamic Management Object (DMO), which means you need to pass a parameter into it to return a table-valued result set. In this case, it expects a sql_handle guid. This guid can be sourced from many different Dynamic Management Views (DMVs), but in our case we're interested in what is currently (actively) running and therefore we're going to source that from the sys.dm_exec_sql_requests DMO. A sql_handle uniquely identifies a batch of T-SQL on an instance.
If you have a query window with statements and you run the entire contents of that query window—all five statements—the sql_handle associated will resolve to all five statements; not one sql_handle per statement. This is important to remember as we later look into how to parse out just the actual executing statement in a particular batch associated with a sql_handle. When passing in a sql_handle the sys.dm_exec_sql_text, DMF will return the following columns, one record for each active request on the instance:
- dbid (aka the database id)
- objectid (aka object_id)
- number
- encrypted
- text
Of these, we’re only interested in this case in the text column. It's interesting to note, though, this is one of the few areas missed by the developers when creating the DMOs. The naming for database_id and object_id is reminiscent of the old days of SQL Server 2000. Yikes!
About Those Execution Plans…
Similar to sys.dm_exec_sql_text, sql_handles and returned batch T-SQL code, we have sys.dm_exec_query_plan and plan_handles for graphical execution plans. By passing in the plan_handle (a unique identifier for graphical execution plan(s) associated with a batch of T-SQL statements, you'll receive among other columns one called query_plan. Identical to sys.dm_exec_sql_text’s sql_handle, we’ll also be sourcing plan_handle from sys.dm_exec_requests.
SELECT S.session_id
, S.host_name
, S.program_name
, S.login_name
, ST.text
, QP.query_plan
FROM sys.dm_exec_sessions AS S
INNER JOIN sys.dm_exec_requests AS R
ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP
WHERE S.is_user_process = 1;
If I was to add the following statement at the beginning of this code block:
SELECT 'This statement is no longer executing';
and the following statement at the end of the of the code block:
SELECT 'This statement has not executed';
then proceed to execute the entirerety of the three statements in one batch, we receive the following results:
Clicking on the xml for the plan, you'll see the xml includes all three statements' plans, not just the one that was executing at the time of the query.
As you see from the results returned, we have a bit of an issue with the results. If there are multiple statements in the activity occurring on the server's instance, we have no idea what the actual live statement is that is running at the time (or possibly hanging at the time.) That is where sys.dm_exec_request’s statement_start_offset and statement_end_offset come into play.
Isolating the Active Statements
Within sys.dm_exec_request there are two exposed columns: statement_start_offset and statement_end_offset. These columns identify the beginning and ending characters of the active statement in the larger command batch. Since they reference the offset in a Unicode column, a value of 50 would refer to the 25th character in the command batch. You may wonder what a value of -1 means when you encounter it as the value for statement_end_offset. A value of -1 simply means that the last character in the active statement is the last character in the command batch; the active statement is the last statement in the batch in this case.
Both sys.dm_exec_sql_text and sys.dm_exec_query_plan allow you to pass in statement_start_offset and statement_end_offset as parameters alongside sql_handle and plan_handle (respectively). The end result is that only the active statement's T-SQL code and graphical execution plan are returned as shown below.
SELECT S.session_id
, S.host_name
, S.program_name
, S.login_name
, SUBSTRING(ST.text, R.statement_start_offset / 2,
(
CASE WHEN R.statement_end_offset = -1
THEN DATALENGTH(ST.text)
ELSE R.statement_end_offset
END - R.statement_start_offset) / 2
) AS statement_executing
, CAST(TQP.query_plan AS XML)
FROM sys.dm_exec_sessions AS S
INNER JOIN sys.dm_exec_requests AS R
ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
CROSS APPLY sys.dm_exec_text_query_plan
(
R.plan_handle
, R.statement_start_offset
, R.statement_end_offset
) AS TQP
WHERE S.is_user_process = 1
ORDER BY R.session_id ;
You may notice some strange trickery going on with parsing of the offset values in this code—this is due to dealing with Unicode values and needing to get that offset adjusted/converted as a result.
Now, when we execute this statement proceeded with the "dummy" statements we added earlier:
SELECT 'This statement is no longer executing';
and:
SELECT 'This statement has not executed';
You'll see that the results only include the statement that was being executed at the time:
Likewise the plan only includes the active statement:
While just being able to isolate what is running on the server won’t answer the question of why your customer's database is slow, it may lead there by telling you what is running on the server and consuming resources your customer’s code needs to perform properly or perhaps even blocking your customer's request.
