Hello I am new to ADS. I have basic tasks: analyze slow queries, monitor current server status and etc. I think that ADS extension: SQL Server Profiles should fit my need. Hence I installed. I don't have experience how to use it hence I browsed some documentation and able to Start/Stop it but I'm confused ... what's the next step? First ... I don't see the option to save results. I assume it should be XEL file saved locally ... but I don't have it .. Well .. my request is: could you please in a high level to explain me the workflow of usage of this extension. E.g. I optimized some query and want to compare performance and that type of task. How could I do it. As I mentioned above .. I'm stopped on the step of saving the session Thanks

When it comes to saving, I found that this is currently not supported, see https://github.com/microsoft/azuredatastudio/issues/20219 Bear in mind that the current version is 0.1.1, a number that suggests that it is far from feature complete. As for analysing slow queries that is a wide topic and a Profiler like this is just a tool. As long as you are only running queries in Azure Data Studio, you don't really need it, since you can wrap the queries in SELECT sysdatettime() to measure the execution time. But say that you are running an application, and you want to know that takes time. Then you can set up SQL Profiler, filter for that application and see what calls it are making. If you are running a stored procedure, you may want to see the individual statements inside in the procedure. There are events to capture this, but SQL Profiler in ADS does not yet seem to support them. There are other Profiler tools you can use for this purpose. Before I start to suggest too much: are you on Windows or are you on some other platform?

Hi @Susja Please note that SQL Server Profiler extension is still in Preview. You can still use profiler from SSMS or Extended Events to , for example, identify long running statements and do query optimization Regards Javier

How to use SQL Profiler in ADS

Accepted answer

Erland Sommarskog 103.9K Reputation points MVP

2023-08-12T17:41:12.34+00:00

When it comes to saving, I found that this is currently not supported, see https://github.com/microsoft/azuredatastudio/issues/20219 Bear in mind that the current version is 0.1.1, a number that suggests that it is far from feature complete.

As for analysing slow queries that is a wide topic and a Profiler like this is just a tool. As long as you are only running queries in Azure Data Studio, you don't really need it, since you can wrap the queries in SELECT sysdatettime() to measure the execution time.

But say that you are running an application, and you want to know that takes time. Then you can set up SQL Profiler, filter for that application and see what calls it are making.

If you are running a stored procedure, you may want to see the individual statements inside in the procedure. There are events to capture this, but SQL Profiler in ADS does not yet seem to support them. There are other Profiler tools you can use for this purpose. Before I start to suggest too much: are you on Windows or are you on some other platform?
Please sign in to rate this answer.

2 people found this answer helpful.
Susja 716 Reputation points

2023-08-12T19:31:49.42+00:00

hi @Erland Sommarskog

Appreciate your explanation.

Here's my point: I don't do any 'heavy' development but rather maintain various aspects of application. Hence I selected ADS because I notice that it has Terminal for bash, SQL tools and other nice 'light weighted' features that I could use not switching from one app to another.

I installed ADS on my Workstation which is Windows Datacenter 2019. My application is also on Windows. For some scripting I use bash from terminal and VS code editor. I only started to use a few days ago and I love it.

Now regarding Profiler: I understand that's completely new extension and I don't expect it be fully featured yet.

As you suggested .. I created a Filter based on TexData field and ran it for some while. Now I'd like to analyze it ... but it's not convenient IMO. If I could save it or export somehow that would be more practical.

What you could suggest?

Thanks for you help ... it's very clear and straightforward ..

Erland Sommarskog 103.9K Reputation points MVP

2023-08-12T19:56:05.8+00:00

There is nothing wrong in working with ADS. Personally, I think it looks uninspiring, and I use SSMS most of the time.

ADS is a newer kid on the block, and while it has been around some time, it does not have all the features that are in SSMS. (Then again, there are things that only work in ADS.) I did not look very closely, but I could find any support for Query Store. Query Store is the tool for performance tuning. You can work with Query Store from ADS by running queries directly against the QS tables, but for a beginner with Query Store, the reports in SSMS can be very helpful - and quite mind-blowing when you see what yo can do.

