Azure SQL database query history

In this Azure SQL article, you will learn how to view the most recent Azure SQL database query history. The query history of a database means the queries that are recently executed on that database.

I will explain various techniques to see Azure SQL database query history.

• How to view the query history of an Azure SQL database
• View date and time on which the query got executed
• View query exeution time and performance history
• View execution count and number of rows affected
• Store execution history permanently

How to view the query history of an Azure SQL database

The query history of an Azure SQL database is stored in the cache only. This means the query history is not stored permanently in the database.

To view the Azure SQL database query history, you have to query the database cache. As a result, you can view just only a few records of a query history i.e. available in the cache in an Azure SQL database.

You can query two catalog views named sys.dm_exec_query_stats and sys.dm_exec_sql_text respectively to get information about the queries executed recently in the database.

Below are some examples that may be helpful to you.

Read Read only replica Azure SQL

View date and time on which the query got executed

You can see the execution date and time of the recently executed queries on an Azure SQL database. You just have to query the last_execution_time column of the sys.dm_exec_query_stats view.

Refer to the T-SQL code below:

SELECT stats.last_execution_time AS Time, sqltext.TEXT AS "Query Text"
FROM sys.dm_exec_query_stats AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS sqltext
ORDER BY stats.last_execution_time DESC

Read Bulk loading data to Azure SQL

View query exeution time and performance history

Execute the below code in the Query Editor of the Azure SQL database where you want to see the date, time, and the query text of some recently executed queries:

SELECT TOP 5 query_stats.query_hash AS "Hash",   
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Average CPU Time",  
    MIN(query_stats.statement_text) AS "Query Text"  
FROM   
    (SELECT QStats.*,   
    SUBSTRING(ST.text, (QStats.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QStats.statement_end_offset END   
            - QStats.statement_start_offset)/2) + 1) AS statement_text  
     FROM sys.dm_exec_query_stats AS QStats  
     CROSS APPLY sys.dm_exec_sql_text(QStats.sql_handle) as ST) as query_stats  
GROUP BY query_stats.query_hash  
ORDER BY 2 DESC;  

You can see the query hash, the average CPU time, and the T-SQL query that was executed recently in the database.

This is just the list of 5 recent queries. You can set this value according to your requirements.

Also read, Connect to Azure SQL database using Python

View execution count and number of rows affected

The below T-SQL code will show the recent query history. You can see how many times the query is affected and the number of rows affected by each query:

SELECT qstats.execution_count,  
    SUBSTRING(qtext.text,qstats.statement_start_offset/2 +1,   
                 (CASE WHEN qstats.statement_end_offset = -1   
                       THEN LEN(CONVERT(nvarchar(max), qtext.text)) * 2   
                       ELSE qstats.statement_end_offset end -  
                            qstats.statement_start_offset  
                 )/2  
             ) AS "Query Text",
     qtext.dbid, dbname= DB_NAME (qtext.dbid), qtext.objectid,   
     qstats.total_rows, qstats.last_rows, qstats.min_rows, qstats.max_rows  
FROM sys.dm_exec_query_stats AS qstats   
CROSS APPLY sys.dm_exec_sql_text(qstats.sql_handle) AS qtext   
WHERE qtext.text like '%SELECT%'   
ORDER BY qstats.execution_count DESC;  

In this way, you can view various information about the query history. You just have to query the two catalog views mentioned above. For column information on these views, you can see their documentation. The catalog views are sys.dm_exec_query_stats and sys.dm_exec_sql_text.

Also read, Cannot open server requested by the login

Store execution history permanently

As explained above, the query history is temporarily stored in the Azure database cache. Once the session is over, you cannot access this query history.

However, if you want a permanent record of the query history, you have to store it in log tables. You can make a log table and insert an entry into it after every query execution.

But, you have to keep some things in your mind. Like, this can cause an extra burden on your database and can reduce its performance. Therefore, you have to store the logs wisely.

You should record only those transactions which are important and ignore the unnecessary ones. Also, you have to keep clearing the old transactional logs regularly.

You may like the following Azure SQL tutorials:

• How to rename a database in Azure SQL
• How to create table in azure sql database
• Backup Azure database to local SQL Server
• Backup and restore SQL Server to Azure Blob storage
• Cannot open backup device operating system error 50 azure

Thus, you might have learned how to view and manage the query history in an Azure SQL database.

• How to view query history of an Azure SQL database
• View date and time on which the query got executed
• View query exeution time and performance history
• View execution count and number of rows affected
• Store execution history permanently