Lesson Learned #383: Loading multiple extended file using sys.fn_xe_file_target_read_file
Published Jun 25 2023 11:43 PM 2,184 Views

In today's technologically advanced world, data plays a crucial role in driving decision-making processes and gaining valuable insights. One of the popular methods for capturing and analyzing data in Microsoft SQL Server is through Extended Events (XE). XE allows administrators to collect information about various events occurring within the SQL Server environment.

 

However, managing and loading multiple extended event files can be a challenging task. In this article, we will explore an effective solution using the sys.fn_xe_file_target_read_file function to load multiple extended event files seamlessly.

 

Understanding Extended Events (XE)

 

Extended Events provide a powerful framework for capturing diagnostic and performance-related information within SQL Server. They offer a lightweight and flexible mechanism to track events and collect data without causing significant overhead. XE allows administrators to define specific events of interest, filter the captured data, and save it to files for further analysis.

 

Challenges of Loading Multiple Extended Event Files:

 

As the volume of data grows, SQL Server creates multiple extended event files to store the captured information efficiently. These files are usually saved in a designated target folder. However, when it comes to loading and analyzing these files, administrators often face the challenge of dealing with multiple files individually. Manually loading each file can be time-consuming and inefficient, especially when dealing with a large number of extended event files.

 

Solution: sys.fn_xe_file_target_read_file

 

To address the challenge of loading multiple extended event files, SQL Server provides the sys.fn_xe_file_target_read_file function. This function is specifically designed to read and load data from multiple extended event files into a single result set. By utilizing this function, administrators can streamline the process of analyzing and extracting insights from captured data efficiently.

 

Implementation Steps

 

1. Identify the target folder: Determine the folder where the extended event files are stored. This folder will serve as the source location for loading the files.

2. Create a table-valued function: Using T-SQL, create a table-valued function that leverages the sys.fn_xe_file_target_read_file function. This function will take the target folder as an input parameter and return a result set containing the combined data from all the extended event files.

3. Load the data: Execute the table-valued function with the target folder as the parameter. The function will automatically scan the specified folder, load the data from each extended event file, and present it as a consolidated result set.

4. Analyze the data: Once the data is loaded into the result set, you can perform various analysis operations using SQL queries. Explore the captured events, filter the data based on specific criteria, and derive valuable insights to drive decision-making processes.

 

Testing this option using Azure Blob Storage with an Azure SQL Database, we have found that we cannot use a wildcard to read all files with a specific extension or name. For this reason, we will use this small PowerShell script that will allow us to read each file with a specific extension one by one and load them into a table for further analysis.

 

First, let's select an article that generates a different extended event from the Azure SQL Database Audit Log - Lesson Learned #1: Capturing a TSQL Command Timeout - Microsoft Community Hub

 

To generate multiple files, we will start it, generate multiple timeout events, stop it, and start it again, generate timeout events again, and then stop it to generate at least two files in the defined Azure Blob Storage container.

 

Once, we have this, basically, using the following PowerShell Script we could read all the files. 

 

# Import the required module
Import-Module Az.Storage

# Set the connection variables
$storageAccountName = "StorageAccountName"
$storageAccountKey = "MES7cU8......cQJtZSR4INRdIgRe0WFCfxGxeQ=="
$containerName = "extended"
$blobName = "BlobName"
$resourceGroupName = "ResourceGroupName"
$subscriptionId = "99Z4XYZ-XXXX-XXXX-bbe2-XXXXXX"
$extensionFilter = "*.xel"
$bFirstTime = $true

#Connect-AzAccount -SubscriptionId $subscriptionId -UseDeviceAuthentication

$storageAccount = Get-AzStorageAccount -ResourceGroupName $resourceGroupName -Name $storageAccountName
$context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey

$blobs = Get-AzStorageBlob -Container $containerName -Context $context | Where-Object { $_.Name -like $extensionFilter }

$connectionString = "Server=tcp:servername.database.windows.net,1433;Database=dbname;User ID=UserName;Password=Password"

$sqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()

        $sqlCommand.CommandText = "DROP TABLE IF EXISTS [_xyz_log]"
        $sqlCommand.ExecuteNonQuery()

foreach ($blob in $blobs) {
    $filePath = $blob.ICloudBlob.Uri.AbsoluteUri
    Write-Output "Full Path: $fullPath"
    Write-Output "Size: $($blob.Length) bytes"
    Write-Output "-------------------------------------"
   
    try {
        if($bFirstTime) 
        {
          $sqlCommand.CommandText = "SELECT * into [_xyz_log] FROM sys.fn_xe_file_target_read_file('$filePath', NuLL, NULL,null)"
          $sqlCommand.ExecuteNonQuery()
          $bFirstTime=$false
        }
        else
        {
         $sqlCommand.CommandText = "INSERT INTO [_xyz_log] SELECT * FROM sys.fn_xe_file_target_read_file('$filePath', NuLL, NULL,null)"
         $sqlCommand.ExecuteNonQuery()
        }
        Write-Output "Load file succesfully: $filePath"
    }
    catch {
        $errorMessage = $_.Exception.Message
        Write-Output "Failed file: $filePath"
        Write-Output "Err Msg: $errorMessage"
    }
}

$sqlConnection.Close()




 

Remember that to read the file generated you need to create a Datacase Scoped Credential 

 

CREATE DATABASE SCOPED CREDENTIAL 
[https://storageaccountname.blob.core.windows.net/extended] 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', 
SECRET = 'sv=2022-11-02&ss=bfqt&srt=sco&sp=rwdlacupiyx&se=2023-06-26T14:00:54Z&st=2023-06-26T06:00:54Z&spr=https&sig=1lWIFTj......................k%3D'

 

Version history
Last update:
‎Jun 25 2023 11:43 PM
Updated by: