sql profiler_这是SQL Profiler的结尾吗？

最新推荐文章于 2024-11-12 10:56:30 发布

culuo4781

最新推荐文章于 2024-11-12 10:56:30 发布

阅读量139

点赞数

文章标签：数据库 python java 大数据 mysql

原文链接：https://www.sqlshack.com/is-this-the-end-of-sql-profiler/

版权

sql profiler

介绍 (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.

SQL Server Profiler仍然是用于监视我们的关系数据库和多维数据库的工具。我们用于性能和安全性目的。但是，在SQL Server 2016中，他们宣布SQL Profiler将在以后的版本中弃用。

为什么要替换SQL Server Profiler？ (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.

SQL Server Profiler的问题在于它收集了太多信息，并且需要大量时间来筛选我们真正需要的数据。探查器还会消耗太多资源。

如果将删除SQL事件探查器，将用什么替代它？ (If the SQL Profiler will be removed, what will replace it?)

The answer is Extended Events.

答案是扩展事件。

一个新希望：扩展事件（XE） (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.

XE将在将来的版本中替换SQL Profiler。目前，SQL Server包括Profiler和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.

XE是SQL Server 2008中包括的一项功能。它是一种较轻的选项，与Profiler相比，它消耗更少的资源。它也可以监视比事件探查器更多的事件。例如，您可以监视Azure，列存储事件，InMemory OLTP，AlwaysOn事件。实际上，自SQL 2008以来，Profiler并未添加新事件。所有新功能仅可在XE中监视，而在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.

在SQL Server 2008中，只能使用T-SQL创建XE事件。这是一个没人喜欢的不友好选择。在新SQL版本中，您可以使用UI中SQL Server Management Studio轻松创建XE。

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

在此演示中，我们将创建一个XE来检测创建的数据库。

要求 (Requirements)

SQL Server 2014 installed.
已安装SQL Server 2014。

入门 (Getting started)

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

我们将首先创建XE，然后将创建数据库以验证是否已存储和检测到该事件。

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

在SQL Server Management Studio（SSMS）中，转到“管理”>“扩展事件”：

Figure 1. SSMS, Extended Events 图1. SSMS，扩展事件
Expand the Extended Events tree view:

展开扩展事件树视图：

Figure 2. The list of Event Sessions 图2.事件会话列表
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 图3.新建会话向导
The Session Wizard will help us to create the events for tuning, troubleshooting and performance analysis:

会话向导将帮助我们创建事件以进行调整，故障排除和性能分析：

Figure 4. Introduction to the Session Wizard 图4.会话向导简介
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 图5.会话名称
There is an option to use existing templates. The templates include locks, batch samplings, query statistics, connection and Log File IO information:

有一个使用现有模板的选项。模板包括锁，批量采样，查询统计信息，连接和日志文件IO信息：

Figure 6. The event session templates available 图6.可用的事件会话模板
In this example, select the do not use a template option and press next:

在此示例中，选择“不使用模板”选项，然后按下一步：

Figure 7. The option for templates 图7.模板选项
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:

XE中探查器没有的一个不错的功能是“搜索事件”选项。您可以在此处轻松搜索事件。在“选择要捕获的事件”中，可以选择要捕获的事件。您这里的事件比事件探查器更多。例如，有Azure事件和ColumnStore事件。在此示例中，选择database_created选项。如果创建了SQL Server数据库，则此选项将生成一个事件：

Figure 8. The list of XE that you can monitor 图8.您可以监视的XE列表
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:

全局字段包含我们监视活动时要包括的字段。您可以存储使用的应用程序名称，使用的CPU ID，使用的数据库，用户名，NUMA节点等。在此示例中，我们将收集客户端名称，数据库名称和Window用户名：

Figure 9. Global fields 图9.全局字段
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:

您可以使用和或通过不同的运算符为事件创建过滤器。在此示例中，我们指定了SQL Server用户名应不同于Rajesh：

Figure 10. The XE filters used 图10.使用的XE过滤器
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:

会话存储使您可以将事件存储在文件中。这里有2个选项。一种选择是存储大量数据，另一种仅存储最新数据。当您选择将数据保存到文件中以供以后分析时，将使用大量数据。另一种选择是将信息存储在缓冲存储器中。第二个选项仅用于存储最新数据。您可以定义要存储的文件或事件的大小和数量：

Figure 11. Storage options 图11.存储选项
The summary will display all the options set:

摘要将显示所有设置的选项：

Figure 12. Summary of the wizard options set 图12.向导选项集的摘要
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 图13. XE成功消息
To generate an event, we will create a database. Run a new query:

为了生成事件，我们将创建一个数据库。运行一个新查询：

Figure 14. Creating a new query 图14.创建一个新查询
Run the following query to create a database. This event will activate our event just created:

运行以下查询以创建数据库。此事件将激活我们刚刚创建的事件：

CREATE DATABASE testDB1 创建数据库testDB1

Figure 15. The T-SQL query to create a new database 图15.创建新数据库的T-SQL查询
Right click the new session created and select the Watch Live Data:

右键单击创建的新会话，然后选择“观看实时数据”：

Figure 16. Watch the data 图16.观察数据
You will be able to see the Event name and the date and time when it occurred:

您将能够看到事件名称以及发生的日期和时间：

Figure 17. Database creation event 图17.数据库创建事件
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:

您还可以看到使用的客户端应用程序名称（SSMS），数据库名称和Windows用户。这些是在图9中选择的字段：

Figure 18. The fields displayed 图18.显示的字段
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:

如果要将会话存储为模板（就像我们在Profiler中所做的那样），则可以通过右键单击会话并选择“导出会话”选项来实现：

Figure 19. Exporting as a template 图19.导出为模板
You can export the session as a XML file:

您可以将会话导出为XML文件：

Figure 20. The xml file template 图20. xml文件模板
That is all. We created a XE and could collect the information of the Database created.

就这些。我们创建了一个XE，可以收集创建的数据库的信息。

结论 (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.

扩展事件将替代SQL Server Profiler。由于优化，它们消耗更少的资源。 SQL Profiler存储了太多的信息，很难找到我们需要的信息。 XE易于使用，并且生成的信息不如SQL Server Profiler多。 XE还包含比Profiler更多的信息和事件。