1

We get the data from system health XE from SQL server and load into table for later querying.

Part of that statements which gets pretty slow is

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)

Above is real slow when fetching events over 200k

Table structure is

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

The only index on the table is clustered indexon object_name [non unique]

I see from msdn that columnevent_data is nvarchar(max)

Update I am able to repro this situation for 42K records in system health which runs for 7-8 seconds and same goes over 50 minutes when there are over 0.5 million records to fetch from system health

link - PLan-- This on all versions starting SQL2014 and above where conversion warning is displayed.

Warning does not show up on SQL 2012 therefore i used trace flag 9481 but process is still slow

Plan without warning

Please suggest

  • 1
    Can you share the fast & slow execution plans at pastetheplan.com, and link to them here in your question? – Brent Ozar Oct 15 at 18:47
  • @BrentOzar, i will check if i can get one as repo since its restricted here to upload any file over the web. But i found only a small difference and have edited the question with that – BeginnerDBA Oct 15 at 22:22
  • @BrentOzar- Just updated the question with the plans – BeginnerDBA Oct 21 at 4:55
  • From sys.fn_xe_file_target_read_file(N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\system_health*.xel',NULL,NULL,NULL) -- functions are terrible at scaling. Is this something you can code inline? – Sean Brookins Oct 21 at 20:49
1
+100

I am trying to explain one of reason .

Currently object_name is Non unique Clustered index.

So when data is inserted, database engine will search for empty space in each page.Because data can be store any where.So this database search will take time.

Also since it is non unique,optimizer will append unique identifier in each row to make it unique.This index cost will increase.

There fore not only insert will be slow.Also while retrieving data Index will never be use, because of Index cost.

So choose Ever Increasing Column,Selective enough,Unique and light data type column as as 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

Create Non clustered index on object_name.

Here database engine will always know that data will be appended at end of page because id int identity(1,1) primary key.So insert will be fast.

2

You should check out This is how to shred Extended Event XML faster.

The gist is to

  • Put the XML into a variable.
  • Shred the XML rows into a temp table.

Also, see below answer by Martin Smith

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.