June 5, 2023 at 5:47 pm
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. ?
June 6, 2023 at 11:50 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2023 at 6:40 pm
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..
June 7, 2023 at 7:01 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 16, 2023 at 8:48 pm
This was removed by the editor as SPAM
October 16, 2023 at 10:50 pm
I am not sure profiler would be of much use to you even if it did support it, Synapse doesn't work the same way as a traditional SQL server does. An individual query can be split up and executed over multiple compute nodes and every column under the hood is not part of a single table, but separate objects themselves.
You should be able to look at execution plans.
Do you have a particular performance problem you are trying to solve?
October 17, 2023 at 12:52 pm
I am not sure profiler would be of much use to you even if it did support it, Synapse doesn't work the same way as a traditional SQL server does. An individual query can be split up and executed over multiple compute nodes and every column under the hood is not part of a single table, but separate objects themselves.
You should be able to look at execution plans.
Do you have a particular performance problem you are trying to solve?
On that note, the execution plans are also radically different in Synapse. They are text only too, so be prepared.
"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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply