Totally Random Tips: Using a SQL Query to Get All Recent Queries
One of the coolest things about databases is how much they can tell us about themselves, so long as we know how to ask. Recently, a SQL Server application database I work with took a total nose dive and wouldn't respond until the service was stopped and started again. As part of my due diligence - to make sure it wasn't something I had done - I queried the query stats for the time frame of the failure. Thankfully, I was able to confirm it was not caused by something I had done but was indeed the fault of the application itself - something to do with issuing 600+ procedures which in turn create several other procedures on the fly on a server configured "frugally". The SQL I used is below: SELECT [text].TEXT QueryText, [stats].last_elapsed_time, [stats].last_execution_time FROM sys.dm_exec_query_stats [stats] CROSS APPLY sys.dm_exec_sql_text( [stats].sql_handle ) [text] order by [stats].last_elapsed_time desc