I have a mouse (the real one that we connect to our laptops not the fake one that runs around after cheese :-p) that jumps all over my screen and every once in a while it lands right on the little X button on the top right corner and I end up clicking before I know what happened. This might seem like a silly problem to have but it’s a pain when I write queries in SQL Server coz the immediate next thing I do is press NO as soon as I see a pop up.
In short I end up losing my queries. Another problem I face is I often write a ton of queries on SSMS without realizing how useful they would be later on for some other requirement. To solve these issues it seems the best solution would be to save every query as soon it executed. This way, at a later date I can revisit the code. Now we already know that this can be achieved using a trace and that a trace can collect a lot more info. But since I was running this on my laptop and that I wanted a light weight way of achieving this I decided to go with XE or Extended Events. The problem with using XE is querying the data afterwards so in this post I am going to show you how I set up your very own query tracker and a procedure to query these XE files.
First off I create a folder in the logs folder for my XE output.
Next I create an XE session to capture the SQL_TEXT every time a SP or a query completes.
CREATE EVENT SESSION [Querytracker] ON SERVER ADD EVENT sqlserver.sp_statement_completed (ACTION(package0.collect_system_time, package0.event_sequence, sqlserver.client_app_name, sqlserver.sql_text) WHERE ([package0].[greater_than_equal_uint64]([sqlserver].[database_id], (7))))
,ADD EVENT sqlserver.sql_statement_completed (SET collect_statement = (1) ACTION(package0.collect_system_time, package0.event_sequence, sqlserver.client_app_name, sqlserver.sql_text) WHERE ([package0].[greater_than_equal_uint64]([sqlserver].[database_id], (7)))) ADD TARGET package0.event_file (SET filename = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\QueryList\XE_QueryTracker.xel')
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 30 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = ON
) GO
Notice here I am collecting the events sqlserver.sp_statement_completed and sqlserver.sql_statement_completed and only for databases greater then 7, you can add additional filters for SSMS etc. if needed.
The output of the XE is saved in the file location shown above. Next I needed a way to query the XE output file to extract only the timestamp and the actual query.
ALTER PROCEDURE GetRecentQueries @starttime DATETIME2
,@endtime DATETIME2
,@searchphrase VARCHAR(100)
AS
SELECT dateadd(mi, 330, eve_data.value('(//event/@timestamp)[1]', 'datetime2')) AS [Timestamp]
,eve_data.value('(//event/data[@name="statement"]/value)[1]', 'varchar(2000)') AS [SQL_Text]
FROM (
SELECT cast(event_data AS XML) AS Eve_Data
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\QueryList\*.xel', NULL, NULL, NULL)
) xed
WHERE eve_data.value('(//event/data[@name="statement"]/value)[1]', 'varchar(2000)') LIKE '%' + @searchphrase + '%'
AND dateadd(mi, 330, eve_data.value('(//event/@timestamp)[1]', 'datetime2')) BETWEEN @starttime
AND @endtime
And that’s it, we can now simply search for a phrase or table within the XE output and retrieve all matching queries.
PS: – In the time that it took me to write this post I have already used this approach once 😉
Please Consider Subscribing
