Create a TraceEvent Importer in SQL Nexus to replace ReadTrace functionality

[PiJoCoder]

Summary:
This proposal introduces a fully managed (.NET Framework 4.8) trace file importer plugin — Trace Event Importer (Managed) — to replace the dependency on the native ReadTrace.exe binary from RML Utilities.
The new importer

• reads both .trc (SQL Trace) and .xel (Extended Events) files,
• normalizes SQL text
• computes hash IDs for query grouping
• correlates Starting↔Completed events,
• tracks connections in trace
• computes time-interval aggregations
• and bulk-loads all data into the existing ReadTrace schema — ensuring all current RDLC reports work without modification.

It is implemented as a standard INexusImporter plugin discovered via DLL reflection, with mutual exclusivity logic to prevent both importers from running simultaneously. The plugin embeds its schema creation and post-load fixup SQL scripts as resources, eliminating external file dependencies, and uses the XELite NuGet package for reliable XEL file parsing. This removes the requirement for users to install RML Utilities and gives the SQL Nexus team full source control over the trace import pipeline.

[PiJoCoder]

Key Design Decisions

1. Same schema, same reports: The importer writes to the identical ReadTrace.* tables that ReadTrace.exe creates. All existing RDLC reports work without modification.

2. Embedded SQL scripts: CreateSchema.sql and PostLoadFixups.sql are embedded resources in the DLL, eliminating external file dependencies.

3. INexusImporter (not INexusFileImporter): The plugin handles its own file enumeration internally (same pattern as ReadTrace), receiving a file mask from the host and processing all matching files in a single DoImport() call.

4. XELite for XEL reading: Uses the Microsoft.SqlServer.XEvent.XELite NuGet package for reading Extended Events files, which handles the binary XEL format reliably.

5. BulkLoadRowset for writing: Uses the existing BulkLoadEx library (shared with other importers) for efficient bulk insert.

Implementation Plan

Phase 1: Core Pipeline

• Event model (TraceEvent.cs): Define a unified event structure that can represent both TRC and XEL events with fields for session, request, timestamps, duration, CPU, reads, writes, text data, etc.
• TRC file reader (TrcFileReader.cs): Read SQL Trace binary format, map trace event IDs to TraceEventType enum, extract fields by column ID.
• XEL file reader (XelFileReader.cs): Read Extended Events files via XELite, map XE event names (sql_batch_completed, rpc_completed, etc.) to the same TraceEventType enum.
• Event processor (EventProcessor.cs): Correlate Starting↔Completed events per session+request, track connections (login→logout), handle attention events.

Phase 2: Normalization & Hashing

