Query with and without parameters -> totally different execution time

Question

I do have a problem I do not understand.

I have a query with two conditions. This query was very slow, so I've created a index. after this I have some kind of a weird behavior. If I run the query directly with ... WHERE xxx=1234 the result will be delivered in 4 ms when i use parameters like

DECLARE @P1 bigint
SET @P1=1234

...WHERE xxx=@P1

the result will be delivered in 80k ms

I've found some information about parameter sniffing - and I deactivated it - same behavior. I've deactivated it using

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

When I run the query with OPTION (OPTIMIZE FOR (@P1 = 1234)) the result will be delivered in 4ms again.

My Problem is: I do not have the chance to use OPTIMIZE FOR because the SQL-Statement because this query is queried by a program.

Can you guys tell me how can I tell SQL-Server to use the query plan like he is using without parameters?

This is the CREATE TABLE for the Table:

CREATE TABLE [dbo].[CRM_RO](
    [ID] [bigint] NOT NULL,
    [ID_FI] [bigint] NOT NULL,
    [ID_PE] [bigint] NOT NULL,
    [ID_GENERIC] [bigint] NOT NULL,
    [DateiKurzk] [nchar](4) NOT NULL,
    [RelPosNr] [int] NOT NULL,
    [Partnerrolle] [int] NOT NULL,
    [KopfExtKey] [nvarchar](20) NULL,
    [PosExtKey] [nvarchar](20) NULL,
    [Dokument1] [nvarchar](20) NULL,
    [Dokument2] [nvarchar](20) NULL,
    [SAPAbglStat] [tinyint] NOT NULL,
    [SAPAbglDatum_DT] [bigint] NOT NULL,
    [SAPAbglModus] [tinyint] NOT NULL,
    [FreiK1] [int] NOT NULL,
    [FreiK2] [int] NOT NULL,
    [FreiK3] [int] NOT NULL,
    [FreiK4] [int] NOT NULL,
    [FreiK5] [int] NOT NULL,
    [FreiC1] [nvarchar](40) NULL,
    [FreiC2] [nvarchar](40) NULL,
    [FreiC3] [nvarchar](40) NULL,
    [FreiC4] [nvarchar](40) NULL,
    [FreiC5] [nvarchar](40) NULL,
    [FreiN1] [int] NOT NULL,
    [FreiN2] [int] NOT NULL,
    [FreiN3] [int] NOT NULL,
    [FreiN4] [int] NOT NULL,
    [FreiN5] [int] NOT NULL,
    [FreiD1] [int] NOT NULL,
    [FreiD2] [int] NOT NULL,
    [FreiD3] [int] NOT NULL,
    [FreiD4] [int] NOT NULL,
    [FreiD5] [int] NOT NULL,
    [FreiL1] [bit] NOT NULL,
    [FreiL2] [bit] NOT NULL,
    [FreiL3] [bit] NOT NULL,
    [FreiL4] [bit] NOT NULL,
    [FreiL5] [bit] NOT NULL,
    [FreiDez1] [float] NOT NULL,
    [FreiDez2] [float] NOT NULL,
    [FreiDez3] [float] NOT NULL,
    [FreiDez4] [float] NOT NULL,
    [FreiDez5] [float] NOT NULL,
    [Neu] [bigint] NOT NULL,
    [Upd] [bigint] NOT NULL,
    [UpdL] [bigint] NOT NULL,
    [LosKZ] [bit] NOT NULL,
    [AstNr] [int] NOT NULL,
    [KomKz] [bit] NOT NULL,
    [RKZ] [binary](30) NOT NULL,
    [Inaktiv] [bit] NOT NULL,
    [DatumVon] [int] NOT NULL,
    [DatumBis] [int] NOT NULL,
    [UPD_FIELD] [varbinary](334) NULL,
    [MNO] [int] NOT NULL,
    [F7000] [int] NOT NULL,
    [F7002] [int] NOT NULL,
    [F7004] [nvarchar](35) NULL,
    [F7005] [nvarchar](35) NULL,
    [F7006] [nvarchar](35) NULL,
    [F7007] [nvarchar](35) NULL,
    [F7008] [nvarchar](35) NULL,
    [F7009] [int] NOT NULL,
    [F7010] [nvarchar](35) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [ID]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [ID_FI]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [ID_PE]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [ID_GENERIC]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ('') FOR [DateiKurzk]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [RelPosNr]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [Partnerrolle]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [SAPAbglStat]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [SAPAbglDatum_DT]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [SAPAbglModus]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiK1]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiK2]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiK3]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiK4]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiK5]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiN1]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiN2]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiN3]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiN4]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiN5]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiD1]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiD2]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiD3]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiD4]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiD5]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiL1]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiL2]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiL3]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiL4]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiL5]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiDez1]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiDez2]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiDez3]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiDez4]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [FreiDez5]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [Neu]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [Upd]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [UpdL]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [LosKZ]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [AstNr]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [KomKz]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT (0x) FOR [RKZ]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [Inaktiv]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [DatumVon]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [DatumBis]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [MNO]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [F7000]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [F7002]
