Exercise - Parameter Sensitive Plan

Completed

Note

This exercise uses the following githib repository:

https://github.com/microsoft/sqlworkshops-sql2022workshop/tree/main/sql2022workshop/03_BuiltinQueryIntelligence/pspopt

Before you continue, make sure that you complete the prerequisites and setup.

Imagine that you're the SQL Server database administrator for Wide World Importers. Part of your role is to keep up to date with new features in products. You learn that SQL Server 2022 is generally available and you need to evaluate the product. The Wide World Importers business unit noted inconsistent runtimes, which has direct effect on the customer experience. You see that the issue is with how SQL Server parameterizes the queries. Looking through the notes, you find that SQL Server 2022 optimized how the engine parameterizes the queries with Parameter Sensitive Plan optimization.

In this exercise, you evaluate the Parameter Sensitive Plan optimization. You create a demo for a business unit to build a use case on why you should upgrade to SQL Server 2022 and Compatibility Mode 160.

Prerequisites

  • SQL Server 2022 Evaluation or Developer Edition.
  • A virtual machine (VM) or computer with at minimum 2 CPUs and 8 GB of memory. For some of the exercises, it's best to have a machine with 8 CPUs or more.
  • The latest version of SQL Server Management Studio (SSMS).
  • Download ostress.exe from https://aka.ms/ostress. Install using the RMLSetup.msi file. Use all defaults for the installation.

Set up the exercise

  1. Create a directory called c:\sql_sample_databases to store backups and files.

  2. Download a backup of a customized version of the WideWorldImporters sample database for the PSP exercise from https://aka.ms/wwi_pspopt. Save it in the c:\sql_sample_databases directory.

    Note

    If you try to restore the default sample WideWorldImporters database, you can use the restorewwi.sql, populatedata.sql and rebuild_index.sql scripts to customize the database for the exercise.

  3. Restore the customized version of the WideWorldImporters sample database to your SQL Server 2022 instance using SSMS. You might need to change the directory paths for the location of your backup and where you restore the database files.

    USE master;
    GO
    DROP DATABASE IF EXISTS WideWorldImporters;
    GO
    -- Edit the locations for files to match your storage
    RESTORE DATABASE WideWorldImporters FROM DISK = 'c:\sql_sample_databases\wwi_pspopt.bak' with
    MOVE 'WWI_Primary' TO 'c:\sql_sample_databases\WideWorldImporters.mdf',
    MOVE 'WWI_UserData' TO 'c:\sql_sample_databases\WideWorldImporters_UserData.ndf',
    MOVE 'WWI_Log' TO 'c:\sql_sample_databases\WideWorldImporters.ldf',
    MOVE 'WWI_InMemory_Data_1' TO 'c:\sql_sample_databases\WideWorldImporters_InMemory_Data_1',
    stats=5;
    GO
    

    Important

    If you have permission issues to restore the backup, you can try to copy the backup into the default data folder for your SQL Server installation and try the restore again. You need to edit the restore script accordingly. The default for most instances is C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA.

  4. Create a new procedure to be used for the workload test using the following script.

    USE WideWorldImporters;
    GO
    CREATE OR ALTER PROCEDURE [Warehouse].[GetStockItemsbySupplier]  @SupplierID int
    AS
    BEGIN
    SELECT StockItemID, SupplierID, StockItemName, TaxRate, LeadTimeDays
    FROM Warehouse.StockItems s
    WHERE SupplierID = @SupplierID
    ORDER BY StockItemName;
    END;
    GO
    
  5. Run the following script from SSMS to ensure the WideWorldImporters database is at dbcompat 150 and clear the query store.

    USE WideWorldImporters;
    GO
    ALTER DATABASE current SET COMPATIBILITY_LEVEL = 150;
    GO
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    GO
    ALTER DATABASE current SET QUERY_STORE CLEAR;
    GO
    

Exercise - Viewing PSP optimization for a single query execution