• SQL text normalizer (SqlTextNormalizer.cs): Replace string literals with {STR}, numeric literals with {##}, normalize whitespace, handle quoted identifiers.
• Hash computation (HashComputer.cs): Compute stable 64-bit hash IDs for normalized SQL text (must produce consistent hashes for grouping).
• Special procedure detection (SpecialProcDetector.cs): Identify sp_executesql, sp_prepare, sp_prepexec, sp_cursoropen, etc. and extract the inner SQL text for normalization.
• Unique store (UniqueStore.cs): Deduplicate batches, statements, app names, login names, procedure names; assign sequential IDs.

Phase 3: Aggregation & Database Writing

• Time-interval aggregation (Aggregator.cs): Partition events into configurable time intervals (default 60 seconds), compute per-HashID partial aggregates (min/max/total for duration, reads, writes, CPU; counts for starting/completed/attention events).
• Bulk writer (BulkWriter.cs): Write all ReadTrace tables via BulkLoadRowset — metadata, reference data, unique text, fact tables, and aggregation tables.
• Schema deployment (CreateSchema.sql): CREATE SCHEMA, all tables matching the ReadTrace.exe layout, and the vwBatchPartialAggsByGroupTimeInterval view.
• Post-load fixups (PostLoadFixups.sql): Create primary keys, nonclustered indexes (using ReadTrace.exe naming convention for index hint compatibility), and reconcile ConnSeq/BatchSeq/ParentStmtSeq foreign key relationships.

Phase 4: Host Integration

• Plugin registration: Add ProjectReference in sqlnexus.csproj, register in AppConfig.xml.
• Mutual exclusivity with ReadTrace: Since both importers write to the same ReadTrace schema, only one can be active at a time.
  • Auto-disable ReadTrace when TraceEventImporter is enabled (and vice versa) at startup
  • Show a MessageBox informing the user when toggling between importers at runtime
  • Persist the selection via SaveImportOptions
• File mask alignment: Use *pssdiag*.xel and *LogScout*.xel masks (not *.xel) to avoid picking up system health, AlwaysOn, or SQLDiag XEL files that belong to the CustomXELImporter.
• Post-processing script: Declare ReadTracePostProcessing.sql as a post-script, and update the host's RunPostScripts to allow it for both ReadTrace and TraceEventImporter.
• Non-boolean options: Handle non-boolean options like "Aggregation interval (seconds)" gracefully in the UI (skip from checkbox menu).
• New importer defaults: Ensure new importers default to their constructor-defined values on first run (not false) when no saved options exist yet.

Phase 5: Schema Compatibility

The following objects must match what ReadTracePostProcessing.sql and the RDLC reports expect:

• View: ReadTrace.vwBatchPartialAggsByGroupTimeInterval — must return exactly 10 columns (StartTime, EndTime, TimeInterval, StartingEvents, CompletedEvents, Attentions, Duration, Reads, Writes, CPU) aggregated across all HashIDs per time interval.
• Index names: Must use the ReadTrace.exe naming convention (e.g., tblBatches_HashID not IX_tblBatches_HashID) because ReadTracePostProcessing.sql uses WITH (INDEX(...)) hints that reference these exact names.
• Column types and names: All table columns must match the types and names that ReadTracePostProcessing.sql stored procedures and the RDLC report datasets expect.

Phase 6: BulkLoadRowset Fix

• Schema-qualified table names: BulkLoadRowset.GetNewRow() wraps the table name in [brackets], which breaks for schema-qualified names like ReadTrace.tblBatches (produces [ReadTrace.tblBatches] instead of [ReadTrace].[tblBatches]). Fix to split on . and bracket each part separately.

Configuration

Option	Type	Default	Description
Enabled	bool	true	Enable/disable the importer
Drop existing ReadTrace tables	bool	true	Drop and recreate schema before import
Aggregation interval (seconds)	int	60	Time bucket size for partial aggregations

Testing Plan

1. Build & Deploy Verification

• Clean and rebuild the solution
• Confirm TraceEventImporter.dll is present in sqlnexus\bin\Release\
• Confirm all embedded resources are included (CreateSchema.sql, PostLoadFixups.sql)

2. Importer Discovery & UI

• Launch SQL Nexus, open the Import form
• Verify "Trace Event Importer (Managed)" appears under Options → Importers with Enabled checked
• Verify "ReadTrace (SQL XEL/TRC Files)" is auto-disabled when TraceEventImporter is enabled
• Toggle between the two importers — verify the MessageBox notification appears and the other is disabled
• Verify the "Aggregation interval (seconds)" option does NOT appear as a checkbox (non-boolean options are hidden)

3. Basic Import (XEL files)

• Point to a SQL LogScout output folder containing *LogScout*.xel files
• Click Import. Verify:
  • The XEL file(s) appear in the file list with progress bars
  • The ReadTrace schema and all tables are created
  • All indexes exist with the correct names (no IX_ prefix)
  • ReadTrace.vwBatchPartialAggsByGroupTimeInterval view exists
  • Data is populated in tblBatches, tblStatements, tblConnections, tblUniqueBatches, etc.

4. Basic Import (TRC files)

• Point to a PSSDIAG output folder containing *sp_trace*.trc files
• Verify the same successful import flow as with XEL files

5. Report Validation

• After import, open each ReadTrace report and verify no errors:
  • ReadTrace Main — time-series charts for batches, CPU, duration, reads, writes
  • Top Unique Batches — top-N table with clickable query templates
  • Batch Details (click a query template) — verify no index hint errors
  • Top Unique Statements — similar drill-down
  • Interesting Events — recompiles, autogrow, etc.
  • ReadTrace Warnings — any import warnings

6. Comparative Testing (TraceEventImporter vs ReadTrace)

This is the critical validation — import the same trace files with both importers and compare results.

Setup

1. Collect a representative set of trace files (both .xel and .trc if possible)
2. Create two separate databases (e.g., sqlnexus_managed and sqlnexus_readtrace)
3. Import with TraceEventImporter into sqlnexus_managed
4. Import with ReadTrace (SQL XEL/TRC Files) into sqlnexus_readtrace

Comparison Queries

-- Compare total event counts 
SELECT 'Managed' AS Source, COUNT(*) AS BatchCount 
FROM [sqlnexus_managed].ReadTrace.tblBatches 
UNION ALL 
SELECT 'ReadTrace', COUNT(*) 
FROM [sqlnexus_readtrace].ReadTrace.tblBatches

-- Compare unique batch counts 
SELECT 'Managed' AS Source, COUNT(*) AS UniqueBatches 
FROM [sqlnexus_managed].ReadTrace.tblUniqueBatches 
UNION ALL 
SELECT 'ReadTrace', COUNT(*) 
FROM [sqlnexus_readtrace].ReadTrace.tblUniqueBatches

-- Compare statement counts 
SELECT 'Managed' AS Source, COUNT(*) AS StmtCount 
FROM [sqlnexus_managed].ReadTrace.tblStatements 
UNION ALL 
SELECT 'ReadTrace', COUNT(*) 
FROM [sqlnexus_readtrace].ReadTrace.tblStatements

-- Compare connection counts 
SELECT 'Managed' AS Source, COUNT(*) AS ConnCount 
FROM [sqlnexus_managed].ReadTrace.tblConnections 
UNION ALL 
SELECT 'ReadTrace', COUNT(*) 
FROM [sqlnexus_readtrace].ReadTrace.tblConnections

-- Compare top-10 batches by total CPU (should be similar ranking) 
SELECT TOP 10 ub.NormText, SUM(b.CPU) AS TotalCPU, COUNT(*) AS Executes 
FROM [sqlnexus_managed].ReadTrace.tblBatches b 
  JOIN [sqlnexus_managed].ReadTrace.tblUniqueBatches ub 
  ON b.HashID = ub.HashID WHERE b.CPU IS NOT NULL 
GROUP BY ub.NormText 
ORDER BY TotalCPU DESC

-- Same query against ReadTrace database for comparison 
SELECT TOP 10 ub.NormText, SUM(b.CPU) AS TotalCPU, COUNT(*) AS Executes 
FROM [sqlnexus_readtrace].ReadTrace.tblBatches b 
  JOIN [sqlnexus_readtrace].ReadTrace.tblUniqueBatches ub 
  ON b.HashID = ub.HashID WHERE b.CPU IS NOT NULL 
GROUP BY ub.NormText 
ORDER BY TotalCPU DESC

-- Compare time interval aggregation totals 
SELECT 'Managed' AS Source, COUNT(*) AS Intervals, SUM(StartingEvents) AS TotalStarting, SUM(CompletedEvents) AS TotalCompleted 
FROM [sqlnexus_managed].ReadTrace.vwBatchPartialAggsByGroupTimeInterval 
UNION ALL 
SELECT 'ReadTrace', COUNT(*), SUM(StartingEvents), SUM(CompletedEvents) 
FROM [sqlnexus_readtrace].ReadTrace.vwBatchPartialAggsByGroupTimeInterval

Expected Differences

Some minor differences are acceptable:

• Hash IDs: Will differ (different hashing algorithms) — compare by NormText instead
• Normalization: Minor differences in how literals are parameterized (e.g., {STR} vs N'{STR}')
• Event counts: Should be within ~1-2% — differences may arise from how Starting events without matching Completed events are handled
• Aggregation boundaries: Time intervals may align slightly differently depending on the first event timestamp
• Connection tracking: ReadTrace.exe may handle MARS sessions differently

Red Flags (investigate if seen)

• Total batch count differs by more than 5%
• Top-10 queries by CPU have significantly different rankings
• Missing entire categories of events (e.g., zero statements, zero connections)
• Reports that work with ReadTrace but produce errors with TraceEventImporter

7. File Exclusion

• Place log_1.trc, log_2.trc, and *_blk.trc files alongside valid trace files
• Verify these are excluded from import (logged as excluded)
• Verify system_health, AlwaysOn_health XEL files are NOT picked up (only *pssdiag* and *LogScout* patterns)

8. Re-import & Drop Tables

• Import data, then import again with "Drop existing ReadTrace tables" checked
• Verify no duplicate data, schema is cleanly recreated

9. Disabled Importer

• Uncheck Enabled, re-import
• Verify trace files do not appear in file list and ReadTrace schema is not created

10. Cancellation

• Start an import of a large trace file set
• Click Stop during import
• Verify the import stops cleanly without leaving partial/corrupted data

[PiJoCoder]

Initial commit: 89d78ea