Daniel Calbimonte

Is this the end of SQL Profiler?

June 13, 2016 by

Introduction

SQL Server Profiler is still a tool used to monitor our relational databases and our multidimensional ones. We used for performance and security purposes. However, in the SQL Server 2016, they announced that the SQL Profiler will be deprecated in future versions.


Figure 0. The SQL Profiler tomb

Why is SQL Server Profiler going to be replaced?

The problem with SQL Server Profiler is that it collects too much information and it takes a lot of time to filter the data that we really need. The Profiler also consumes too much resources.

If the SQL Profiler will be removed, what will replace it?

The answer is Extended Events.

A new hope: The Extended Events (XE)

XE will replace the SQL Profiler in the future versions. By the moment, SQL Server includes Profiler and XE.

The XEs is a feature included in SQL Server 2008. It is a lighter option that consumes less resources than the Profiler. It also can monitor more events than the Profiler. For example, you can monitor Azure, Column Store Events, InMemory OLTP, AlwaysOn Events. In fact, Profiler is not adding new events since the SQL 2008. All the new features are available to be monitored only in XE and not in Profiler.

In SQL Server 2008, it was only possible to create XE events using T-SQL. It was an unfriendly option that nobody liked. In the new SQL versions, you can easily create your XE using the SQL Server Management Studio in the UI.

In this demo, we will create a XE to detect the databases created.

Requirements

  • SQL Server 2014 installed.

Getting started

We will first create the XE and then we will create the database to verify that the event was stored and detected.

  1. In the SQL Server Management Studio (SSMS), go to Management>Extended Events:


    Figure 1. SSMS, Extended Events

  2. Expand the Extended Events tree view:


    Figure 2. The list of Event Sessions

  3. Right click on Sessions and select New Session Wizard (you could create your events using the new session option, but the wizard is always a good option for newbies):


    Figure 3. New Session Wizard

  4. The Session Wizard will help us to create the events for tuning, troubleshooting and performance analysis:


    Figure 4. Introduction to the Session Wizard

  5. In the Set Session Properties, specify a name for the session. You can also set the option to start the session at server startup:


    Figure 5. The name of the session

  6. There is an option to use existing templates. The templates include locks, batch samplings, query statistics, connection and Log File IO information:


    Figure 6. The event session templates available

  7. In this example, select the do not use a template option and press next:


    Figure 7. The option for templates

  8. A nice feature in XE that the Profiler does not have is the Search Event option. You can easily search your events here. In the Select Events to Capture, you can select the events to capture. You have more events than the Profiler here. For example there are Azure events and ColumnStore events. In this example select the database_created option. This option will generate an event if a SQL Server database is created:


    Figure 8. The list of XE that you can monitor

  9. The global fields contain the fields to be included when we monitor activities. You can store the application name used, CPU id used, database used, user name, NUMA node, etc. In this example, we will collect the client name, Database name and Window user name:


    Figure 9. Global fields

  10. You can create filters to your events using and, or with different operators. In this example we are specified that the SQL Server username should be different than Rajesh:


    Figure 10. The XE filters used

  11. The session storage allows you to store the events in a file. There are 2 options here. One option is to store a big amount of data and another store only the most recent data. The big amount of data is used when you select the save data to a file for later analysis. The other option is to store the information in the buffer memory. The second option is used to store only the most recent data. You can define the size and the number of files or events to store:


    Figure 11. Storage options

  12. The summary will display all the options set:


    Figure 12. Summary of the wizard options set

  13. The final wizard window shows the success message. There is an option to start the session immediately and to watch the data when it is captured. Check these options:


    Figure 13. Success message of XE

  14. To generate an event, we will create a database. Run a new query:


    Figure 14. Creating a new query

  15. Run the following query to create a database. This event will activate our event just created:

    CREATE DATABASE testDB1


    Figure 15. The T-SQL query to create a new database

  16. Right click the new session created and select the Watch Live Data:


    Figure 16. Watch the data

  17. You will be able to see the Event name and the date and time when it occurred:


    Figure 17. Database creation event

  18. You can also see the client application name used (SSMS), the database name and the Windows User. These are the fields selected in figure 9:


    Figure 18. The fields displayed

  19. If you want to store the session as a template (as we used to do in Profiler), you can do it by just right clicking on the session and selecting the Export Session option:


    Figure 19. Exporting as a template

  20. You can export the session as a XML file:


    Figure 20. The xml file template

  21. That is all. We created a XE and could collect the information of the Database created.

Conclusions

Extended Events will replace the SQL Server Profiler. They consume less resources because they are optimized. SQL Profiler stores too much information and it is hard to find what we need. XEs are easier to use and they do not generate as much information as the SQL Server Profiler. The XEs also contain more information and events than the Profiler.

References

For more information, refer to these links:

Images


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Server performance tuning

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

1,977 Views
  • Dominic Fitzpatrick

    This is not great. XE look really useful but what if you have a problem and you aren’t sure what you are looking for? Going through a massive “all queries” dump from Profiler has got us out of holes before due to unknown unknowns it has revealed.

  • Daniel Calbimonte

    Good question. You need to use dynamic management views. Check views like sys.dm_exec_query_stats and functions like sys.dm_exec_sql_text and sys.dm_exec_query_plan. I know that Profiler will be easier than complex queries, but it consumes excessive resources and if your SQL Server is already slow, it is not part of the solution.

    • Dominic Fitzpatrick

      Based on the provided Activity session template, it looks like we can get what we need from Extended Events. Also, they are scriptable and the filters are really good too.

      I can change the columns displayed in the Live Data feed and it seems to remember them. Do you know where that information is stored? It doesn’t seem to be part of the script, maybe it can be? Then we can script the whole thing onto production servers with no config required.