You want to understand what is happening in the Wide World Importers database. Look at the PSP optimization for a single query execution that runs under the older database compatibility mode of 150.

  1. Run the following script to execute the stored procedure created earlier. Make sure that the Actual Execution Plan option is enabled. Run the script twice.

    USE WideWorldImporters;
    GO
    SET STATISTICS TIME ON;
    GO
    -- The best plan for this parameter is an index seek
    EXEC Warehouse.GetStockItemsbySupplier 2;
    GO
    

    Screenshot of SSMS with the script to execute the GetStockItemsbySupplier stored procedure.

  2. Select the Messages tab in the output pane of the query. Notice that the query runs in under 1 second.

    Screenshot of SSMS with the output message of the GetStockItemsbySupplier stored procedure.

  3. Check the timings for SET STATISTICS TIME ON from the second query execution. The query is run twice so the second execution doesn't need to compile, which is reflected in Execution 2 in the screenshot. You want to compare this time.

    Screenshot of SSMS with the execution plan of the GetStockItemsbySupplier stored procedure showing Index Seek.

    The query plan uses an Index Seek shown in the screenshot. When this plan is within the procedure cache, the business unit reports that performance exceeds the service level agreement (SLA).

  4. In a different query window, run the following script to run the stored procedure again. This time, it clears the procedure cache and uses a different supplier value.

    USE WideWorldImporters;
    GO
    SET STATISTICS TIME ON;
    GO
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    GO
    -- The best plan for this parameter is an index scan
    EXEC Warehouse.GetStockItemsbySupplier 4;
    GO
    

    Screenshot of SSMS with the script to execute the GetStockItemsbySupplier stored procedure with a supplier value of 4.

    This script might take a few minutes to run. If you look at the Execution plan, the query is using a Clustered Index Scan and Parallelism.

    Screenshot of SSMS with the execution plan of the GetStockItemsbySupplier stored procedure showing Clustered Index Scan.

    When this query runs, the business unit reports that the query runs long. It's acceptable for the information being requested and meets the SLA. However, after the EXEC Warehouse.GetStockItemsbySupplier 4 query runs, the business unit notices the query, EXEC Warehouse.GetStockItemsbySupplier 2 runs slower than before and no longer meets the SLA agreement.

  5. To see what's going on and work on identifying the issue, go back and run the first query in this exercise again.

    USE WideWorldImporters;
    GO
    SET STATISTICS TIME ON;
    GO
    -- The best plan for this parameter is an index seek
    EXEC Warehouse.GetStockItemsbySupplier 2;
    GO
    

    You see that the query runs quickly, in less than 1 second. However, the timing from SET STATISTICS TIME ON is longer than the previous execution. Examine the messages recorded from setting statistics time to on. You can see a significant increase in SQL Server Execution Times.

    Screenshot of SSMS with the output message of the GetStockItemsbySupplier stored procedure showing an increase in SQL Server Execution Times.

    If you look at the Execution plan, it now shows the query is using a clustered index scan and parallelism. The query plan that was compiled when you run EXEC Warehouse.GetStockItemsbySupplier 4.

    Screenshot of SSMS with the execution plan of the GetStockItemsbySupplier stored procedure showing a switch of the query plan from Index Seek to Clustered Index Scan.

