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). The quickest and easiest tool for that is containers, more specifically Docker. I've written before about why I reach for containers.
Don't have Docker installed? Grab it from this guide. You can still follow along with the examples even without it.
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 | Environment variables. The EULA and SA Password are required; TZ sets the timezone needed 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, the 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 Docker, here are some useful links:
- Microsoft SQL Server Docker Image
- Documentation of the mssql Environment variables
- Great Docker quickstart guide by
Andrew Pruski (DBA From The Cold)
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. If you're new to capturing errors this way, see Investigating errors with Extended Events.
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. There is only one underlying timestamp column, and it is stored in UTC. SSMS converts it to your local time on the fly.
So, back to the original question: the Extended Events file viewer shows time local to the SSMS client, not the server. The data on disk is always UTC.
Thank you for reading