This post is part of the blog series created primarily for database engineers, with the hope that it might help you get a head start on your development tasks. With that being said, let us dive in...

Finding queries currently running on the database

For most common scenarios, my search starts with this query

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;

An example of the output:

This helps us understand which queries are currently running on the database, corresponding session_id, for how long these have been running, etc.

Once there was a situation that a new team member who was not very well proficient with the database issued a "select * on a_certain_table_with_millions_of_records" and the database resources were completely consumed by this query. Using above query I was able to understand what was going on and then I was able to kill the query using the command:

kill <session_id>;

To break this for further understanding, sys.dm_exec_requests returns information about each request that is executing in SQL Server. Refer the documentation link for detailed information. The text of the request can be retrieved by using sys.dm_exec_sql_text with the corresponding sql_handle for the request. sql_handle column value is a token that uniquely identifies the batch or stored procedure that the query is part of.

For simplicity, to get the text of the query, one could first get the sql_handle from sys.dm_exec_requests and then query sys.dm_exec_sql_text with the sql_handle. But in the above mentioned query we have used the APPLY operator to pass values of sql_handle column as argument to sys.dm_exec_sql_text.

When CROSS is specified, no rows are produced when the right_table_source is evaluated against a specified row of the left_table_source and returns an empty result set.

When OUTER is specified, one row is produced for each row of the left_table_source even when the right_table_source evaluates against that row and returns an empty result set.

The APPLY operator works in the following way to produce the table source for the FROM clause:

1. Evaluates right_table_source against each row of the left_table_source to produce rowsets.

   The values in the right_table_source depend on left_table_source. right_table_source can be represented approximately this way: TVF(left_table_source.row), where TVF is a table-valued function.

2. Combines the result sets that are produced for each row in the evaluation of right_table_source with the left_table_source by performing a UNION ALL operation.

   The list of columns produced by the result of the APPLY operator is the set of columns from the left_table_source that is combined with the list of columns from the right_table_source.

This post has more detailed information on the APPLY operator.

Some of the practical scenarios where this is useful

• Finding a long running and resource intensive query

• Finding whether an expected query has begun execution or not, by using a where clause along with identifying information of the concerned query

• Finding which locks a running batch is holding, by using transaction_id with the system function sys.dm_tran_locks

• Finding all the requests in blocked state, and the blocking_session_id for those blocked requests

• Listing all the requests ordered by cpu_time

In the series of posts titled "Azure SQL Database query ideas", I will cover some of the topics that I usually encounter as part of my projects.