9

My aim is to audit all queries on an instance, who's running them and from where, how many reads, in order to consolidate/migrate on another instance.

Figured out the best way is via Extended Events to .xel files, over 2 weeks, copy those and analyse on my well-specd desktop. Set up the capture with the minimal number of details (fields), so really can't make it smaller than this - aprox 12GB in 12 files. Used "Merge Extended Event Files" in SSMS to load the .xel files and process as detailed in https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/advanced-viewing-of-target-data-from-extended-events-in-sql-server?view=sql-server-2017

Is this really the best way to do it ? Is there a better tool to load the files ? It's really slow with SSMS 17.7 - example: with only 4 filters so far (before getting into grouping and aggregates) it took over 12h to get to crunch through 50% of the logs.

1
  • Have you tried doing this with TSQL : sys.fn_xe_file_target_read_file loading it to a table and doing filtering there? May 16, 2018 at 10:14

2 Answers 2

9

For large XE trace files, I use custom tooling with QueryableXEventData. I've found this to be much faster than XML parsing in T-SQL.

Below is a basic PowerShell that imports selected fields and actions from an rpc_completed event trace into a table in 10K batches. You'll need to include an Add-Type command for the Microsoft.SqlServer.XE.Core.dll and Microsoft.SqlServer.XEvent.Linq.dll assemblies, which will be in your SQL Server installation folder with the exact location will varying depending on SQL version and chosen install location.

$SharedPath = "C:\Program Files\Microsoft SQL Server\140\Shared";
$SqlInstanceName = "";

$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;

if( [System.IO.File]::Exists($xeCore) )
{
    Add-Type -Path $xeCore;
}

Note that there are separate Fields and Actions collections in the PublishedEvent class so you'll need to extract values from the appropriate connection.

# create target table
$connectionString = "Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand(@"
CREATE TABLE dbo.rpc_completed(
      event_name sysname
    , timestamp datetimeoffset
    , statement nvarchar(MAX)
    , username nvarchar(256)
);
"@, $connection)
$connection.Open()
[void]$command.ExecuteNonQuery()
$connection.Close()

# data table for SqlBulkCopy
$dt = New-Object System.Data.DataTable
[void]$dt.Columns.Add("event_name", [System.Type]::GetType("System.String"))
$dt.Columns["event_name"].MaxLength = 256
[void]$dt.Columns.Add("timestamp", [System.Type]::GetType("System.DateTimeOffset"))
[void]$dt.Columns.Add("statement", [System.Type]::GetType("System.String"))
[void]$dt.Columns.Add("username", [System.Type]::GetType("System.String"))
$dt.Columns["username"].MaxLength = 128
$dt.Columns["statement"].MaxLength = -1

$events = new-object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("D:\TraceFiles\Log\rpc_completed*.xel")

# import XE events from file(s)
$bcp = New-Object System.Data.SqlClient.SqlBulkCopy($connectionString)
$bcp.DestinationTableName = "dbo.rpc_completed"
$eventCount = 0
foreach($event in $events) {
    $eventCount += 1
    $row = $dt.NewRow()
    $dt.Rows.Add($row)
    $row["event_name"] = $event.Name
    $row["timestamp"] = $event.Timestamp
    $row["statement"] = $event.Fields["statement"].Value
    # username is a collected action
    $row["username"] = $event.Actions["username"].Value
    if($eventCount % 10000 -eq 0) {
        $bcp.WriteToServer($dt)
        $dt.Rows.Clear()
    }
}
$bcp.WriteToServer($dt) # write last batch
Write-Host "$eventCount records imported"
6
  • Thank you, the import seems fast enough. How would you add more fields to this, say username, database_name and client_app_name ? I've tried to add based on the existing logic, but end up with NULL in these new columns. May 17, 2018 at 17:19
  • 1
    @RazvanZoitanu, I updated my answer with how to extract action data.
    – Dan Guzman
    May 17, 2018 at 18:27
  • Thanks again, accepted the answer as I was able to import all I need, and fast! Looks like SSMS can only use 2GB of memory, and that's why it's so slow.. Last questions, getting this exception, any ideas on how to fix it ? Seems like all the data is in there, did max length on "statement" shows 235k chars. May 18, 2018 at 12:26
  • Data at the root level is invalid. Line 1, position 1. At line:15 char:5 + $row["statement"] = $event.Fields["statement"].Value + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], XmlException + FullyQualifiedErrorId : System.Xml.XmlException May 18, 2018 at 12:26
  • 1
    So it seems there is a bug in QueryableXEventData with regards to accessing the collect statement action of the module_end event. The workaround is to either not collecting the statement with the module_end event or skip the statement column when importing that event. In this case, there were other detail events in the trace so the module_end statement was redundant.
    – Dan Guzman
    May 18, 2018 at 16:57
3

The fastest way to read and parse XEvents currently is by using the new XELite library from Microsoft. You can find it here: XELite in NuGet.org If you are PowerShell guy you can use the updated SQLServer module which is based on the XELite library. You can find it in the gallery: SQLServer.

1
  • 2
    Where can i find the documentation regarding XELite. ?
    – Shettyh
    Dec 18, 2019 at 6:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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