Extended Events Timezones

Page content

Foreword πŸ”—︎

I’ve helped answer another question that appeared on the SQL Server Slack:

Are timestamps in XE event files you view in SSMS local or server time?

To test this, I need a server in a different timezone than the client (SSMS). I find the quickest and most easy tool for that to be containers - more specifically, Docker.

If you don’t have the Docker installed - you can do so by using this guide, but if not, you can still follow along with the examples.

Preparing the environment πŸ”—︎

To create a new SQL Instance, we will just run the following command.
If you don’t have the latest SQL Server 2019 image, it will download it for you (roughly 1.5 GB).

docker run `
-e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=Password5' `
-e 'MSSQL_PID=Developer' `
-e TZ='America/Los_Angeles' `
-p 14338:1433 `
-d `
--name xeinstance `
mcr.microsoft.com/mssql/server:2019-latest

Command explanation πŸ”—︎

docker run The command to create and run the container
-e Environmental variables. The EULA and SA Password are required, *TZ is timezone necessary for our test
-p port mapping in format host:container. I’m using a different port on the host in case you already have a local instance. If you are based in the LA timezone, you can pick a different one, for example, Asia/Singapore.
-d Detached
–name Name of the container for easy reference
mcr.microsoft.com/mssql/server:2019-latest Image name:tag In our case latest CU of SQL Server 2019

If you need more information, you can run docker run --help or visit the Documentation. If the container is created successfully, its ID will appear. You can also check with the docker ps -a command.

Additional Docker references πŸ”—︎

No need for this now, but if you want to dig deeper into the Docker, here are some useful links:

Test in the SSMS πŸ”—︎

  • Connect to localhost,14338 (or .,14338 if you don’t like typing).
  • Select SQL Server Authentication
    • Login = sa
    • Password = Password5

Connection popup in SSMS

Open a new query window and run this to confirm the server time.

SELECT
   SYSDATETIMEOFFSET()
   , GETUTCDATE()

The timezone will match our config from earlier.

We don’t need to create a new XE session as the system_health will be running by default.

To open it, navigate to the Docker instance in Object Explorer.
Go to Management\Extended Events\Sessions\System_health and double-click the package0.event_file.

We can add an additional column to the default view as per the image instructions. Event file view in SSMS showing the local time

I am in the GMT+1 timezone, so we can see that the event file view shows time local to the SSMS client.

Digging deeper πŸ”—︎

Let’s look at the underlying data with the fn_xe_file_target_read_file DMF.

SELECT TOP (10)
   ca.eventDataXml.value('event[1]/@timestamp', 'datetime2(3)') AS tmpstmp
   , ca.eventDataXml
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) AS ef
CROSS APPLY
(
   VALUES (CAST(ef.event_data AS xml)) 
) ca(eventDataXml)
ORDER BY ef.timestamp_utc DESC

This gives us a sample of the events. We can see that there is only one underlying column in the UTC format. So SSMS is converting this automatically for us.