Problem
SQL Server is always making improvements so SQL Server can run faster and more efficient. There are often times new features are added that you might not be aware of, because there really isn’t much you need to do or sometimes nothing you need to do at all to take advantage of the improvements. In this article, we are going to do an overview of Intelligent Query Processing and how this improves Azure SQL.
Solution
Microsoft introduced the Intelligent Query Processing (IQP) feature, which enables an adaptive optimization mechanism in the query processor. It enhances the performance of current workloads with less implementation effort and does not require changes in code, making this feature very useful.
Microsoft’s IQP in Azure SQL database addresses these issues by enabling adaptive and feedback-driven optimization mechanisms in the query processor. Without requiring code changes, IQP improves performance and stability through a series of automated features.
Sample Dataset Generation
Let’s start by setting up a sample dataset and tables that will be used in our examples:
-- MSSQLTips.com (T-SQL)
-- Drop table if exists
DROP TABLE IF EXISTS Customer_Orders;
DROP TABLE IF EXISTS Customer_Products;
DROP FUNCTION IF EXISTS dbo.GetCustomerDiscount;
-- Create Customer_Products table
CREATE TABLE Customer_Products (
Product_ID INT PRIMARY KEY,
Product_Name NVARCHAR(50),
Product_Date DATE
);
-- Create Customer_Orders table
CREATE TABLE Customer_Orders (
Order_ID INT PRIMARY KEY,
Product_ID INT FOREIGN KEY REFERENCES Customer_Products(Product_ID),
Order_Date DATE,
Amount_USD DECIMAL
);
-- Insert sample data into Customer_Products Table
INSERT INTO Customer_Products (Product_ID, Product_Name)
SELECT
fn.Product_ID,
CONCAT('Product ', fn.Product_ID)
FROM (
SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Product_ID
FROM sys.all_objects AO CROSS JOIN sys.all_objects AB
) AS FN;
-- Insert sample data into Customer_Orders Table
INSERT INTO Customer_Orders (Order_ID, Product_ID, Order_Date, Amount_USD)
SELECT
o.Order_ID,
(ABS(CHECKSUM(NEWID())) % 5000) + 1,
DATEADD(DAY, -1 * (ABS(CHECKSUM(NEWID())) % 365), GETDATE()),
ROUND(RAND(CHECKSUM(NEWID())) * 5000 + 100, 2)
FROM (
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Order_ID
FROM sys.all_objects a CROSS JOIN sys.all_objects b
) AS o;Feature 1: Adaptive Joins
Problem: Cardinality estimation is wrong and bad join strategies make performance slower.
IQP Solution: Adaptive joins, select a join type dynamically at runtime depending on actual input rows.
Example Query:
-- MSSQLTips.com (T-SQL)
SELECT p.Product_Name, o.Order_Date
FROM Customer_Products p
JOIN Customer_Orders o ON p.Product_ID = o.Product_ID
WHERE p.Product_ID = 20;For fewer rows, a nested loop join is chosen as shown below. A hash join is used when it returns a larger set of rows. At the time of execution, IQP automatically determines this.
Note: This feature will work in compatibility level 150 or higher.
Feature 2: Memory Grant Feedback
Problem: Performance is affected when the memory grant size is incorrectly measured, resulting in inefficient memory usage. If the memory grant is not sufficient, then it spills to disk which can impact performance.
IQP Solution: To address repeated workloads, memory grants calculate the memory that is actually required for the query, and it updates the grant value for the plan.
Example Query with Memory Usage:
-- MSSQLTips.com (T-SQL)
SELECT Order_Date,
COUNT(*) AS Orders_Count,
SUM(Amount_USD) AS Total_Spent
FROM Customer_Orders
GROUP BY Order_Date
ORDER BY Total_Spent DESC;Run the above query several times and check the memory grants by using the query below:
-- MSSQLTips.com (T-SQL)
SELECT
a.query_id as queryid,
b.query_sql_text as sqlquery,
d.execution_type_desc as executiontype,
d.count_executions as executioncount,
d.avg_duration as avgduration,
d.avg_cpu_time as cputime
FROM sys.query_store_query a
JOIN sys.query_store_query_text b ON a.query_text_id = b.query_text_id
JOIN sys.query_store_plan c ON a.query_id = c.query_id
JOIN sys.query_store_runtime_stats d ON c.plan_id = d.plan_id;
Feature 3: Batch Mode on Rowstore
Problem: Analytical queries run on rowstore tables that get slower because of row-by-row processing.
IQP Solution: It enables batch-mode vectorized execution for the tables, which increases database performance as it helps in processing the data in vectors rather than processing one row at a time.
What is vectorized execution?
Each column is considered as a vector of values and batches of rows are processed by the database instead of row by row.
Query Example:
-- MSSQLTips.com (T-SQL)
SELECT Order_Date, AVG(Amount_USD) AS Avg_Order_Amount
FROM Customer_Orders co
JOIN Customer_Products cp ON co.Product_ID = cp.Product_ID
GROUP BY Order_Date;Feature 4: Scalar UDF Inlining
Problem: Scalar UDFs follow per-row execution and prevent parallelism, resulting in a significant performance increase.
IQP Solution: Using a UDF in your query as shown in the example below for a performance increase. It transforms the scalar UDF to a relational expression, resulting in enhanced performance of workloads and also effects CPU overhead as it will also be reduced.
Step 1: Create a scalar UDF
-- MSSQLTips.com (T-SQL)
CREATE OR ALTER FUNCTION dbo.GetCustomerDiscount (@amountusd DECIMAL)
RETURNS DECIMAL
AS
BEGIN
DECLARE @custdiscount DECIMAL;
SET @custdiscount = CASE
WHEN @amountusd > 5000 THEN @amountusd * 0.25
WHEN @amountusd > 2000 THEN @amountusd * 0.15
ELSE 0
END;
RETURN @custdiscount;
END;Step 2: Use the UDF in a query
-- MSSQLTips.com (T-SQL)
SELECT co.Order_ID, co.Amount_USD, dbo.GetCustomerDiscount(Amount_USD) AS Discount
FROM Customer_Orders co;
Feature 5: Approximate Query Processing
Problem: When calculating exact DISTINCT counts on large datasets (i.e. 1 billion row count) then a fast response time is critical.
IQP Solution: Provides approximate results where a perfect result is not required. In different dashboards and analytical analysis or reports, exact precision is not required. It trades a small margin of error but results in higher performance and helps in lowering the usage of resources.
Query Example:
-- MSSQLTips.com (T-SQL)
SELECT COUNT(DISTINCT Product_ID) AS Exact_Count
FROM Customer_Orders co;
-- APPROX_COUBT_DISTINCT with better performance
SELECT APPROX_COUNT_DISTINCT(Product_ID) AS Approx_Count
FROM Customer_Orders co;Feature 6: Parameter Sensitive Plan Optimization (PSP)
Problem: A cached plan can be optimal and performant for one parameter, but may be slower for other parameters.
IQP Solution: It automatically enables several cached plans for a similar query based on different parameter values. At runtime, SQL Server selects the most efficient query plan.
Create a parameterized query:
-- MSSQLTips.com (T-SQL)
-- Enable Parameter Sensitive Plan Optimization
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
-- Run with small input
EXEC sp_executesql N'
SELECT Order_ID, Amount_USD
FROM Customer_Orders co
WHERE Product_id = @Prod_ID',
N'@Prod_ID INT', @Prod_ID = 1;
-- Run with large input
EXEC sp_executesql N'
SELECT Order_ID, Amount_USD
FROM Customer_Orders co
WHERE Product_id >= @Prod_ID',
N'@Prod_ID INT', @Prod_ID = 1000;Check via:
-- MSSQLTips.com (T-SQL)
SELECT * FROM sys.query_store_plan WHERE query_id = <mention query id here>;Feature 7: Degree of Parallelism (DOP) Feedback
Problem: Over-parallelized queries can increase CPU overhead and impact performance.
IQP Solution: IQP automatically manages the CPU threads used by the query depending on previous performance. If DOP is too high, then it will increase CPU overhead, and if DOP is too low, hardware is not utilized properly, leading to slow queries. A fixed DOP will not be suitable for all kinds of loads, especially for queries that are repeatedly used with different inputs.
How does it work?
Let’s suppose a query ran with DOP (for example, with 8 threads), SQL Server will monitor and analyze the stats. If it checks that the query ran faster with 4 threads, then it will save the feedback, and the next time the query runs, it will run based on the saved suggestion.
DOP Feedback:
-- MSSQLTips.com (T-SQL)
-- Enable feedback, SET DOP_FEEDBACK TO ON
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;
-- Resource-intensive query
SELECT co.Product_ID, COUNT(*) AS Order_Count
FROM Customer_Orders co
GROUP BY Product_ID
OPTION (MAXDOP 0);Feature 8: Cardinality Estimation Feedback
Problem: At the time when the query compiles, if rows are estimated inaccurately, then it may generate an inefficient and low-performance query plan.
IQP Solution: It uses run-time row counts for the queries that will run in the future and adjust the query plans accordingly, resulting in better query plans and performance.
Enable Cardinality Estimation Feedback:
-- MSSQLTips.com (T-SQL)
ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = ON;Then run queries that may return variable row counts:
-- MSSQLTips.com (T-SQL)
SELECT *
FROM Customer_Orders co
WHERE co.Amount_USD > 1000;The SQL Server engine starts learning estimation patterns and adjusts the query behavior over time.
Monitor IQP Activity with Query Store
To analyze IQP in action use this query:
-- MSSQLTips.com (T-SQL)
-- Identify and analyze recently run queries
SELECT a.query_sql_text as query, d.avg_duration as avgduration, d.avg_cpu_time as avgcputime
FROM sys.query_store_query_text a
JOIN sys.query_store_query b ON a.query_text_id = b.query_text_id
JOIN sys.query_store_plan c ON b.query_id = c.query_id
JOIN sys.query_store_runtime_stats d ON c.plan_id = d.plan_id
ORDER BY d.avg_duration DESC;Use sys.dm_exec_query_plan_stats and sys.dm_exec_requests for real time visibility of stats.
Next Steps
- Upgrade the compatibility level of the database to 160 to use the full capabilities of IQP.
- Try IQP Features in real world scenarios and with larger datasets.
- Stay updated with latest features as IQP continues to evolve.
- Try to analyze slow running queries and identify where IQP can be used.
- Read more performance related articles.
