Post reply

FORCED PARAMETERIZATION for syntactically similar but not precisely same query.

  • August 25, 2021 at 10:16 pm

    #3924050

    Should it be done one time only as shown here or should plan guide for adhoc query be dropped /recreated at the beginning of every day, especially if there are some minor syntax discrepancies in the core query?

    We have massive blocking chains that the qry (not this example below, its a huge ad-hoc..) is the block leader of, thousands executions an hour... considering forcing parameterization for just this one qry.

    DECLARE @stmt nvarchar(max); DECLARE @params nvarchar(max); EXEC sp_get_query_template N

    'SELECT pi.ProductID, SUM(pi.Quantity) AS Total FROM Production.ProductModel AS pm INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID WHERE pi.ProductID = 101

    GROUP BY pi.ProductID, pi.Quantity HAVING sum(pi.Quantity) > 50'

    , @stmt OUTPUT, @params OUTPUT;

    EXEC sp_create_plan_guide N'TemplateGuide1', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)';

    example is from here:

    https://docs.microsoft.com/en-us/sql/relational-databases/performance/specify-query-parameterization-behavior-by-using-plan-guides?view=sql-server-ver15

     

    Likes to play Chess

  • August 25, 2021 at 10:48 pm

    #3924053

    If the Production.ProductInventory table is clustered on ProductId (and it almost certainly should be), and you specify a specific ProductId or a list of ProductIds, as in the WHERE clause above, then that query should not cause a lot of blocking regardless.

    If that table is clustered on something else, or you fairly frequently run if for (almost) all ProductIds, then you'll need a nonclustered index on ( ProductId ) INCLUDE ( Quantity ) on Production.ProductInventory to support that particular query.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 26, 2021 at 1:16 am

    #3924075

    I only gave that query as an example of syntax of plan guides. It's from MS doc

    The actual query is different, complex, 100 lines...

     

     

    Likes to play Chess

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply