Solved: server last executed query

When dealing with databases, data manipulation, and application development, among other things, it’s often necessary to trace the last executed query. A thorough understanding of this topic not only helps developers in debugging but also in enhancing the overall performance of their applications. In this article, we’ll look at how you can retrieve the last executed query in a SQL Server using Transact-SQL.

Understanding SQL Server Profiler

SQL Server Profiler is a potent tool that records SQL Server events from a server. It monitors the database and instances of SQL Server for activities and operations that are executed. One such example of this operation is tracing the last executed query. To capture this data, SQL Server Profiler makes use of SQL Trace, which is also utilized by other applications like Index Tuning Wizard.

-- Typical structure of a SQL Server Query capture
SELECT text
FROM sys.dm_exec_sql_text( ( SELECT MAX( request_id ) FROM sys.dm_exec_requests where command = 'SQL Trace' ) )

Extracting the Last Executed Query

To extract the last executed query, we first need to access the dynamic management view: sys.dm_exec_requests. This view holds information about each request that is running in SQL Server. We can use the ‘SQL Trace’ command to filter out and get the specific request ID of the most recent query executed.

-- The SQL Query to get the last executed query 
SELECT text
FROM sys.dm_exec_sql_text( 
  ( SELECT TOP 1 request_id 
    FROM sys.dm_exec_requests 
    ORDER BY start_time DESC) 
)

Understanding Dynamic Management View (DMV)

The Dynamic Management View (DMV) gives you server state information that you can use to monitor the health of a server instance, diagnose problems, and tune performance.

In the query above, we used the DMV: sys.dm_exec_sql_text which returns the text of the SQL batch that is being executed. It’s important to understand that this view only provides information about the current SQL statement on the server, hence it’s often used in conjunction with sys.dm_exec_requests to return specific information.

Step-by-step Explanation of the Code

Let’s breakdown the query used above:

1. We use the DMV sys.dm_exec_requests to get the request id of all the queries being executed.
2. We order these requests by their start time in descending order and pick the first one. This gives us the most recently executed query.

SELECT TOP 1 request_id 
FROM sys.dm_exec_requests 
ORDER BY start_time DESC

3. We pass this request id to another DMV sys.dm_exec_sql_text which returns the text of the SQL batch that corresponds to this ID.

SELECT text
FROM sys.dm_exec_sql_text(<request_id>)

It is important to note that the user must have VIEW SERVER STATE permission to query this data. This can be granted using:

GRANT VIEW SERVER STATE TO <user_name>;