I recently released a Pluralsight Course Analyzing SQL Server Query Plans, and it is really doing great in terms of viewership and I have received some really great comments and feedback about this course. This week, I will be discussing different aspects of Analyzing SQL Server Query Plans. Today we will discuss Capture Query Plans Using Extended Events and Traces.
Real World – Profiler Fans
If you go out in the industry and ask people what is the tool they use the most besides SSMS, the answer will be Profiler. It is true that even though profiler has many issues, it has been the favorite tool for many and till today, you will see people using it to analysis their workload, query plans and various different aspect of the server using Profiler. I believe Profiler has created a space in the heart of the people such a way that it is impossible to replace. I think credit goes to simplicity of the tool which pretty much everyone understands. I believe the profiler is the root of the rest of the tools which are helping us to analyze query plans.
I have realized and accepted that Profiler is going to live forever and I am comfortable with it.
SQL Trace
SQL Trace is one such product, which I adored for what it did but I equally did not like it due to the complexity of implementing it. Many of my clients and customers did not like its implementation as it requires you to write a few queries or use the profile as a starting point. I have been doing SQL Server Performance Tuning consultation for a while and there are few shops where I have been working very frequently. There are many places, where I go after many years and I see the SQL Trace is working as I had initially configured. I think not many people know how to use this tool even if someone helps to configure it.
SQL Trace is now officially deprecated by Microsoft but is still supported.
Extended Events to Capture Query Plans
When the Extended Event started, my initial feeling was very similar to SQL Trace. However, every year it started to evolve and at this point in time, I am all a big fan of this feature. There are so many different things we can do with this tool and the best part is that it is pretty much caught up with recent advancement of the SQL Server.
I have been using this feature to capture lots of different information when my client complains about their server is running slower. I must say the Extended Event has got easier to implement as now we have Wizard to configure it. I must say that users love visual guidance and with the help of visual guidance of Wizard I have seen lots of people using it more in their implementation.
One more thing I must say about the Extended Event is that it is much lighter on the resources compared to SQL Trace and SQL Profiler. When implemented, it takes very little resource in the background and I have seen very little impact of its execution on the busy server.
Pluralsight Course Analyzing SQL Server Query Plans
In the Pluralsight Course Analyzing SQL Server Query Plans, I discuss about the profiler, SQL Trace and Extended Events. I explain to you how you can implement your first extended event and use it later on to identify the troublemaking query plans. The course is of only 2 hours and 30 minutes and if you have a Pluralsight subscription, you can watch it for free. If you do not have a Pluralsight subscription, you can still watch the course for FREE by signing up for a trial account.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
