(Slow work)
As part of a cool side project I am working on, I decided to test the observer overhead of Extended Events for myself on my own kit. Whilst numerous other bloggers have done this, I have decided to test this on all the current versions of SQL Server, still (some barely) in support. Why bother? I want to determine if there has been a change in observer overhead over the years, using a typical XE session.
I also chose to focus on just a file target, as again, other bloggers have pointed out that this method has less overhead, not to mention I only have limited time.
I will preface this by saying this was one of the most tedious things I have ever done in my career. I had to:
- Run the tests (5 times for each VU profile per SQL Server version)
- Restore the TPCC database after each set of results (when switching XE on, for example)
- Ensure the CPU was being recorded by Perfmon (every 5 seconds) per run
- Collate the results into a table
- Collate the CPU results into CSVs
- Run the CPU results through a Python script to give me the mean with yerrorbars (lower and upper bound if 95% confidence interval)
- Run these results through GNUplot to create the graphs for CPU
- Write this blog post
High-level goals
The high-level goals were simple:
- What is the performance impact of XE on the application in a highly transactional workload?
- What is the impact on the CPU of XE on the database server during a highly transactional workload?
- Is there any difference between the following versions of SQL Server?
- SQL Server 2016
- SQL Server 2017
- SQL Server 2019
- SQL Server 2022
The workload
As it so happens, I am quite familiar with HammerDB as I have used it extensively in the past on the following platforms:
- SQL Server
- Oracle
- Postgres
Due to the DeWitt clause, the actual Transactions Per Minute and New Orders Per Minute results will not be published; only the performance change over baseline with XE switched on will be reported. We are just interested in the impact of XE.
I opted to go with the following set of tests:
- 1 Virtual user – (No keying and thinking time)
- 4 Virtual users – (No keying and thinking time)
- 8 Virtual users – (No keying and thinking time)
- 16 Virtual users – (No keying and thinking time)
- 32 Virtual users – (No keying and thinking time) – Excessive contention beyond this level
- 2048 Virtual users (Keying and thinking time) – 5 async clients per user
For each configuration, I ran five workloads to give me statistically valid results using a 95% confidence interval. The TPCC database was built specifying 640 warehouses and was around 90GB in size. The two VMs involved in the testing were an application server with 8 vCPUs and 24GB of RAM, and the database server was a 16-core box with 32GB of memory. The storage was NVME. I specified 1 minute of ramp-up time with a test period of 4 minutes, with a checkpoint occurring at the end. I also built in an extra minute after each run, as I was using autopilot and wanted the servers to settle down between runs.
When not using keying and thinking time, HammerDB performs as much work as possible so SQL Server is driven as fast as it can for the given workload. With keying and thinking time enabled, HammerDB simulates how a human would operate the application, hence the much higher amount of virtual users. Please note, I didn’t go above 2048 (5 async clients per user) as my app server did not have enough memory to make this work. In an ideal world, I would simulate much more to mimic a normal workload.
SQL Server considerations
The following versions of SQL Server were tested:
- SQL Server 2016 – 13.0.6300.2
- SQL Server 2017 – 14.0.3490.10
- SQL Server 2019 – 15.0.4430.1
- SQL Server 2022 – 16.0.4205.1
As per the HammerBD best practices guide, I picked out the following:
exec sp_configure 'show advanced options', '1' exec sp_configure 'recovery interval','32767' exec sp_configure 'max degree of parallelism','1' exec sp_configure 'lightweight pooling','1' exec sp_configure 'max worker threads', 3000
Disclaimer: I have no idea what ‘lightweight pooling’ is or what it does other than reading the MS documentation. I enabled it in faith that this is needed for a reason.
I also ensured I grew out the data and log files and set file growth to sane values.
With regards to the Extended Events Session itself, I aimed to capture the following events:
- rpc_completed
- sql_batch_completed
- sql_statement_completed
Filters were:
- Duration > 5 seconds
- Skip system databases
Other options:
- EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
- TRACK_CAUSALITY=OFF
- MAX_MEMORY=2048KB
I also decided to use the latest compatibility level at all times. I could have kept the same compatibility level across all tests to keep everything aligned, but as I only intended to do this testing once, I opted to use the current.
Impact on the application
With my XE session enabled, the following tables show the impact on the TPM and NOPM scores, respectively. I did not graph these as I would need to use the actual numbers, which is prohibited.
Transactions Per Minute
New Orders Per Minute
I have no explanation for why the NOPM per minute for 2048 users (including keying and thinking time) is reduced compared to TPM. However, I did notice the writelog waits were higher (2.4ms vs 0.4ms) for 2048VU on the SQL Server 2022 runs (I didn’t check the other versions). Did these writelog waits get in the way? In any case, I would expect the same values for TPM. I would have had to observe this on other versions of SQL Server to be sure, or retest everything.
Broadly speaking, my observations show the following:
- There is an impact on the application performance with XE enabled
- As the workload increases and the number of sessions increases, the impact reduces overall on the HammerDB results
- It is what it is
- I saw anomalous results on 2017, where performance actually increased on 8 VU and 32 VU
Impact on CPU
I have 48 result sets for CPU usage, and I am not going to post them all as this post is already dry enough 🙂
Let’s look at the CPU overhead for SQL Server 2022 as an example. CPU data was collected in 5-second intervals (noisy, yes). For 2048 virtual users, I opted to display every third CPU sample as the duration of the test was longer.
1 Virtual User
4 Virtual Users
8 Virtual Users
16 Virtual Users
32 Virtual Users
2048 Virtual Users (Keying and thinking time)
Here is a table of the CPU impact across the versions:
Here are my observations:
- On these particular workloads on my setup, SQL Server 2022 had the least overhead when an XE session was running
- Some anomalous results highlighted in grey, where the CPU overhead was reduced
Why did I not push the CPU to 100%? Because at this stage, I want to observe the effect on a non-cataclysmic workload. In my experience, when the CPU is pinned at 100% (not hovering near it), all bets are off, and the server is unresponsive until something happens.
So, how ‘busy’ was SQL Server under its heaviest loads? You can take a look at the wait stats using sp_blitzfirst and draw your own conclusions. I recommend you click on the images to expand them.
1 Virtual User
4 Virtual Users
8 Virtual Users
16 Virtual Users
32 Virtual Users
2048 Virtual Users
As an aside , the largest batch/reqs sec I saw was approximately 26,000. In addition, I saw the following wait type in the top 3-4 across all workloads.
WAIT_ON_SYNC_STATISTICS_REFRESH
On the larger workloads, there were quite a few of these waits and for up to 600ms. I didn’t see anything in the HammerDB documents regarding dealing with this (disabling auto stats, async stats updates, etc), so I didn’t intervene. As far as testing is concerned, the settings were the same throughout the testing, so they are aligned.
The transaction log was our ultimate bottleneck (writelog), which can be typical for an OLTP-type workload. It is worth mentioning that ‘per-core per second’ did not really go very high for any wait stats (not even above one).
Things to do differently next time
- Maybe do a second set of results this time, keeping the compatibility level the same across the workloads
- Do more runs using keying and thinking time, which is more realistic
- Lots of SQL Servers do not push 26,000 batch reqs per second or push the CPU to 70% and beyond
- Layer in more/different extended events
- Push the CPU higher
- Probably by reducing the number of vCPUs on the VM
Conclusion
Enabling Extended Events does impact performance when running a heavy OLTP workload like HammerDB. In my testing, the impact on the application was highest on SQL Server 2022. However, the differences between the different versions of SQL Server are not vast.
Conversely, in my testing, the CPU overhead on the SQL Server itself was lower on SQL Server 2022 than on previous versions.
If your server is already hovering at the 95% mark in a busy OLTP environment, it is possible that switching on XE could tip you into the danger zone.
[…] storage required by any underlying internal objects associated with change tracking. As per my earlier post about Extended Events, I will perform some HammerDB tests. As I will not be using “keying and […]