随着事件量的增加，查询系统健康数据的速度变慢。

Question

我们从SQL服务器获取系统健康XE中的数据，并将其加载到表中供以后查询。

其中一部分语句变得相当慢，

INSERT INTO TABLE1
      (
         [object_name]
         ,[col1)
       )

SELECT [Object_name], CAST (event_data as XML) AS eventcol1

FROM sys.fn_xe_file_target_read_file (N'D:\SQL\system_health*.XEL', NULL,NULL,NULL)

当获取超过200 k的事件时，上面的速度是非常慢的。

表结构

CREATE TABLE [dbo].[Table_1](
    [object_name] [nvarchar](60) NOT NULL,
    [col1] [xml] NULL
) ON [DEMO] TEXTIMAGE_ON [DEMO]
GO

表中唯一的索引是clustered indexon object_name 非唯一

我从msdn中看到列event_data是nvarchar(max)。

更新我可以在系统健康中复制42K记录的这种情况，运行时间为7-8秒，当有超过50万条记录要从系统健康状况中获取时，同样的情况也要超过50分钟。

链接- PLan--在所有启动SQL2014及以上版本的版本上都会显示转换警告。

警告不会出现在SQL 2012上，因此我使用了跟踪标志9481，但是进程仍然很慢。

毫无预兆地计划

请建议

Answer 1

我试着解释其中一个原因。

目前，object_name是Non unique Clustered index。

因此，当数据被插入时，database engine将搜索每个page.Because数据中的空空间，可以存储任何where.So --数据库搜索将需要时间。

另外，由于它是非唯一的，优化器将在每一行中附加唯一标识符，使其unique.This索引成本增加。

因此，不仅插入将是slow.Also，而检索数据索引将永远不会使用，因为索引成本。

因此，选择Ever Increasing Column、Selective enough、Unique和light data type列作为Clustered index

CREATE TABLE [dbo].[Table_1](
    id int identity(1,1) primary key
    [object_name] [nvarchar](60) NOT NULL,
    [col1] [xml] NULL
) ON [DEMO] TEXTIMAGE_ON [DEMO]
GO

在Non clustered index上创建object_name。

在这里，database engine将始终知道数据将被追加到页面末尾，因为id int identity(1,1) primary key.So插入速度很快。

Answer 2

你应该去看看这是如何更快地分解扩展事件XML.。

要旨是

• 将XML放入变量中。
• 将XML行分解为临时表。

另外，见下面马丁·史密斯的回答。

• 使用XML读取器优化计划