GO

ALTER TABLE [dbo].[CRM_RO] ADD  DEFAULT ((0)) FOR [F7009]
GO

This is the CREATE INDEX Code. Please do net get confused by the name "missing index". This is just because i used the syntax. I made the Index off the ORDER BY:

CREATE INDEX [QS_missing_index_583420_583419_CRM_RO] ON [CRM].[dbo].[CRM_RO] (ID_FI,ID_PE,DateiKurzk,ID_GENERIC,RelPosNr,Partnerrolle)

How did you "deactivate" parameter sniffing? Also, unless 1234 is going to be the *most* executed parameter value, you don't want to use `optimize for` with this value. Post your DDLs including `CREATE TABLE` and `CREATE INDEX` statements, and both execution plans via [Paste The Plan](https://www.brentozar.com/pastetheplan/). For your last question, if you want a *new* plan each time you'll want to use `option (recompile)` but usually this **is not** the case. — S3S, Apr 10 '19 at 13:21
And last but not least this is how i deactivate the parametersniffing: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; — MiTiJoSo, Apr 10 '19 at 13:32
Please do not post additional information in comments, but edit your question and put all relevant information there. — GuidoG, Apr 10 '19 at 13:34
You can edit your post here: https://stackoverflow.com/posts/55613603/edit — Jacob H, Apr 10 '19 at 13:34
Take a look at the answer by `6eorge Jetson` here: https://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server — Jen R, Apr 10 '19 at 13:45
Your first query went parallel, that's big difference, and they both used a cached plan. Did you create that missing index off a missing index recommendation or based off your order by clause? — S3S, Apr 10 '19 at 13:46
Your table design seems to have some normalization issues. You have what appears to be repeating groups which violates 1NF. https://en.wikipedia.org/wiki/First_normal_form — Sean Lange, Apr 10 '19 at 13:48
i made the index off the order by clause. and i know the table design is... not good :) but i can not influence that. The table is not "mine" :) — MiTiJoSo, Apr 10 '19 at 13:50
If SQL Server generates wildly different plans depending upon the value I would use OPTION (RECOMPILE) on the statement this will ensure that the actual parameter value will be used to generate the plan each time it is run rather than caching the plan based upon the first run. — Steve Ford, Sep 15 '19 at 20:39

score 0 · Answer 1 · answered Apr 19 '19 at 21:53

This is one of the reasons we use stored procedures rather than actual queries in applications. If the query needs to be tuned, like this one does, it is much simpler to make changes to the stored procedure rather than opening up the application.

With that said, cracking open the application and exchanging the query for a stored procedure is really the best answer.

The only other possible way to tune this query is through the Query Store. Take a look at this page: https://blogs.technet.microsoft.com/dataplatform/2017/01/31/query-store-how-it-works-how-to-use-it/

Specifically the "1) It is possible to compare plans, especially useful in case of parameter sniffing" & "2) It is also possible to force the plan" sections.

score 0 · Answer 2 · answered Sep 15 '19 at 14:57

For parameter sniffing, you do not have to turn the option off. You can set local variables for the parameters and use those. If you look at the execution plan, you will see that SQL Server no longer caches parameter values.

I don't run into a ton of issues with this, but have come across situations where a query takes <1s to run and the SSRS report calling it takes several minutes, for example, and doing this fix the problem. So, if the code runs fast, but the thing calling it doesn't, it could be parameter sniffing.

CREATE PROCEDURE dbo.Test ( @var int )
AS
DECLARE 
    @_var int
SELECT 
    @_var = @var

SELECT * 
FROM dbo.SomeTable 
WHERE
    Id = @_var

I would try this and see if it helps.

You can also OPTIMIZE FOR UNKNOWN as well, but I'm not sure if that is going to help you.

Since you created an index, you can also try to add a table hint in the join to make sure SQL Server uses that index (e.g. SELECT * FROM dbo.TableName WITH (INDEX(ix_Index))) when something else calls it. I, generally, try to avoid doing this.

Query with and without parameters -> totally different execution time

2 Answers2