The other day I had a very stressed developer coming to see me… he had been in the middle of coding something when his PC pulled the ever inventive “Blue Screen of Death” trick on him.
He wouldn’t have been quite so bothered but he had been working on new piece of SQL, hadn’t been saving his work as he went along, and had not yet written it to disk as a procedure but was working in ad-hoc mode whilst he fine tuned it.
Predictably the code was quite complex and relatively lengthy, therefore meaning that he would really struggle to re-create it at speed or to necessarily remember a couple of tuning quirks he had included along the way.
He came to me quite downbeat to tell me of his trauma in the hope that I was logging all code run against the servers in some sort of mass and constant data collection.
Sadly for him, I wasn’t. However, not all was lost…
The thing that the developer didn’t realise was that ALL code IS logged by SQL Server itself. As long as you’re fast enough to react and you’re not limited on RAM in your server then chances are that SQL Server still has quite a lot of information held about everything you’ve been running no matter how trivial or complex it may have been.
Never forget the SQL Server Plan Cache!
SQL Server notes every single plan it has ever had to create, along with the text, last execution dates etc. Loads of information about what’s been going on recently.
Therefore, in this case, I simply asked him for a key word or two that would likely make his code unique (for example the name of a variable he may have used or alias). This alone WOULD work but you have to remember that the plan cache could be several GB in a server and therefore doing a text search on a cross applied function through several GB would be virtual suicide. Again, don’t despair.
All I did was ask the developer roughly (very roughly will do) when he executed the code. In this case it was within the last hour.
from sys.dm_exec_query_stats q
cross apply sys.dm_exec_sql_text(q.sql_handle) s
where last_execution_time >= dateadd(hh, -1, current_timestamp)
and s.text like '%@kindOfUniqueVariableOrOtherPhrase%'
And that was all that was required. The output came back with, in this case, 40 different records. It took less than a couple of seconds to scan the records, find his code, and then simply copy and paste it out of the results.
One happy developer and one handy hint next time you accidentally close a window or suffer an outage.