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
object_name [non unique]
I see from msdn that column
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