IDERA Live | Manage Your Database Evolution

We invite you to join us for IDERA Live, our second-annual Virtual Conference, on Wednesday, May 16 from 9 AM - 3 PM CT.

We’ve gathered some of the industry’s top speakers to walk you through compelling content about significant challenges present in the IT world, as well as give you an inside look into all things IDERA. Some members of our product team will also be speaking on industry topics and will feature the IDERA database lifecycle management methodology. Q&A is welcome and encouraged during each session.

You do not have to register for all sessions to be able to take part in this event - feel free to pick and choose the topics that are most important to you. You can learn and enjoy from your home or workspace as this conference is 100% free and virtual.

Also, those that attend any of the sessions will be entered to win a Holy Stone F181W Wifi FPV Drone! 

IDERA Live Conference Schedule:

  • 9:00 AM - 10:00 AM CT: Decode your Organization’s Data DNA with Ron Huizenga. View Ron's presentation slides here.
  • 10:00 AM - 11:00 AM CT: Nuts and Bolts: Initial SQL Server Security Setup with K. Brian Kelley. View Brian's presentation slides here.
  • 11:00 AM - 12:00 PM CT: To Force Plans, or Not to Force Plans, That is The Question with Kendra Little. View Kendra's presentation slides here. You can view her scripts below: 
    SQLWorkbooks_To-Force-Plans-Or-Not-To-Force-Plans.sql
    /*
    Copyright (c) SQL Workbooks LLC
    All rights reserved.
    
    MIT License.
    
    Permission is hereby granted, free of charge, to any person obtaining a copy
     of this software and associated documentation files (the "Software"), to deal
     in the Software without restriction, including without limitation the rights
     to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
     copies of the Software, and to permit persons to whom the Software is
     furnished to do so, subject to the following conditions:
    
    The above copyright notice and this permission notice shall be included in
     all copies or substantial portions of the Software.
    
    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
     IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
     FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
     AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
     LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
     OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
     THE SOFTWARE.
    
    
    The "SELECT AVG([UnitPrice]*[Quantity]) AS TotalPrice
    	FROM Sales.OrderLines
    	WHERE PackageTypeID = @packagetypeid;" query used in this demo originated in:
    https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/automatic-tuning/force-last-good-plan/sql-scripts/demo-full.sql
    
    License info from: https://github.com/Microsoft/sql-server-samples/blob/master/license.txt
    This material is built on:
    
    Microsoft SQL Server Sample Code
    Copyright (c) Microsoft Corporation
    All rights reserved.
    
    MIT License.
    
    Permission is hereby granted, free of charge, to any person obtaining a copy
     of this software and associated documentation files (the "Software"), to deal
     in the Software without restriction, including without limitation the rights
     to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
     copies of the Software, and to permit persons to whom the Software is
     furnished to do so, subject to the following conditions:
    
    The above copyright notice and this permission notice shall be included in
     all copies or substantial portions of the Software.
    
    THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
     IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
     FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
     AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
     LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
     OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
     THE SOFTWARE.
    */
    
    /* Doorstop */
    RAISERROR(N'Did you mean to run the whole thing?',20,1) WITH LOG;
    GO
    
    
    /****************************************************************************************************************
    * Restore WideWorldImporters database
    * WideWorldImporters-Full.bak is 121MB and can be downloaded from:
    * https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
    ****************************************************************************************************************/
    USE master;
    GO
    SET NOCOUNT ON;
    GO
    
    IF DB_ID('WideWorldImporters') IS NOT NULL
    BEGIN
        ALTER DATABASE WideWorldImporters SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    END
    GO
    RESTORE DATABASE WideWorldImporters FROM DISK=
    	'S:\MSSQL\Backup\WideWorldImporters-Full.bak'  
    	WITH REPLACE,
    	MOVE 'WWI_Primary' to 'S:\MSSQL\Data\WideWorldImporters.mdf',
    	MOVE 'WWI_UserData' to 'S:\MSSQL\Data\WideWorldImporters_UserData.ndf',
    	MOVE 'WWI_Log' to 'S:\MSSQL\Data\WideWorldImporters.ldf',
    	MOVE 'WWI_InMemory_Data_1' to 'S:\MSSQL\Data\WideWorldImporters_InMemory_Data_1';
    GO
    USE WideWorldImporters;
    GO
    
    ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140
    GO
    
    ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON
    GO
    
    ALTER DATABASE WideWorldImporters SET QUERY_STORE (
        OPERATION_MODE = READ_WRITE, 
        QUERY_CAPTURE_MODE = AUTO /* default is all, this ignores insignifiant queries */,
        MAX_PLANS_PER_QUERY = 200 /*default */,
        MAX_STORAGE_SIZE_MB = 2048 /* starter value */,
        CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
        SIZE_BASED_CLEANUP_MODE = AUTO,
        DATA_FLUSH_INTERVAL_SECONDS = 15,
        INTERVAL_LENGTH_MINUTES = 5 /* 
    			Note: I am using a short interval because it's a short demo
    			The default is 30 which usually makes sense
    			Available values: 1, 5, 10, 15, 30, 60, 1440 */,
        WAIT_STATS_CAPTURE_MODE = ON /* 2017 gets wait stats */
        );
    GO
    
    /********************************************************
    OUR PROCEDURE IS SENSITIVE
    ********************************************************/
    
    CREATE OR ALTER PROCEDURE dbo.ParameterSensitiveProc
        @packagetypeid INT
    AS
        SELECT AVG([UnitPrice]*[Quantity]) AS TotalPrice
        INTO #dontfloodssmswithresultsets
    	FROM Sales.OrderLines
    	WHERE PackageTypeID = @packagetypeid;
    GO
    
    
    SET STATISTICS TIME ON;
    GO
    
    --Look at the plan on this first run
    --This plan should have a merge join
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    --This reuses the plan
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    
    
    --But sometimes it gets a different plan...
    exec sp_recompile 'ParameterSensitiveProc';
    GO
    
    --Look at the plan on this first run
    --This plan should have a hash join
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    
    --When this plan is reused, it's slower....
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    
    SET STATISTICS TIME OFF;
    GO
    
    
    
    
    
    /********************************************************
    1) YE OLDE PLAN FREEZING
    One form of plan guides
    Available in SQL Server 2008+
    ********************************************************/
    
    --Get the plan we want to freeze into cache
    exec sp_recompile 'ParameterSensitiveProc';
    GO
    
    --This plan should have a merge join
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    
    
    /* Freeze the plan for the procedure */
    DECLARE 
        @handle varbinary(64),
        @offset int = NULL;  
    
    SELECT 
        @handle = qs.plan_handle
    FROM sys.dm_exec_procedure_stats AS qs  
    JOIN sys.objects as so on qs.object_id = so.object_id
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st  
    WHERE 
        so.name='ParameterSensitiveProc'
     
    EXECUTE sys.sp_create_plan_guide_from_handle @name =  N'Freeze Merge Plan for ParameterSensitiveProc',  
        @plan_handle = @handle,  
        @statement_start_offset = @offset;  
    GO
    
    
    --Can you tell it's frozen?
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    --Note the cost: 2.4521
    
    
    --Reuses the plan, same cost
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    
    exec sp_recompile 'ParameterSensitiveProc';
    GO
    --Does this have the exact same cost?
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    --Reuses the plan, same cost
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    
    
    -- Clean up the plan guide 
    EXEC sys.sp_control_plan_guide N'DROP', N'Freeze Merge Plan for ParameterSensitiveProc';  
    GO
    
    
    --Can you see that it is no longer frozen?
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    
    
    --Nice thing about this approach: SQL Server 2008+
    --Problems with this approach: 
    --	Need to get the good plan in cache
    --	Fumbly to make sure you get the exact right query to freeze the plan
    
    
    
    /********************************************************
    2) MANUAL PLAN FREEZING
    In Query Store
    ********************************************************/
    
    --Let's look at the plans in Query Store
    SELECT 
        (SELECT CAST(qst.query_sql_text AS NVARCHAR(MAX)) FOR XML PATH(''),TYPE) AS [TSQL],
        qsp.plan_forcing_type_desc,
        qsq.query_id,
        qsp.plan_id,
        qsp.engine_version,
        qsp.compatibility_level,
        cast(qsp.query_plan as XML) as plan_xml
    FROM sys.query_store_query as qsq
    JOIN sys.objects as so on 
        so.object_id = qsq.object_id
    JOIN sys.query_store_query_text as qst on 
        qsq.query_text_id = qst.query_text_id
    JOIN sys.query_store_plan as qsp on qsq.query_id = qsp.query_id
    WHERE so.name = 'ParameterSensitiveProc'
    GO
    
    --Open the Queries with High Variation Report and find our query
    --Right click and make it a Tracked Query
    
    --You can force plans in the reports, or you can do it in T-SQL
    --Plug in the query id and the plan id 
    exec sp_query_store_force_plan @query_id=41981, @plan_id=516;
    GO
    
    --Do you see the forced plan?
    SELECT 
        (SELECT CAST(qst.query_sql_text AS NVARCHAR(MAX)) FOR XML PATH(''),TYPE) AS [TSQL],
        qsp.plan_forcing_type_desc,
        qsq.query_id,
        qsp.plan_id,
        qsp.engine_version,
        qsp.compatibility_level,
        cast(qsp.query_plan as XML) as plan_xml
    FROM sys.query_store_query as qsq
    JOIN sys.objects as so on 
        so.object_id = qsq.object_id
    JOIN sys.query_store_query_text as qst on 
        qsq.query_text_id = qst.query_text_id
    JOIN sys.query_store_plan as qsp on qsq.query_id = qsp.query_id
    WHERE so.name = 'ParameterSensitiveProc'
    GO
    
    --Can you tell the plan is forced?
    --Note the cost of the plan
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    --Cost: 3.53512
    
    --Now, we have more rows... a lot more rows
    INSERT Sales.OrderLines ([OrderID], [StockItemID], [Description], [PackageTypeID], [Quantity], [UnitPrice], [TaxRate], [PickedQuantity], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen])
    SELECT [OrderID], [StockItemID], [Description], [PackageTypeID], [Quantity], [UnitPrice], [TaxRate], [PickedQuantity], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen]
    FROM Sales.OrderLines
    WHERE PackageTypeID = 7
    GO 2
    
    
    --Updating statistics won't un-force our manually forced plan
    UPDATE STATISTICS Sales.OrderLines;
    GO
    
    --Compare this plan and its cost now
    --There is also an index request
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    --Question:
    --We froze that plan before the data changed.
    --How do we know that's the best plan now?
    
    
    
    --Unforce the plan
    exec sp_query_store_unforce_plan @query_id=41981, @plan_id=516;
    GO
    
    
    /********************************************************
    3) AUTOMATIC PLAN CORRECTION
    In Query Store
    ********************************************************/
    
    -- Enable automatic tuning on the database:
    ALTER DATABASE WideWorldImporters
    	SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
    GO
    
    SELECT *
    FROM sys.database_automatic_tuning_options;
    GO
    
    --Run with actual plans
    --What plan does this get?
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    --Turn off actual plans 
    --Run this 30 times
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO 30
    
    
    --What plan does this get now when compiled for @packagetypeid=0?
    exec sp_recompile 'dbo.ParameterSensitiveProc';
    GO
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    
    --Our workload continues
    --Turn off actual plans before running this
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO 20
    
    
    -- Do you see a recommendation?
    SELECT 
    	score,
    	reason,
    	JSON_VALUE(state, '$.currentValue') state,
    	JSON_VALUE(state, '$.reason') state_transition_reason,
        JSON_VALUE(details, '$.implementationDetails.script') script,
        planForceDetails.*
    FROM sys.dm_db_tuning_recommendations
      CROSS APPLY OPENJSON (Details, '$.planForceDetails')
        WITH (  [query_id] int '$.queryId',
                [new plan_id] int '$.regressedPlanId',
                [recommended plan_id] int '$.recommendedPlanId'
              ) as planForceDetails;
    GO
    
    
    --Do you see the forced plan? The type is different
    --Which plan is forced at this point?
    SELECT 
        (SELECT CAST(qst.query_sql_text AS NVARCHAR(MAX)) FOR XML PATH(''),TYPE) AS [TSQL],
        qsp.plan_forcing_type_desc,
        qsq.query_id,
        qsp.plan_id,
        qsp.engine_version,
        qsp.compatibility_level,
        cast(qsp.query_plan as XML) as plan_xml
    FROM sys.query_store_query as qsq
    JOIN sys.objects as so on 
        so.object_id = qsq.object_id
    JOIN sys.query_store_query_text as qst on 
        qsq.query_text_id = qst.query_text_id
    JOIN sys.query_store_plan as qsp on qsq.query_id = qsp.query_id
    WHERE so.name = 'ParameterSensitiveProc'
    GO
    
    
    		  
    --Can you see that it is auto-tuned in the actual execution plan?
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 7;
    GO
    
    exec sp_recompile 'dbo.ParameterSensitiveProc';
    GO
    
    --Compare estimated and actual plans
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    
    
    --A little more data changes
    INSERT Sales.OrderLines ([OrderID], [StockItemID], [Description], [PackageTypeID], [Quantity], [UnitPrice], [TaxRate], [PickedQuantity], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen])
    SELECT [OrderID], [StockItemID], [Description], [PackageTypeID], [Quantity], [UnitPrice], [TaxRate], [PickedQuantity], [PickingCompletedWhen], [LastEditedBy], [LastEditedWhen]
    FROM Sales.OrderLines
    WHERE PackageTypeID = 1
    GO
    
    UPDATE STATISTICS Sales.OrderLines;
    GO
    
    --Is the plan still forced?
    EXEC dbo.ParameterSensitiveProc	@packagetypeid = 0;
    GO
    
    
    -- What says our recommendation now?
    SELECT 
    	score,
    	reason,
    	JSON_VALUE(state, '$.currentValue') state,
    	JSON_VALUE(state, '$.reason') state_transition_reason,
        JSON_VALUE(details, '$.implementationDetails.script') script,
        planForceDetails.*
    FROM sys.dm_db_tuning_recommendations
      CROSS APPLY OPENJSON (Details, '$.planForceDetails')
        WITH (  [query_id] int '$.queryId',
                [new plan_id] int '$.regressedPlanId',
                [recommended plan_id] int '$.recommendedPlanId'
              ) as planForceDetails;
    GO
    
    
    
    
    -- Disable automatic tuning on the database:
    ALTER DATABASE WideWorldImporters
    	SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF );
    GO
    
  • 12:00 PM - 1:00 PM CT: Defend Your Database Against Disasters with Tracy Boggiano. You can view Tracy's slides here.
  • 1:00 PM - 2:00 PM CT: The Ever Growing Science of Database Migrations with Bert Scalzo. You can view Bert's slides here.
  • 2:00 PM - 3:00 PM CT: Databases Don’t Build and Populate Themselves with Stan Geiger. You can view Stan's slides here.

Interested in learning more about IDERA's database lifecycle management methodology and the products mentioned in our IDERA Live sessions? Check out our database lifecycle infographic here. Our solutions allow database and IT professionals to design, monitor and manage data systems with complete confidence, whether in the cloud or on-premises. 

IDERA Live sessions will be recorded and available in our resource center here