Running SQL Statements

Introduction

Earlier today, I wanted to see whether anyone was updating the statistics on one of my databases. Fortunately, there are several was to look for running SQL statements, one of which is detailed below: –

The SQL

In this instance, all I was interested in was UPDATE STATISTICS instructions so I created this: –

SELECT 
  r.session_id, 
  r.status, 
  r.command, 
  s.text AS query_text
FROM 
  sys.dm_exec_requests r
CROSS APPLY 
  sys.dm_exec_sql_text(r.sql_handle) s
WHERE 
  r.command LIKE '%UPDATE STATISTICS%';

Let’s break down the query for a better understanding:

1. SELECT Clause: This part of the query specifies the columns to be returned in the result set.
   • r.session_id: The ID of the session in which the command is running.
   • r.status: The status of the request (e.g., running, waiting, completed).
   • r.command: The type of command that is being executed. In this context, it’s used in the WHERE clause to filter for ‘UPDATE STATISTICS’.
   • s.text AS query_text: The actual SQL text of the query being executed, retrieved from the sys.dm_exec_sql_text dynamic management function and aliased as query_text.
     
2. FROM Clause: This specifies the source of the data.
   • sys.dm_exec_requests r: This is a dynamic management view (DMV) that returns information about each request that is currently executing within SQL Server. The alias ‘r’ is used for easier reference in the query.
     
3. CROSS APPLY: This is used to join each row from sys.dm_exec_requests to the sys.dm_exec_sql_text function.
   • sys.dm_exec_sql_text(r.sql_handle) s: This function returns the text of the SQL batch that is identified by the specified sql_handle. The sql_handle is a unique identifier for the batch of SQL statements being executed, and it comes from the sys.dm_exec_requests DMV. The function result is aliased as ‘s’ for use in the SELECT clause.
     
4. WHERE Clause: Filters the results.
   • r.command = 'UPDATE STATISTICS': This condition filters the results to only include rows where the command being executed is ‘UPDATE STATISTICS’.

This gave me a list of Running SQL Statements that were executing on the database that were updating the statistics. It turned out that we were updating statistics for all databases. By refreshing the display and using the above SQL, I was able to see which table was being updated.

I have written other posts about SQL and updating statistics here : – Update SQL Statistics on things, and have a lot of other SQL posts including What are SQL fat pipes?, Understanding the SQL “SELECT FROM a SELECT” Query, SQL Joins.

The official SQL documentation for UPDATE STATISTICS can be found here: – https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver16

Conclusion

In summary, the query is used to find out which sessions are currently running an ‘UPDATE STATISTICS’ command in SQL Server, along with the status of these requests and the exact SQL query text being executed. ‘UPDATE STATISTICS’ is a command in SQL Server used to update the statistics used by the query optimiser to determine the most efficient way to execute a query.

The same TSQL could be used to find any specific commands you need and is a quick way to determine what is running on your machine, and how far through a process might be.

Related posts:

Running C# on an ESP32 Microcontroller SQL JSON: Taming the Wild World of JSON Data in SQL Server SQL Indexes and Check if One Exists Determine where SQL files are