0

The query that I'm using:

SELECT 
    deqs.execution_count,
    deqs.last_execution_time,
    dest.objectid,  
    SUBSTRING(dest.[text], (deqs.statement_start_offset/2)+1,
                        ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                              ELSE deqs.statement_end_offset
                            END - deqs.statement_start_offset)/2)+1) AS statementtext 
    , dest.[text]   
FROM sys.dm_exec_query_stats AS deqs 
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where dest.[text] like '%foo%' 
order by deqs.last_execution_time DESC

That gives me 10 hits, but unfortunately I can't see what arguments were passed in. In regular SQL land I'd just attach the profiler, but that's not an option. This answer on SO makes me think it's not possible, which is hard to believe -- seeing proc arguments is an important debugging tool.

How can I see the last few procs that were run, along with their arguments on Azure?

2

Today, I think you would have to add logging to the stored procedures (or to the calling application). Trace / profiler are not supported on Azure SQL Database, and never will be.

At some point in the future, you should be able to use database-scoped Extended Event sessions. Bob Beauchemin talked about this here and here, and there is an official marketing statement from Microsoft's Tiffany Wissner here (but nothing technical):

If you have an Azure database and you happen to be on the most recent public version (13.0.15 as far as I can tell), you can start to see some evidence of her statements about Extended Events. For example, I have a context menu item that I can't do anything with yet:

Hints of Extended Events in Azure SQL Database

So, it's coming, just not yet. Until then I can't really think of a decent way to obtain runtime parameter values for called procedures unless you change the application to retrieve actual, post-execution plans and log the XML output somewhere. And if you're going to do that, well, you may as well just add the logging of the actual values - much easier to consume.

This answer, of course, is subject to change as it gets older.

  • "add logging to the stored procedures" -- wouldn't happen to have an article handy that has an approach that you like, would you? – jcollum Mar 31 '15 at 22:27
  • @jcollum off the top of my head and on mobile, no. I can think of a few ways to automate this but it depends on how many stores procedures you're talking about. If it's just a handful give me some time and I'll work something out. – Aaron Bertrand Mar 31 '15 at 23:28
  • No worries man, we're talking about 400 stored procs. You're thinking I could script out something that inserts a logging call into the top of the proc text and then drops and creates it with the next text? Makes sense. – jcollum Apr 1 '15 at 19:58
  • 1
    @jcollum I'm thinking you could generate the logging code and you could generate the alters (depending on length of the procedure code of course), but you may have to do some manual editing to piece them together. Since ALTER PROCEDURE has pretty loose formatting (e.g. you may or may not have BEGIN / END), automating the placement of the logging code at the beginning of the procedure could get tricky. What do you look for? BEGIN? Maybe. You might get the wrong BEGIN. And you can't just append to the end because parameter values may be changed locally. – Aaron Bertrand Apr 1 '15 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.