So I would suggest that you continue to use ADS, but that you still have SSMS around when ADS doesn't have what you need. In the specific case of profiling, you can use the XEvent Profiler in SSMS, or use the free-standing Profiler which uses the old Trace technology. This is one I use myself when I want to do something quick.

By the way, when it comes to saving the result from SQL Profiler in ADS, you said you wanted an .XEL file. But I wonder, is what you want a straight format of the grid? The XEL file is a bunch of XML which is not very practical to work with.

Finally, the reason I asked about your operating system is that this is one the things where ADS shines. SSMS only runs on Windows, but ADS also runs on Linux and Mac.

Susja 716 Reputation points

2023-08-12T23:46:10.2266667+00:00

@Erland Sommarskog

Appreciate your suggestions. Actually since I'm not heavy developer LOL but still need to use SQL tools for the last few years I was using Toad for SQL. I loved it very much. Currently we don't have license anymore for Toad plus Toad is a tool for SQL but ADS allows me SCM, bash and other things I like ..

Anyway I was using SSMS as well but still prefer ADS. Thinking of your suggestions ... I already installed extension in ADS for Query Store but forgot that I could play with it.

Now when you reminded me ... I will try to play with it instead of Profiler ... maybe it'll be more useful in my case.

Regarding XEL ... likely it's my fault. I meant to export grid result to any file format that could be convenient for analysis. Maybe Query store will be more friendly with export the grid.

Thanks again

Erland Sommarskog 103.9K Reputation points MVP

2023-08-13T07:52:38.03+00:00

I already installed extension in ADS for Query Store but forgot that I could play with it.

So there is a extension for Query Store? That's good to know. I entered Query Store in the search box for extension, but got no hit.

Susja 716 Reputation points

2023-08-13T08:23:15.7833333+00:00

Sorry

I misled you There’s no such extension

I went to SSMS and enabled Query Store

After that I selected in ADS my DB, right-click and selected Manage

Now I see different picture vs I saw before enabling

Does it mean that I could use it for 'profiling' or I misunderstood you?

Thanks

Erland Sommarskog 103.9K Reputation points MVP

2023-08-13T08:34:33.5233333+00:00

So Query Store and Profiler are two different things. Query Store collects execution statistics and saves the data per execution plan aggregated by a period, which by default is one hour. Say that you have an application where the user complain over poor performance. You can then go to Query Store to find which are the slow queries. And particularly which are the slow plans. Due to autostats and other things that trigger recompiles. a query can have many execution plans during the course of a day. Some may be slow, others may be fast.

Sometimes query that has been running well may start to misbehave gravely because of change of plans. Query Store permits you fix the urgent problem by forcing the old plan. Then you can spend time on a more close analysis on a more robust solution to the problem.

Profiling is more useful when you need "what is going on now". For instance, I use Profiler when I want to know what calls a certain function in the application emits, so I can work directly with that call from SQL. But you may also need Profiler if you have an issue where the same execution plan have big differences in execution time due to difference in input parameters. Since parameter values and individual executions are not tracked by Query Store, you need to use Profiler for such things.

Susja 716 Reputation points

2023-08-13T11:25:30.2433333+00:00

@Erland Sommarskog

You definitely has the skills to explain complex things in a way that even I could understand it LOL

I do have one teammate who is MVP for me because he is not only smart but could explain things in simple English.

Seriously .. I do know about Execution Plan but never put much attention to it because it was not easy for me to understand it.

Regarding your response: I am planning to take 3 Actions:

request ADS to add feature for saving Results grid to a file n a future releases

play with Query Store in ADS

play with Profiler in SSMS

follow your posts by subscribing to @Erland Sommarskog . I assume you post not only SQL related subjects

Highly appreciate your response

Erland Sommarskog 103.9K Reputation points MVP

2023-08-14T20:44:24.7533333+00:00

Performance tuning, including execution plans, is certainly something that takes some time to master, and a lot of that is experience.

Grant Fritchey's book SQL Server Execution Plans is good reading if you want to dive into that topic.
Sign in to comment

Share via

How to use SQL Profiler in ADS

1 additional answer