Exercise - Workload problem for PSP

  1. Run the following script to clear plan cache and query store. Remember that dbcompat is still set to 150.

    USE WideWorldImporters;
    GO
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    GO
    ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;
    GO
    
  2. Set up Performance Monitor (perfmon) to capture Processor\% Processor Time (not Processor Information) and SQL Server:SQL Statistics\Batch Requests/sec counters.

    Screenshot of Performance Monitor.

  3. Select Performance > Monitoring Tools to select Performance Monitor. Select the plus icon to add the counters Processor\% Processor Time and SQL Server:SQL Statistics\BatchRequests/sec counters.

    Screenshot of Performance Monitor and selecting the plus sign.

  4. After you select the plus icon, an Add Counters menu is displayed. Add the Processor\% Processor Time and SQL Server:SQL Statistics\Batch Requests/sec counters, and then select OK. The counters are in alphabetical order.

    Screenshot of Performance Monitor and adding counters.

  5. Use the X icon to remove % Processor Time (Processor Information) from the counters.

    Screenshot of Performance Monitor and removing counters.

  6. With the performance counters ready, you can simulate a workload against our Wide World Importers database. Run workload_index_seek.cmd 10 from the command prompt or in a PowerShell window. This command should finish quickly. The parameter used is the number of users. This example uses 10. You might want to increase this value for machines with 8 CPUs or more. Make sure that you are in the directory you downloaded as part of the prerequisites.

    Note

    If you are using a named instance, edit workload_index_seek.cmd and workload_index_scan.cmd to use -S.\<instance name>

    Screenshot of command prompt for executing workload_index_seek.cmd.

    Observe the performance monitor counters that you set up previously.

    Screenshot of Performance Monitor after running an index seek query.

  7. Now run workload_index_scan.cmd from the command prompt or in a PowerShell window. These commands should take longer, but now locks into cache a plan for a scan.

    Screenshot of command prompt for executing workload_index_scan.cmd.

  8. Run workload_index_seek.cmd 10 again from the command prompt or in a PowerShell window.

    Observe the performance monitor counters that you set up previously. Notice a higher % Processor Time (CPU), and lower Batch Requests/sec. Also observe that the workload doesn't finish in a few seconds as before.

    Screenshot of Performance Monitor after running an index scan and index seek query.

  9. Press Ctrl+C in the command window or PowerShell window to cancel the workload for workload_index_seek.cmd, because it can take minutes to complete.

  10. Pause Performance Monitor by selecting the pause button.

  11. Run the following script in SSMS to see the skew in supplierID values in the table.

    USE WideWorldImporters;
    GO
    SELECT SupplierID, count(*) as supplier_count
    FROM Warehouse.StockItems
    GROUP BY SupplierID;
    GO
    

    The differences in supplier_count explain why "one size doesn't fit all" for the stored procedure based on parameter values. The seek business process returns data for SupplierID 2, which when initially executed, the optimizer compiles a query plan that uses a clustered index seek. However, when you run the scan business process, the optimizer compiles a query plan that uses a clustered index scan. This new plan is stored in the procedure cache and is the one used for future queries.

    When this situation happens, you can see by the above performance metrics the query doesn't scale for those SupplierIDs with a lower supplier count due to the skew in the data. This parameter sensitivity, also known as parameter sniffing, would require attention of the database administrator and could require coding changes to ensure that the query scales when using parameters for SupplierID.

    Screenshot of SSMS after running the stored procedures in the exercise showing the differences in supplier_count.

For more information on parameter sensitivity, see Query processing architecture guide.

Exercise - Solve the parameter sniffing problem with no code changes

