Overview of SQL server 2022:
Microsoft SQL Server 2022 is a powerful relational database management system (RDBMS) developed by Microsoft. It is the latest version in the SQL Server series and comes with various improvements and new features.
The SQL server 2022 is Microsoft's latest major release SQL server product, it serves hybrid data platform with providing advancements in security, performance, availability, and data virtualization.
This new release help's organization which looking to, modernize their data infrastructure from older of SQL Server versions, with the cloud connected features to users who aiming to facilitate hybrid data scenarios.
Organization can enhance application performance without requiring any changes in their existing code, additionally it provides data integrity protection through blockchain capabilities and delivers increased scalability and availability for critical data.
Business continuity through Azure: Link feature in Azure SQL Managed Instance. Continuously replicate data to and from the cloud.
Seamless analytics over on-premises operational data: Breaking the wall between operational and analytical stores. Azure Synapse Link
Visibility over your entire data estate: Manage and govern your entire data estate by Microsoft Purview Integration
The most secure database over the last 10 years: Use an immutable ledger to protect data from tampering.
Industry-leading performance and availability: Built-in Query intelligence which Accelerate query performance and tuning with no code changes
Intelligent Query Processing:
"Intelligent Query Processing" in SQL Server is a set of advanced techniques and features designed to enhance the performance of your database queries and make them more efficient. This powerful capability can significantly improve the way SQL Server handles your queries, leading to faster and more reliable results.
In this concept we are mainly focusing on three techniques.
- Parameter sensitive optimization
- Cardinality Estimate feedback
- Degree of parallelism feedback
Parameter Sensitive plan optimization:
When it comes to database management, performance is key. SQL Server 2022 introduces a game-changing feature, "Parameter Sensitive Plan Optimization," designed to revolutionize query performance tuning. In this blog post, we'll dive into what this feature is all about and how it can significantly enhance the database performance.
Before we delve into the solution, let's understand the problem: parameter sniffing. In SQL Server, when a query is compiled, the optimizer generates a query plan tailored to the specific parameter values used during compilation. This optimized plan is then cached and reused for subsequent executions of the same query. While this approach is efficient when parameter values remain consistent, it can lead to performance issues when they vary widely.
Consider a scenario where a query plan optimized for one set of parameter values performs admirably but degrades in performance when used with a different set. This is where parameter sniffing comes into play, and it can result in suboptimal execution plans, ultimately impacting query performance.
SQL Server 2022 introduces Parameter Sensitive Plan Optimization as the solution to the age-old problem of parameter sniffing.
Parameter-sensitive Plan (PSP), a.k.a. Parameter-sniffing problem refers to a scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values.
SQL Server 2022 detects situations where parameter sniffing might be problematic. During query compilation or recompilation, the optimizer considers multiple potential plan shapes based on different parameter values.
SQL Server 2022 can generate and cache multiple execution plans for the same query, each optimized for a specific set of parameter values. When a query is executed, the system selects the most appropriate plan based on the supplied parameters, reducing the likelihood of suboptimal performance.
Here we quickly jump into the practical execution of query/stored procedure to explore the PSP.
The stored procedures, can be call with different parameters and every parameter may need different indexes, but the stored procedure has just one execution plan and one index.
The SQL Server 2022 has a new feature for this problem. We can store more than one execution plan for a stored procedure.
Here I have took a https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak database for this demo.
Restore the DB backup with name as PSP and populated the data along with that rebuild an index associated with the table by below script to get performance improvement.
For the workload test using below query to create new procedure, check the database is at dbcompat 150 and clear the query store.
Use the below query with a parameter GetStockItemsbySupplier 2 and execute procedure, note the query execution time which is less than a minute if we run twice will not require compile, here the query plan uses the Index seek.
In a different query window run the below query with a parameter GetStockItemsbySupplier 4 and check the query plan will uses a Clustered Index Scan and parallelism.
Now run the previous query which took an index seek for query plan, it took execution quite longer than previous execution, also note the query plan uses clustered index scan at next run, this leads to a bad execution plan.
Solve the problem in SQL Server 2022 with no code changes
Let's get this workload to run much faster and consistently using PSP optimization. Execute the below query and change the database to dbcompat 160 and clear the query store.
Run the query with a parameter GetStockItemsbySupplier 2 again. Should finish in a few seconds, with index seek.
Run the query with a parameter GetStockItemsbySupplier 4 again, check the query plan will uses a Clustered Index Scan.
Run workload parameter GetStockItemsbySupplier 2 again and see that it now finishes again in a few seconds. Observe the perfmon counters and see consistent performance.
Predicate cardinality range
The concept of "predicate cardinality range" refers to the range of possible values that a predicate (a condition applied to filter data) can take on when executed with different parameter values. This concept is integral to PSP Optimization as it helps the query optimizer make more informed decisions about query plan selection based on the actual parameter values used.
In a SQL query, a predicate is a condition that filters rows from a table. Cardinality refers to the estimated number of rows that will satisfy a predicate. The optimizer uses cardinality estimates to choose the most efficient query execution plan.
Optimizer can choose a query execution plan with the knowledge of the predicate cardinality range, that which will be suited for the specific parameter values provided, thereby avoiding suboptimal plans that might cause the parameter sniffing.
Predicate Cardinality Range emerges as a pivotal factor in the success of PSP Optimization. It enables SQL Server to tailor query execution plans to the real-world variability in data distribution, guaranteeing optimal performance regardless of the parameter values in play.
PSP optimization feature can create three ranges that would represent low, medium, and high cardinality ranges, as shown in the following diagram.
The dispatcher bucketizes the cardinality values into three predicate cardinality ranges at compile time.
The PSP optimization feature generates a shell plan known as a dispatcher plan
In a dynamic database environment, where query conditions and parameters may undergo frequent changes, Predicate Cardinality Range proves indispensable to database administrators. With PSP Optimization and this innovative approach to cardinality estimation, SQL Server 2022 establishes a new benchmark for query performance and adaptability.
Limitations:
- Increased Query Compilation Time: PSP Optimization may result in slightly longer query compilation times as the optimizer evaluates multiple potential plans. In most cases, this increase is negligible, but for very complex queries, it could be noticeable.
- Resource Consumption: Generating and maintaining multiple execution plans for a single query can consume additional memory and CPU resources.
- Potential for Plan Cache Bloat: Storing multiple execution plans for a single query can lead to plan cache bloat, where the cache consumes more memory. This can be managed through proper plan cache maintenance.
In conclusion, Parameter Sensitive Plan Optimization significantly improves in SQL Server with several advantages by addressing parameter sniffing issues and improving plan adaptability that DBAs can benefit from these enhancements by allowing SQL Server 2022 to automatically adapt to different parameter values, resulting in more stable and efficient query execution. However, it also introduces some complexities and potential resource consumption. Understanding when and where to leverage this feature is key to making the most of its benefits while mitigating potential drawbacks.
