Sql-server – Different plans for the same SQL code

execution-plansql serversql-server-2016

I have a slow stored procedure like

CREATE PROCEDURE [dbo].[MyProc]
(
    @Param1 nvarchar(20),
    @Param2 nvarchar(20)
)
AS
BEGIN
    set nocount on

    ;with cte (col1, col2, col3)
    as
    (
        select col1, col2, col3
        from    someTable
    --And the rest of it

I am trying to optimise it because it was taking too long.

When I call it as exec MyProc @Param1=N'xxx', @Param2=N'yyy' it takes 20 seconds or so. To test what I could do I ran it like this:

DECLARE @Param1=N'xxx',
@Param2=N'yyy'
set nocount on

;with cte (col1, col2, col3)
as
(
    select col1, col2, col3
    from    someTable
--And the rest of it

and that takes about a second. Execution plans are completely different. Plan that is used when I call 'exec …' is different to the plan that is used when I run the code directly. I re-indexed the tables used, I called sp_recompile on the table and the stored procedure itself, I found the plans that are used for the SP and called DBCC FREEPROCCACHE ( on the plan handles, but every time the plan is re-generated, they are different.

Any idea what's going on? Why would the same piece of code use different plans, one that takes 1 sec and the other taking 20 secs?

Best Answer

This is called parameter sniffing, and it's one of the hardest problems to solve in a database.

The basic problem is that data is skewed in a way that makes the cost based optimizer choose different plans for returning your data.

There are a lot of ways people think they "fix" parameter sniffing:

Recompile hints
Optimizer for unknown
Optimize for a value

But these really just disable parameter sniffing in different ways. That's not to say they can't solve the problem, they just don't really get to the root cause.

That's because getting to the root cause is usually kind of difficult. You have to look for those pesky "plan quality issues".

Starting with the fast vs slow plans, look for differences like:

Indexes used
Join order
Serial vs Parallel

Also look for different operators that make your code sensitive to parameter sniffing:

Lookups
Sorts
Join type
Memory grants (and by extension, spills)
Spools

Don't get too wrapped up in seek vs scan, index fragmentation, or any of the cargo cult-y stuff people hem and haw about.

Usually, there's a pretty basic indexing problem. Sometimes the code needs a little rewriting. If you want more help, please share the query plans, along with the code, table and index definitions.

Otherwise, good luck!

Related Solutions

Sql-server – SQL Server using incorrect plan

It looks like you have mostly answered your other questions in your monologue, and the things that made a difference are the standard list of things to do to force a recompile after being afflicted by parameter sniffing.

To answer your last question, the one marked my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

The answer is that the plan cache is very specific about SET settings that are in effect on the session that is presenting the query. A different SET setting could need a very different plan, such as the setting SET ANSI_NULLS. Very likely, your connection from SSMS has different settings from what your .Net application. This is easily verifiable by using SQL Server Profiler to trace for Existing Connections and Login Audits, which show you the SET settings.

The effect is that the seemingly same query will result in a Cache Miss and a new one has to be created, which will be somewhat based on the parameters being presented to SQL Server for this instance of the query.

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Scalar functions are called once-per-row, when called as part of a query.

Consider the following example.

Create a new, blank database for our tests:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'mv')
BEGIN
    ALTER DATABASE mv SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE mv;
END
GO
CREATE DATABASE mv;
GO

Create a table, a multi-statement function, and a table-valued-function:

USE mv;
GO
CREATE TABLE dbo.t
(
    t_id int NOT NULL
        CONSTRAINT PK_t
        PRIMARY KEY CLUSTERED
);
GO

CREATE FUNCTION dbo.t_func
(
    @t_id int
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @r bit;
    IF EXISTS (SELECT 1 FROM dbo.t WHERE t.t_id = @t_id)
        SET @r = 1
    ELSE
        SET @r = 0;
    RETURN @r;
END
GO

CREATE FUNCTION dbo.t_tvf
(
    @min_t_id int
    , @max_t_id int
)
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN (
    SELECT t_id = t.t_id
        , e = dbo.t_func(dbo.t.t_id)
    FROM dbo.t
    WHERE t.t_id >= @min_t_id
        AND t.t_id <= @max_t_id
);
GO

Insert some sample data into the table:

INSERT INTO dbo.t (t_id)
SELECT ROW_NUMBER() OVER (ORDER BY c.id, c.colid)
FROM sys.syscolumns c;
GO

Create a table to store function execution stats, and populate it with a start-row showing execution counts for the multi-statement-function, t_func:

CREATE TABLE dbo.function_stats
(
    run_num int NOT NULL
    , object_name sysname NOT NULL
    , execution_count int NULL 
    , CONSTRAINT PK_function_stats
        PRIMARY KEY CLUSTERED (run_num, object_name)
);
GO
INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 1
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';
GO

Run a query against the TVF:
```
SELECT t.*
FROM dbo.t_tvf(1, 2) t;
GO
```

Capture the execution stats now:

INSERT INTO dbo.function_stats (run_num, object_name, execution_count)
SELECT 2
    , o.name
    , COALESCE(fs.execution_count, 0)
FROM sys.objects o 
    LEFT JOIN sys.dm_exec_function_stats fs ON fs.object_id = o.object_id
WHERE o.name = 't_func';

The function stats results:

SELECT *
FROM dbo.function_stats fs
ORDER BY fs.run_num
    , fs.object_name;

╔═════════╦═════════════╦═════════════════╗
║ run_num ║ object_name ║ execution_count ║
╠═════════╬═════════════╬═════════════════╣
║       1 ║ t_func      ║               0 ║
║       2 ║ t_func      ║               2 ║
╚═════════╩═════════════╩═════════════════╝

As you can see, the multi-statement-function has execute twice, once per row for the source table accessed by the TVF.

I expect the mutli-statement-function is being called many, many times by the TVF, giving the impression that it is running slowly, whereas in fact it is simply being called many times.

Best Answer

Related Solutions

Sql-server – SQL Server using incorrect plan

Sql-server – Why would call to scalar function inside a Table Value Function be slower than outside the TVF

Related Question