Extended Events Timezones
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:
- Microsoft SQL Server Docker Image
- Documentation of the mssql Environment variables
- Great Docker quickstart guide by Andrew Pruski - @dbafromthecold
Test in the SSMS 🔗︎
- Connect to
localhost,14338(or.,14338if you don’t like typing). - Select SQL Server Authentication
- Login = sa
- Password = Password5
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.
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.
