-
제목
ApexSQL Audit performance usage - Testing case using Extended Events auditing technology -
설명
ApexSQL Audit performance usage when using Extended Events auditing technology -
해결 방안
DISCLAIMER: The following sample represents an insight of resource utilization during overload cases, insinuated with a huge auditing data processing using ApexSQL Audit. Testing that was performed below provides metrics obtained by the usage of ApexSQL Audit and SQL Server within a specific testing environment with no extra processes running except for SQL engine and auditing. The obtained results can be considered as a reference guide in building the expectations on how ApexSQL Audit can impact the given environment.
Testing environment specification
Audited instance machine specification:- OS: Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
- SQL Server version: Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Developer Edition (64-bit)
- Processor: Intel(R) Xeon(R) Platinum 8171M CPU @ 2.60GHz, 4 Core(s), 8 Logical Processors
- Memory: 32 GB
- Storage: Standard SSD of 100 GB
- 10 Gigabit Ethernet Network Card
Central Repository instance machine specification:- OS: Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)
- SQL Server version: Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Jun 15 2019 00:26:19 Developer Edition (64-bit)
- Processor: Processor Intel(R) Xeon(R) Platinum 8272CL CPU @ 2.60GHz, 1 Core(s), 2 Logical Processors
- Memory: 16 GB
- Storage: Standard SSD of 100 GB
- 10 Gigabit Ethernet Network Card
Performance testing on the audited instance machine
The following data provides an insight of the testing result in a specific environment and displays overall hardware consumption in specific scenarios. Showing the graph, results in which a dedicated auditing agent is referred to as ApexSQL Audit processor Distributed, which is a core component for data processing and collecting on the audited instance machines.
Testing case using the Extended Events auditing technology - low impact on resource utilization
Input parameters for testing:- Testing duration: 10 minutes
- Auditing technology: Extended Events
- Auditing configuration: GDPR
- SQL query average length: 80 chars long
- Number of executed SQL events: nearly 274,000 events, which stand for 457 transactions per second (TPS)
Graph below showing CPU utilization during test run with Extended Events auditing technology:
Graph below showing memory utilization during test run with Extended Events auditing technology.
Conducted testing shows low impact on CPU and memory utilization regarding ApexSQL Audit Distributed processor when test SQL query was executed simultaneously from 1 application within 10 minutes.
Workload during test run per total SQL Server resources (test SQL query vs ApexSQL Audit SQL query):
Comparison of applications that executed SQL queries during test run per total SQL Server resources:
Workload during test run per total SQL Server resources (test SQL query vs ApexSQL Audit SQL query):
Comparison of applications that executed SQL queries during test run per total SQL Server resources.
Testing case using the Extended Events auditing technology - high impact on resource utilization
Input parameters for testing:- Testing duration: 6 minutes
- Auditing technology: Extended Events
- Auditing configuration: GDPR
- SQL query average length: 80 chars long
- Number of executed SQL events: nearly 656,000 events, which stand for 1.817 transactions per second (TPS)
Graph below showing CPU utilization during test run with Extended Events auditing technology:
Graph below showing memory utilization during test run with Extended Events auditing technology.
Conducted testing shows the limit where higher impact on CPU can be expected and slightly higher memory utilization regarding ApexSQL Audit Distributed processor when test SQL query was executed simultaneously from 6 applications within 6 minutes.
Workload during test run per total SQL Server resources (test SQL query vs ApexSQL Audit SQL query).
Comparison of applications that executed SQL queries during test run per total SQL Server resources:
Workload during test run per total SQL Server resources (test SQL query vs ApexSQL Audit SQL query):
Comparison of applications that executed SQL queries during test run per total SQL Server resources
