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.
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.
- SQL Server 2014 installed.
We will first create the XE and then we will create the database to verify that the event was stored and detected.
In the SQL Server Management Studio (SSMS), go to Management>Extended Events:
Figure 1. SSMS, Extended Events
Expand the Extended Events tree view:
Figure 2. The list of Event Sessions
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
The Session Wizard will help us to create the events for tuning, troubleshooting and performance analysis:
Figure 4. Introduction to the Session Wizard
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
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
In this example, select the do not use a template option and press next:
Figure 7. The option for templates
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
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
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
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
The summary will display all the options set:
Figure 12. Summary of the wizard options set
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
To generate an event, we will create a database. Run a new query:
Figure 14. Creating a new query
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
Right click the new session created and select the Watch Live Data:
Figure 16. Watch the data
You will be able to see the Event name and the date and time when it occurred:
Figure 17. Database creation event
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
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
You can export the session as a XML file:
Figure 20. The xml file template
That is all. We created a XE and could collect the information of the Database created.
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.
For more information, refer to these links:
- Extended Events
- Create an Extended Events Session Using Query Editor
- SQL Server Extended Events Targets
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
Latest posts by Daniel Calbimonte (see all)
- Functions and stored procedures comparisons in SQL Server - February 20, 2017
- Functions vs stored procedures in SQL Server - February 14, 2017
- How to generate random SQL Server test data using T-SQL - January 26, 2017