Search code examples
t-sqlazure-sql-databasequery-performancesql-execution-plan

How to: Change actual execution method from "row" to "batch" - Azure SQL Server

I am having some major issues. When inserting data into my database, I am using an INSTEAD OF INSERT trigger which performs a query.

On my TEST database, this query takes much less than 1 second for insert of a single row. In production however, this query takes MUCH longer (> 30 seconds for 1 row).

When comparing the Execution plans for both of them, there seems to be some CLEAR differences:

  1. Test has: "Actual Execution Method: Batch"
  2. Prod has: "Actual Execution Method: Row"
  3. Test has: "Actual number of rows: 1"
  4. Prod has: "Actual number of rows 92.000.000"

Less than a week ago production was running similar to test. But not anymore - sadly.

Can any of you help me figure out why?

I believe, if I can just get the same execution plan for both, it should be no problem.

Prod environment database - Using Row method and no predicate (really slow)

Test environment database - using Batch method and predicate (Fast)

Solution

  • I have found a somewhat satifying solution to my problem.

    By going into Query store of the database, using Microsoft SQL Server Management Studio, I was able to Force a specific plan for a specific query - but only if the plan was already made by the query.