SQL Server 2022 enhancements to PSP can solve the parameter sniffing problem observed in the last exercise with no code changes. SQL Server 2022 with compatibility mode 160 improves performance and scalability of your queries.

  1. Get this workload to run faster and consistently using PSP optimization. Run the following commands in SSMS to set your compatibility mode to 160 and clear your cache.

    USE WideWorldImporters;
    GO
    ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160;
    GO
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    GO
    ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;
    GO
    
  2. Validate that the WideWorldImporters database is set to compatibility level 160 by running the following T-SQL.

    SELECT name, compatibility_level 
    FROM sys.databases
    WHERE name = 'WideWorldImporters'
    

    Screenshot of SSMS checking compatibility mode.

  3. Resume capturing Performance Monitor by selecting the play button.

  4. Run workload_index_seek.cmd 10 from the command prompt or in a PowerShell window. The command should finish quickly as in the second exercise.

  5. Now run workload_index_scan.cmd from the command prompt or in a PowerShell window. These commands should take longer again, and locks into cache a plan for a scan.

  6. Run workload_index_seek.cmd 10 again from the command prompt or in a PowerShell window. The command now finishes again in a few seconds. Unlike in the previous exercise where the command took a longer time to run, you don't need to terminate the workload.

    Observe the Performance Monitor counters and you see consistent performance.

    Screenshot of Performance Monitor showing consistent performance from running the index scan and index seek queries.

  7. To observe why there's a performance difference, look in the Query Store. In SSMS Object Explorer, select WideWorldImporters > Query Store > Top Resource Consuming Queries to open the report. There are two plans for the same stored procedure. The one difference is that there's a new option applied to the query for each procedure, which is why there are two different queries in the Query Store.

    Screenshot of SSMS Query Store Top Resource Consuming Queries report.

    Screenshot of SSMS Query Store Top Resource Consuming Queries report showing the first variant of the query that we ran.

    Screenshot of SSMS Query Store Top Resource Consuming Queries report showing the second variant of the query that we ran.

  8. Run the following script to look at the Query Store plan. Look into the details of the results to see the query text is the same but slightly different with the option to use variants. You see that the query_hash is the same value.

    USE WideWorldImporters;
    GO
    -- Look at the queries and plans for variants
    -- Notice each query is from the same parent_query_id and the query_hash is the same
    SELECT qt.query_sql_text, qq.query_id, qv.query_variant_query_id, qv.parent_query_id, 
    qq.query_hash,qr.count_executions, qp.plan_id, qv.dispatcher_plan_id, qp.query_plan_hash,
    cast(qp.query_plan as XML) as xml_plan
    FROM sys.query_store_query_text qt
    JOIN sys.query_store_query qq
    ON qt.query_text_id = qq.query_text_id
    JOIN sys.query_store_plan qp
    ON qq.query_id = qp.query_id
    JOIN sys.query_store_query_variant qv
    ON qq.query_id = qv.query_variant_query_id
    JOIN sys.query_store_runtime_stats qr
    ON qp.plan_id = qr.plan_id
    ORDER BY qv.parent_query_id;
    GO
    

    Screenshot of SSMS showing the same query_hash for the query store plan.

  9. Run the following script and observe the text of the query is from the stored procedure without variant options. This text is from the parent plan.

    USE WideWorldImporters;
    GO
    -- Look at the "parent" query
    -- Notice this is the SELECT statement from the procedure with no OPTION for variants.
    SELECT qt.query_sql_text
    FROM sys.query_store_query_text qt
    JOIN sys.query_store_query qq
    ON qt.query_text_id = qq.query_text_id
    JOIN sys.query_store_query_variant qv
    ON qq.query_id = qv.parent_query_id;
    GO
    

    Screenshot of SSMS showing the parent plan for the query store plan.

  10. Run the following script. If you select the dispatcher_plan value, you see a graphical plan operator called MULTIPLE PLAN.

    USE WideWorldImporters;
    GO
    -- Look at the dispatcher plan
    -- If you "click" on the SHOWPLAN XML output you will see a "multiple plans" operator
    SELECT qp.plan_id, qp.query_plan_hash, qv.query_variant_query_id, cast (qp.query_plan as XML) as dispatcher_plan
    FROM sys.query_store_plan qp
    JOIN sys.query_store_query_variant qv
    ON qp.plan_id = qv.dispatcher_plan_id;
    GO
    

    Screenshot of SSMS showing the dispatcher plan for the query store plan.

    Screenshot of SSMS showing the multiple plan for the query store plan.

  11. To find the parent stored procedure of the statements from variants, run the following script. Scroll left and right on the results and see the parent_query_id, query_variant_query_id, query_hash, and other columns of interest.

    USE [WideWorldImporters];
    GO
    SELECT  Pl.plan_id as QueryStorePlanId, Pl.query_id as QueryStoreQueryId, qvr.query_variant_query_id, qvr.parent_query_id,
    qvr.dispatcher_plan_id,OBJECT_NAME(Qry.object_id) as ObjectName, Txt.query_sql_text,
    convert(xml,Pl.query_plan)as ShowPlanXML, Qry.query_hash,Rs.first_execution_time, Rs.last_execution_time,
    Rs.count_executions AS NumberOfExecutions, Qry.count_compiles AS NumberOfCompiles, RS.avg_rowcount, Rs.max_rowcount,
    Qry.initial_compile_start_time, Qry.last_compile_start_time, Pl.plan_type_desc
    FROM sys.query_store_runtime_stats AS Rs
    JOIN sys.query_store_plan AS Pl
    ON Rs.plan_id = Pl.plan_id
    JOIN sys.query_store_query_variant qvr
    ON Pl.query_id = qvr.query_variant_query_id
    JOIN sys.query_store_query as Qry
    ON qvr.parent_query_id = Qry.query_id
    JOIN sys.query_store_query_text AS Txt  
    ON Qry.query_text_id = Txt.query_text_id  
    ORDER BY Pl.query_id, Rs.last_execution_time;
    GO
    

Knowledge check

1.

Parameter Sensitive Plan (PSP) optimization can help which scenario: