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.
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.
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.
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.
Images
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 writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023