Post reply

Monitor spids on SQL Azure instance? Can we do it?

  • December 10, 2017 at 9:17 am

    #341225

    Hi All,

    Can I do the below for SQL Azure instance/db?

    Suppose, I want to take a look at what is running right now . For example I want to run sp-who2 or execute sys.dm_exec_requests or sp_whoisactive to see if there is a blocking or want to see the waits
    or want to create an extended events for a particular spid to monitor waits for a specific spid, Can we do that for sql azure db? if so, how can I monitor ?

    I want to capture sp_whoisactive every 5 mins into a table to see what the SPIDs are doing and how long they have been executing.I used it quite often for monitoring onPrem SQL Instances.
    Can I do the same for SQL Azure instance/dbs?

    Thanks,

    Sam

  • December 10, 2017 at 11:45 am

    #1971481

    I don't know if this exists for Azure SQL, but it does for Azure Data Warehouse. They have a nice Audit/Threat Detection feature you can enable that allows you to view all the activity on the SQL instance. You can tap into it via API and download the logs. It also includes the executed statements, durations, effected rows, volumes, client info (i.e.: SSMS, PowerBI,...) etc. If someone is running a query, dropping a table, simply making a connection or failing one, you can look at it and log it to Blob storage and or export it out to Excel for further auditing/reviews.

    I don't think it gives you all the bits whoisactive gives you, but gives you something to chew on. It also does not include the execution plans though, but they do exist with other monitoring tools.

    With that, you can also setup notifications based on those logs where it can notify you via email. For example, you can have it alert you if any user fails to connect to the instance X times.

  • December 11, 2017 at 6:35 am

    #1971528

    You can run Extended Events to capture this type of behavior. If you want to schedule a script to run, you can do that through Azure Automation.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 11, 2017 at 7:16 am

    #1971542

    The wonderful Adam Machanic released an update sp_whoisactive which works with Azure PAAS last night

    http://www.whoisactive.com/downloads/

  • December 12, 2017 at 11:40 am

    #1971802

    Thanks Grant & Anthony. Will try those options. Thanks a lot.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply