History of query becomes very important when you really need the earlier version of your query, in case you do not have backups or you want to know which queries have been executed earlier in your respective database.
In SQL Server Management Studio, you can achieve it by running given below query but as per SQL Server documentation, the lifetime of the given below rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view and you cannot find the query in the history. Also, if your administrator restarts the server, you will lose the entire history. There is also a limitation that you can only get the queries executed successfully; failed queries will not be there in the history.
- Query History in SQL Server Management Studio
USE AdventureWorks2019
GO
SELECT last_execution_time
, text
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY
last_execution_time DESC
GO
--OUTPUT
- Query History in Azure Data Studio
Fortunately, there is an extension available in Azure Data Studio to get the history of query and there is no limitation like I mentioned above.
Query History Extension Installation:
Let’s install the extension step by step.
Step 1: First of all, please click on View menu which is on the left top of the screen, then click on Extensions as shown below. You can also use the shortcut Ctrl+Shift+X to open Extensions window.
Step 2: Extensions window is now open. Let’s search the Query History extension as shown below and click on Install button.
Step 3: As you can see the Query History extension is installed as shown below.
Query History Extension Application:
Since, we have installed query history extension successfully, let’s use it step by step.
Step 1: Next step is to view the Output window, let’s go to View menu and then click on Output menu as shown below. Or you can also use the shortcut Ctrl+Shift+U.
Step 2: Output window has appeared as shown below.
Step 3: Now, let’s open a New Query window to run some queries and see the query history. To do this you need to go to File menu and then click on New Query, or you can use shortcut Ctrl+N.
Step 4: New Query window is opened as shown below. Please connect the respective database to run queries.
Step 5: Now, we need to execute some queries on Azure Data Studio to see the query history, as shown in the Query History window.
Sections of Query History window:
There are four sections in query history as shown below.
- Status icon: This section shows the status of query.
If a query has executed successfully, we can see a green tick on it.
If a query has error, we can see a cross sign in red which shows that the query has failed. - Query Text: This section shows the actual query.
- Connection Info: This section shows the conection info like Server and Database name.
- TimeStamp: This section shows the timestamp when you executed the query.
Options in Query History:
Query history provides multiple options when you right-click on the query. You get the following options on the context menu:
- Open Query.
- Run Query.
- Delete.
- Clear History.
Let’s discuss these options in detail.
- Open Query:
The Open Query option opens the selected query from query history in a new query widow using the same connection as shown below.
- Run Query:
The Run Query option opens the selected query from query history in a new query widow using the same connection and execute it immediately as shown below.
- Delete:
The Delete option permanently deletes the selected row from query history.
- Clear History:
The Clear History option clears the entire query history.
Query Window: Play / Pause options:
On the right top of the Query window, you can see a play / pause button. This feature gives us the flexibity to stop or start recording query history.
Conclusion:
Query History extension is very useful; you can view query history without much effort in Azure Data Studio.
Let me know if you use this extension and how user friendly you found it.
SQL Server Portal 