Performance
改進擴展事件目標文件的查詢
作為我們伺服器資產監控的一部分，我正在添加擴展事件以獲取警告、阻塞等，並且我想定期（每幾分鐘）查詢事件文件以收集數據。我一直在使用以下內容使用 xQuery 查詢數據，但它似乎很慢。我知道使用 fileoffset 作為優化選項，但除此之外，還有什麼方法可以更好地改進謂詞，如下所示？
SELECT event_data ,n.value('@timestamp', 'DATETIME2') DtTimeStamp ,n.value('(action[@name="collect_cpu_cycle_time"]/value)[1]', 'bigINT') CollectCpuCycleTime ,n.value('(action[@name="collect_system_time"]/value)[1]', 'DATETIME2') CollectSystemTime ,n.value('(action[@name="last_error"]/value)[1]', 'varchar(255)') LastError ,n.value('(action[@name="collect_system_time"]/value)[1]', 'datetime2') CollectSystemTime ,n.value('(action[@name="task_time"]/value)[1]', 'bigint') TaskTime ,n.value('(action[@name="client_app_name"]/value)[1]', 'varchar(255)') ClientAppName ,n.value('(action[@name="client_hostname"]/value)[1]', 'varchar(255)') ClientHostName ,n.value('(action[@name="database_name"]/value)[1]', 'varchar(255)') DatabaseName ,n.value('(action[@name="nt_username"]/value)[1]', 'varchar(255)') NtUserName ,n.value('(action[@name="server_instance_name"]/value)[1]', 'varchar(255)') InstanceName ,n.value('(action[@name="session_id"]/value)[1]', 'INT') SessionID ,n.value('(action[@name="client_pid"]/value)[1]', 'INT') ClientPID ,n.value('(action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') SQLText FROM ( SELECT CAST(event_data as XML) event_data FROM sys.fn_xe_file_target_read_file('C:\Temp\EE_QueryWarnings*.xel', null, null, null) ) ed OUTER APPLY ed.event_data.nodes('event') (n) WHERE n.value('@name', 'varchar(MAX)') = 'missing_column_statistics' AND n.value('@timestamp', 'DATETIME2') >= DATEADD(MINUTE,-10,GETUTCDATE());
另一種選擇是Microsoft.SqlServer.XEvent.Linq而不是 T-SQL 在 Powershell 或 .NET 應用程序中處理 XE 數據。QueryableXEventData 類可以直接處理來自目標文件或來自實時事件流的 XE 數據。
實時蒸汽源通常適用於相對不頻繁發生的事件（如在您的監控案例中），而文件源最適用於高頻事件案例（例如擷取 rpc 和批處理完成的事件以進行跟踪）。
假設您的目標是將事件插入到取證表中，下面是一個 PowerShell 範例和目標表 DDL。對於高頻事件，可以使用 SqlBulkCopy 而不是涓流插入。
桌子：
CREATE TABLE dbo.missing_column_statistics ( Timestamp datetimeoffset , Name varchar(100) , collect_cpu_cycle_time decimal(20, 0) , collect_system_time datetimeoffset , last_error int , task_time decimal(20, 0) , client_app_name varchar(100) , client_hostname varchar(100) , database_name sysname , nt_username varchar(100) , server_instance_name varchar(100) , session_id int , client_pid int , sql_text nvarchar(MAX) ); CREATE CLUSTERED INDEX cdx ON dbo.missing_column_statistics(Timestamp);PowerShell 腳本：
# class with actions/fields of interest class MissingColumnStatisticsEvent { [DateTimeOffset]$Timestamp [String]$Name [UInt64]$collect_cpu_cycle_time [DateTimeOffset]$collect_system_time [UInt32]$last_error [UInt64]$task_time [String]$client_app_name [String]$client_hostname [String]$database_name [String]$nt_username [String]$server_instance_name [UInt16]$session_id [UInt16]$client_pid [String]$sql_text MissingColumnStatisticsEvent($event) { $this.Name = $event.Name $this.Timestamp = $event.Timestamp $this.collect_cpu_cycle_time = $event.Actions["collect_cpu_cycle_time"].Value $this.collect_system_time = $event.Actions["collect_system_time"].Value $this.last_error = $event.Actions["last_error"].Value $this.task_time = $event.Actions["task_time"].Value $this.client_app_name = $event.Actions["client_app_name"].Value $this.client_hostname = $event.Actions["client_hostname"].Value $this.database_name = $event.Actions["database_name"].Value $this.nt_username = $event.Actions["nt_username"].Value $this.server_instance_name = $event.Actions["server_instance_name"].Value $this.session_id = $event.Actions["session_id"].Value $this.client_pid = $event.Actions["client_pid"].Value $this.sql_text = $event.Actions["sql_text"].Value } } # insert event data into target table Function Insert-MissingColumnStatisticsEvent($event) { $insertQuery = @" INSERT INTO dbo.missing_column_statistics ( Timestamp , Name , collect_cpu_cycle_time , collect_system_time , last_error , task_time , client_app_name , client_hostname , database_name , nt_username , server_instance_name , session_id , client_pid , sql_text ) VALUES ( @Timestamp , @Name , @collect_cpu_cycle_time , @collect_system_time , @last_error , @task_time , @client_app_name , @client_hostname , @database_name , @nt_username , @server_instance_name , @session_id , @client_pid , @sql_text ); "@ $connection = New-Object System.Data.SqlClient.SqlConnection($targetDatabaseConnectionString) $command = New-Object System.Data.SqlClient.SqlCommand($insertQuery, $connection) ($command.Parameters.Add("@Timestamp", [System.Data.SqlDbType]::DateTimeOffset)).Value = $event.Timestamp ($command.Parameters.Add("@Name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.Name ($command.Parameters.Add("@collect_cpu_cycle_time", [System.Data.SqlDbType]::Decimal, 20, 0)).Value = $event.collect_cpu_cycle_time ($command.Parameters.Add("@collect_system_time", [System.Data.SqlDbType]::DateTimeOffset)).Value = $event.collect_system_time ($command.Parameters.Add("@last_error", [System.Data.SqlDbType]::Int)).Value = $event.last_error ($command.Parameters.Add("@task_time", [System.Data.SqlDbType]::Decimal, 20, 0)).Value = $event.task_time ($command.Parameters.Add("@client_app_name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.client_app_name ($command.Parameters.Add("@client_hostname", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.client_hostname ($command.Parameters.Add("@database_name", [System.Data.SqlDbType]::NVarChar, 128)).Value = $event.database_name ($command.Parameters.Add("@nt_username", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.nt_username ($command.Parameters.Add("@server_instance_name", [System.Data.SqlDbType]::VarChar, 100)).Value = $event.server_instance_name ($command.Parameters.Add("@session_id", [System.Data.SqlDbType]::Int)).Value = $event.session_id ($command.Parameters.Add("@client_pid", [System.Data.SqlDbType]::Int)).Value = $event.client_pid ($command.Parameters.Add("@sql_text", [System.Data.SqlDbType]::NVarChar, -1)).Value = $event.sql_text $connection.Open() [void]$command.ExecuteNonQuery() $connection.Close() } # ############ # ### MAIN ### # ############ try { # load assemblies needed for QueryableXEventData $sharedPath = "C:\Program Files\Microsoft SQL Server\150\Shared" # path varies by SQL tool version $xeCore = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XE.Core.dll") $xeLinq = [System.IO.Path]::Combine($sharedPath, "Microsoft.SqlServer.XEvent.Linq.dll") Add-Type -Path $xeLinq Add-Type -Path $xeCore $xeSessionConnectionString = "Data Source=.;Initial Catalog=master;Integrated Security=SSPI;Application Name=XE ETL" # run in master database context for live stream $targetDatabaseConnectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI;Application Name=XE ETL" $sessionName = "EE_QueryWarnings" # get events from file target # $events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("DC:\Temp\$sessionName*.xel") # get events from live session $events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData( $xeSessionConnectionString, $sessionName, [Microsoft.SqlServer.XEvent.Linq.EventStreamSourceOptions]::EventStream, [Microsoft.SqlServer.XEvent.Linq.EventStreamCacheOptions]::DoNotCache) # Stream source will consume continuously until this script or XE session is stopped. # File source will read all XE files and exit. foreach($event in $events) { if($event.Name -eq "missing_column_statistics") { $missingColumnStatisticsEvent = New-Object MissingColumnStatisticsEvent($event) Insert-MissingColumnStatisticsEvent -event $missingColumnStatisticsEvent } } } catch { throw }