Azure Datawarehouse or SQL Pools and profiler equivalent...

  • I added the Profiler module to AZure Data Studio in our ("Microsoft Azure SQL Data Warehouse ) env to monitor performance memory / disk, etc just as I've done with SSMS profiler for years..  I know I 've used it before on another Azure product, but for this I get:

    "An exception occurred while executing a Transact-SQL statement or batch. ---> Catalog view 'dm_xe_database_sessions' is not supported in this version."

    And it wont run..   it tries to run then errors with the above. Anyone else have this happen,or fix or found a workaround to see the IO/ Memory and duration cost of your queries so I can make performance improvement. ?

    • This topic was modified 3 months, 3 weeks ago by  digdave7.
  • First things first, just so you know, despite the name, Profiler in Azure Data Studio isn't Profiler. It's a wrapper around Extended Events. You can expand and extend what gets captured and how if you just go to Extended Events directly rather than rely on the ADS GUI.

    That said, Extended Events aren't supported in Azure Synapse (new name for Azure SQL Data Warehouse). The only tool you have available there is queries against the DMVs outlined here. Here for a more detailed explanation.

    Instrumentality like Query Store and Extended Events just hasn't been ported to Synapse yet. The DMVs are the only game in town.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Hi Thanks.   I started in Azure 2 years ago, but the company shyed away from it so didnt learn more. I've been a MSSQL dba for years and surprised they dont have events to profile yet built into azure dw.. It helps a ton, and make performance tuning a lot easier. For now will have to stick with the dmv's you noted..

  • Yeah, I'm a little surprised they don't have either Query Store or Extended Events working on it. Seems like a necessity. The DMVs are nice, but cache dependent, so it's easy to miss things.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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