When it comes to measuring and comparing the performance of SQL Server, it can be challenging to interpret and compare performance counters and DMV information, especially when activity levels differ significantly. However, there are ways to overcome this challenge and gain a better understanding of SQL Server performance.
One approach is to use a Cellular Automation (CA) application that provides a precise workload that can be repeated, measured, and observed. This CA application includes various “Pattern Generators” that stress different areas of a SQL Server instance. These generators use different techniques such as CPU load, IO-bound queries, and in-memory tables and stored procedures. All three generators are mathematically provable implementations of “Conway’s Game of Life,” making them effective tools for benchmarking and comparing the relative performance of different SQL Servers.
Benchmarking Process Overview
The benchmarking process involves three main steps:
- Initialization: This step clears any historic patterns associated with the session and populates a “Merkle” table with the coordinates for an initial set of new patterns. Different initial pattern complexity levels can be set using a “Stress Level” parameter.
- Stress Testing: The “Pattern Generator” procedures create the specified number of new patterns using different techniques that stress different areas of a SQL instance. The Merkle table holds all the patterns generated for a specific session.
- Results Verification: The “Display Pattern” procedure shows the highest generation of patterns for the current session, allowing visualization and confirmation of the final pattern.
Example: Physical/Virtual Server Performance Comparison
Let’s consider an example where we compare the CPU, IO, and in-memory performance of a physical server and a virtual server. Both servers are running SQL Server 2014 instances with SP1, have a maximum memory set to 8 GB, and default parallelism. To minimize background activity, other SQL instances and SQL Agent services were stopped.
The benchmarking process involved running the CA setup script and executing a test with different configurations. The resulting graphs showed a comparison between the virtual and physical server processing time for the same workload. Overall, the physical server performed better in terms of CPU, IO, and in-memory workload types.
Example: Hekaton OLTP/OLAP Performance Analysis
In another example, we explore the performance of Hekaton, the in-memory OLTP engine in SQL Server. A whitepaper by Microsoft titled “In-Memory OLTP – Common Workload Patterns and Migration Considerations” provides insights into Hekaton performance. However, this article goes a step further by providing code samples to support the points made in the whitepaper.
The test workload pattern used in this example shows that increasing index sizes and logical reads have a negative impact on Hekaton performance. However, the more procedure calls per second, the better Hekaton performs compared to traditional SQL. The Cellular Automation application is used to place similar demands on Hekaton and native SQL Server tables and stored procedures. The results of the test show that Hekaton is significantly faster than native SQL procedures, especially for OLTP bias workloads.
Example: Synthetic Transactions
Another approach to measuring SQL Server database application performance is through synthetic transactions. These transactions provide a precise and easy-to-implement way to measure performance from both CPU and IO perspectives.
A synthetic transaction involves a mathematical workload that is performed, and the duration of the workload is measured. This can be done by running a T-SQL setup script and scheduling a SQL Agent job to run the workload at regular intervals. The workload can be tuned for OLTP/OLAP bias and the cost of execution.
Conclusion
Understanding SQL Server performance is crucial for optimizing database applications. By using tools like the Cellular Automation application and synthetic transactions, you can gain valuable insights into the performance of your SQL Server instances. These insights can help you identify areas for improvement and make informed decisions to optimize your database applications.
Disclaimer: The pattern generators used in the Cellular Automation application are designed for isolated testing environments. Use them at your own risk.