I am currently facing an issue with parameterized queries in SQL Server that I do not understand where it is rooted in. I broke it down to a simple example: Let's assume a table that holds data about some child entity as well as the parent_id and a corresponding index on the parent_id. The data is accessed based on this parent_id but through a view that, additionally to the table data, holds a column calculating a row_number over all entries partitioned by the parent_id. ### Reproducable setup Create the table, index and view as follows:

CREATE TABLE dbo.test (id BIGINT IDENTITY(1,1), text NVARCHAR(255), parent_id BIGINT);
GO

CREATE NONCLUSTERED INDEX idx_test_parent_id
       ON dbo.test (parent_id);
GO

CREATE VIEW dbo.test_view
AS
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) AS row_num
  FROM dbo.test
GO

Now get some data into the table:

DECLARE @i BIGINT = 0
WHILE @i < 200000
BEGIN
    SET @i = @i + 1
    INSERT INTO dbo.test (text, parent_id)
    VALUES ('test 1', @i), ('test 2', @i), ('test 3', @i);
END

### The issue When accessing the data through a parameterized query from the view the SQL Server will do a full scan on the table.

DECLARE @parent_id BIGINT = 123456
SELECT *
FROM dbo.test_view
WHERE parent_id = @parent_id

While when accessing the data directly (without using a parameter) we will get the expected index seek.

SELECT *
FROM dbo.test_view
WHERE parent_id = 123456

### What I have tried Searching different forums, I do not really understand what is happening here. I have found similar issues where the parameter had the wrong data type and thus performance was bad, but this is not an issue in my case. I also read about issues with parameter sniffing but I neither think this is a problem here as I do not access data through stored procedures or functions. Also, when I am accessing the data directly from the table with a parameterized query the issue will not occur. An index seek is done even with the parameters. Same happens when I add the OPTION (RECOMPILE) to the query accessing the view with a parameterized query the SQL Server will end up doing an index seek. ### Question Can someone explain what the issue is here? How come that this is an issue for the view but not for the table itself? Do I really need to get rid of the view calculating this row_number differently during inserts/deletes? ### Setup - SQL Server 2022 v16.0.4165 running in a docker container - Docker image: mcr.microsoft.com/mssql/server:2022-latest The real table has a primary key of course. But it also has a lot more columns then only the text column. Including all of these columns in the index would be a possibility. The issue though is not occurring when selecting from the table itself, so it seems not to be an issue of the index to me. I was not aware that I am running the database in a compatibility mode. In the productive environment I am even getting CardinalityEstimationModelVersion="140". I do not think that I have set it up anywhere on purpose. ### Execution plans - direct select with index seek - parameterized select with full table scan - Full table scan with QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150

I am dealing with an issue that I can't seem to solve. I was given this stored procedure that has speed issues. It takes 35 seconds or so. When run *adhoc*, it completes instantly. This made me think it was something to do with parameter sniffing. I have tried rewriting this to use local variables, optimize for unknown\variable\etc, with recompile. The table the scan is happening on is massive, but even with stats updated with fullscan, estimates are still WAY off. Perhaps the query could just be rewritten but I would like to figure this out. *ad hoc* plan - https://www.brentozar.com/pastetheplan/?id=Hkvg5Ge40 Parameters for *ad hoc* - SP plan - https://www.brentozar.com/pastetheplan/?id=S1w49GxER Parameters for SP - --- Parameters come in as:

(sp name)
    @ TransactionId VARCHAR(32),
    @ Category VARCHAR(4000)
AS

DECLARE
    @ Hash VARBINARY(16) = HashBytes('MD5', @Category);

The same values are passed in both the SP and *ad hoc* version. I thought it might be due to something with the hash being created as a local variable from the passed parameter, but in the *ad hoc* version it's doing the same (to me).

Is my query having Parameter Sniffing performance issue ? I have kept my non-clustered index suggested by query execution plan, but still I have doubts whether this is parameter sniffing, or something else. Please check below query: declare @orgid int=22, @salesperson int=0 select pd.col1,dd.col1 from t1 pd inner join (select max(doId) doid,personId from t2 where productId=99 and personId>0 and effectDate IS NOT NULL group by personId) d on pd.personId=d.personId join t2 dd on d.doid=dd.doId join (select max(requestId) requestid,doId from t3 group by doId ) p on dd.doId=p.doId join t3 pp on p.requestid=pp.requestIdjoin person prn on cp.personId=prn.personId where pd.organizationId=@orgId and (@salesperson=0 or cp.personId=@salesperson) order by pd.patientName Will this line create an issue? (@salesperson=0 or cp.personId=@salesperson)

Application uses Entity Framework to create/run query. SELECT [t].[sbt_assetid], [t].[sbt_messagetimestampmst], [t].[sbt_transid], [t].[CurrentRow], [t].[etl_timestampUTC] , [t].[sbt_battery], [t].[sbt_cargostate], [t].[sbt_city], [t].[sbt_country], [t].[sbt_extpwr], [t].[sbt_geoname] , [t].[sbt_geotypename], [t].[sbt_heading], [t].[sbt_idleduration], [t].[sbt_idlegap], [t].[sbt_idlestatus], [t].[sbt_latitude] , [t].[sbt_longitude], [t].[sbt_messagetype], [t].[sbt_movementstate], [t].[sbt_mtsn], [t].[sbt_quality], [t].[sbt_skyfencestatus] , [t].[sbt_speed], [t].[sbt_state], [t].[sbt_tetherstate], [t].[sbt_tirestate] FROM [sb].[TrailerLocations] AS [t] WHERE ((((((((((((([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_0) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_1) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_2) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_3) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_4) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_5) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_6) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_7) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_8) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_9) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_10) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_11) AND ([t].[CurrentRow] = 1))) OR (([t].[sbt_assetid] = @__trailerLocation_SbtAssetid_12) AND ([t].[CurrentRow] = 1)) Typically runs < 3 seconds but several times a day exceeds 30 secs whereupon app times out (dev has said need sub 30 sec response times). Table has ~60 million rows. There are 7126 different "assets" that can be passed in as one of 12 parameters . PK Index on [sbt_assetid] ASC, [sbt_messagetimestampmst] ASC, [sbt_transid], ASC (maybe change sbt_messagetimestampmst to DESC?) NC Index on [sbt_assetid] ASC, [CurrentRow] DESC Stats updated automatically (last update yesterday). https://www.brentozar.com/pastetheplan/?id=SyjnVJcHA

I'm working on the SQL Server performance area and I want to know that is the **Parameter Sniffing** problem resolved in SQL Server 2022 completely ? What **trace flags** are useful in this area ? Thanks in advance.

I have a stored procedure that is producing slightly different plans for different parameters. I would expect it to use the same plan that was generated when it was run the first time. CREATE TABLE myTable ( ID INT IDENTITY(1,1) PRIMARY KEY, Column1 VARCHAR(50), Column2 VARCHAR(50), Column3 VARCHAR(50), Column4 VARCHAR(50), Column5 VARCHAR(50), Column6 VARCHAR(50), Column7 VARCHAR(50), Column8 VARCHAR(50), Column9 VARCHAR(50), Column10 VARCHAR(50) ) DECLARE @i INT = 1 DECLARE @j INT = 1 DECLARE @distinct_value_count INT = 20 DECLARE @distinct_value_count_with_more_rows INT = 3 DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows) WHILE @i <= @distinct_value_count BEGIN DECLARE @current_rows_per_value INT = @rows_per_distinct_value IF @i <= @distinct_value_count_with_more_rows BEGIN SET @current_rows_per_value = @rows_per_distinct_value + 2000 END SET @j = 1 WHILE @j <= @current_rows_per_value BEGIN INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10) VALUES ('Value' + CAST(@i AS VARCHAR(2)), 'Value' + CAST(@j AS VARCHAR(5)), 'Value' + CAST(@j + 1 AS VARCHAR(5)), 'Value' + CAST(@j + 2 AS VARCHAR(5)), 'Value' + CAST(@j + 3 AS VARCHAR(5)), 'Value' + CAST(@j + 4 AS VARCHAR(5)), 'Value' + CAST(@j + 5 AS VARCHAR(5)), 'Value' + CAST(@j + 6 AS VARCHAR(5)), 'Value' + CAST(@j + 7 AS VARCHAR(5)), 'Value' + CAST(@j + 8 AS VARCHAR(5))) SET @j = @j + 1 END SET @i = @i + 1 END Create NonClustered Index Idx_col on MyTable(Column1) sp_recompile 'dbo.MyTable' Create or Alter Procedure dbo.tmp_testProc( @inValue VarChar(50) ) As Begin Set NoCount On Select Id Into #tmpCol From MyTable Where Column1 = @inValue Select t.* From MyTable t Join #tmpCol tmp On t.Id = tmp.Id Order by t.Id End --Scenario 1 exec dbo.tmp_testProc @inValue = 'Value1' --Scenario 2 exec dbo.tmp_testProc @inValue = 'Value5' I'm executing the stored procedure with parameter value = 'Value1' for the first execution and then with 'Value5'. Here are the execution plans for both scenarios: **Scenario 1 Execution Plan**: https://www.brentozar.com/pastetheplan/?id=r1H-3HONh **Scenario 2 Execution Plan**: https://www.brentozar.com/pastetheplan/?id=r1p42SOE3 I anticipated that the second scenario would employ the same plan as the first, but I have observed that the plan differs. I was under the impression that the optimizer reuses the same plan generated for the initial parameter set for all subsequent executions. Therefore, I am uncertain why the optimizer is creating different plans in this instance. I would appreciate any clarification on this matter. Thank you for your assistance.

As far as I know, the OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Table WHERE param=@param1 We have two parameters S1 with high selectivity and S2 with low selectivity. If we are using such a hint: OPTION(OPTIMIZE FOR @param1=S1) and then send S2 to the stored procedure, we have still parameter sniffing. Now I have a conceptual question: How does the OPTIMZE FOR hint help us avoid parameter sniffing?

I recently dealt with a problematic stored proc. At times, runs beautifully fast, other times, runs very very long. I determined that *bad* parameter sniffing was the cause. Just for info - The parameters in the proc are datetime and the query uses these parameters to search through date ranges. Anyway, this is what I attempted: 1. Recreated the proc and used WITH RECOMPILE - Didn't help 2. Recreated the proc and added OPTION (RECOMPILE) - Didn't help 3. Recreated the proc and added OPTION (OPTIMIZE FOR UNKNOWN) - Runs fast 4. Recreated the proc and used local variables - Runs fast To help my understanding.... Is using local variables & OPTIMIZE FOR UNKNOWN the exact same thing in the way that is uses average density statistical data to produce a plan? I tried a couple of combinations of things as well: 5. Recreated the proc and added OPTIMIZE FOR UNKNOWN **&** OPTION (RECOMPILE) - Runs fast 6. Recreated the proc with local variables **&** OPTION (RECOMPILE) - Runs slow I have read about the potential dangers of using OPTIMIZE FOR UNKNOWN and in a lot of cases, using local variables are brought up as if it's the same thing. This is what leads me to think that it is the same thing. BUT - How do I explain that attempt 6 runs slow. I want to say that yes, stats are updated but it's with a less than zero % sampling rate - Tables are HUGE +- 1.6 billion rows. Might also be worth noting - I used the awesome sp_blitzcache and filtered on the specific proc - There is a compilation timeout warning for it - My intuition is telling me that is something to note here.

Here I am facing a performance problem that occurs with particular statements in a stored procedure, that has many statements, on occasion SP is executed once a second, normally completes less than 50 ms, but during problem time (and that can happen once a month, or few times per week - randomly) much longer, until recompiled > SP has 2 input parameters > SP is used by different applications Statements usually complete in ~ 1 ms each, but take longer during problem time I must admit I have only beginner's understanding of what parameter sniffing is and how to fix it. Should definitely invest more time in education but hard to do with all things that happen in my country, please don't be too strict on me One statement that is having problem most frequently: UPDATE MyTable SET tMax = 0 WHERE tMax = 1 and tID in (SELECT b8 FROM #e538) During problem time, this update statement has a lot of LCK_M_U waits, and begins to deadlock with exact same statement executed from different sessions Two other statements: INSERT #e534 (b4, d4, s4, r4) SELECT tID, tDate, tStatusID, ID FROM MyTable WHERE tStatusID = (SELECT min(tStatusID) FROM MyTable as f, LMyTable WHERE tID = MyTable.tID and tType = 1 and ltID = tStatusID and ltComplete = 1 AND tActive = 1) and tID in (select b8 from #e538) AND tActive = 1 and INSERT #e534 (b4, d4, s4, r4) SELECT tID, tDate, tStatusID, ID FROM MyTable WHERE (tDate = (SELECT top 1 tDate FROM MyTable as f WHERE tID = MyTable.tID and tType = 1 AND tActive = 1 order by tDate desc)) and tType = 1 AND tActive = 1 and tID in (select b8 from #e538) Definitely execution plan is very different when compare "good" vs "bad" plans, I can see that from CXPACKET and CXCONSUMER waits on those statements, when normally there aren't any Unfortunately I do not have "bad" execution plan as monitoring tool says "No query plan sampled for this query." MyTable has 225+ million rows, tID is clustered index key (BIGINT, although not unique) Here are my questions: 1. Stored procedure has input parameter, yes, BUT these particular statements do not have parameters, they use temp table instead Is this parameter sniffing in my scenario, or something else ? 2. Apparently it does Index Scan on MyTable during problem time, instead of using seek on Clustered index (tID) as it mostly does. Why it can be ? 3. What should be my further steps to investigate and fix this issue ? I am about to enable Query Store and implement logging to table from this SP, but what else ? and what to look at in Query Store ?

I have to admit I am a computer programmer, and not a DBA so I lack a few prerequisites to do this analysis work properly. That said, I have optimized an ORM generated query, and now the performance is quite acceptable, except sometimes (with some parameter) performance drops dramatically and I don't know how to solve the issue. Looking around, I discovered this thing called 'parameter sniffing' and a quick tutorial on how to analyze the situation, but need some help to validate my assumption, and suggestions on how to resolve the issue. I have an articles list filtered characteristics this generate the following query (in all the subsequent queries, all parameters are replaced by their actual value): SELECT [t9].[ID], [t9].[ID_BRAND], [t9].[CODE], [t9].[CODFOR], [t9].[COD_ALT01], [t9].[COD_ALT02], [t9].[COD_ALT03], [t9].[ID_UOM], [t9].[IS_ACTIVE], [t9].[_ATTRIBUTES] AS [_ATTRIBUTES], [t9].[_DOCUMENTS] AS [_DOCUMENTS], [t9].[_SEO] AS [_SEO], [t9].[_TRANSLATIONS] AS [_TRANSLATIONS], [t9].[_TAGS] AS [_TAGS], [t9].[_NOTES] AS [_NOTES], [t9].[_METADATA] AS [_METADATA], [t9].[IS_B2B], [t9].[IS_B2C], [t9].[IS_PROMO], [t9].[IS_NEWS], [t9].[CAN_BE_RETURNED], [t9].[IS_SHIPPABLE], [t9].[HAS_SHIPPING_COSTS], [t9].[IS_PURCHEASABLE], [t9].[test], [t9].[ID2], [t9].[ID_LINE], [t9].[ID_GROUP], [t9].[ID_CLASS], [t9].[ID_FAM], [t9].[ID_ARTICLE], [t9].[LINE_CODE], [t9].[GROUP_CODE], [t9].[CLASS_CODE], [t9].[FAMILY_CODE], [t9].[ARTICLE_CODE], [t9].[test2], [t9].[ID3], [t9].[CODE2], [t9].[BUSINESS_NAME], [t9].[NAME], [t9].[PHONE_01], [t9].[PHONE_02], [t9].[PHONE_03], [t9].[FAX_01], [t9].[FAX_02], [t9].[COUNTRY_01], [t9].[CITY_01], [t9].[ADDRESS_01], [t9].[COUNTRY_02], [t9].[CITY_02], [t9].[ADDRESS_02], [t9].[EMAIL_01], [t9].[EMAIL_02], [t9].[PEC], [t9].[SITE_01], [t9].[SITE_02], [t9].[SITE_03], [t9].[SITE_04], [t9].[VAT_NUMBER], [t9].[SORT], [t9].[GROUPID_01], [t9].[IS_GROUPLEADER_01], [t9].[GROUPID_02], [t9].[IS_GROUPLEADER_02], [t9].[IS_ACTIVE2], [t9].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t9].[[_SEO]]2] AS [_SEO2], [t9].[[_METADATA]]2] AS [_METADATA2], [t9].[test3], [t9].[ID4], [t9].[CODE3], [t9].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t9].[[_METADATA]]3] AS [_METADATA3], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE] AS [ID_ARTICLE2], [t10].[ID_FILTER], ( SELECT COUNT(*) FROM ( SELECT NULL AS [EMPTY] FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t11] WHERE [t11].[ID_ARTICLE] = [t9].[ID] ) AS [t12] ) AS [value] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t0].[COD_ALT01], [t0].[ID], [t2].[ID], [t4].[ID], [t6].[ID]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[ID_LINE], [t2].[ID_GROUP], [t2].[ID_CLASS], [t2].[ID_FAM], [t2].[ID_ARTICLE], [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t2].[ARTICLE_CODE], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE2], [t4].[BUSINESS_NAME], [t4].[NAME], [t4].[PHONE_01], [t4].[PHONE_02], [t4].[PHONE_03], [t4].[FAX_01], [t4].[FAX_02], [t4].[COUNTRY_01], [t4].[CITY_01], [t4].[ADDRESS_01], [t4].[COUNTRY_02], [t4].[CITY_02], [t4].[ADDRESS_02], [t4].[EMAIL_01], [t4].[EMAIL_02], [t4].[PEC], [t4].[SITE_01], [t4].[SITE_02], [t4].[SITE_03], [t4].[SITE_04], [t4].[VAT_NUMBER], [t4].[SORT], [t4].[GROUPID_01], [t4].[IS_GROUPLEADER_01], [t4].[GROUPID_02], [t4].[IS_GROUPLEADER_02], [t4].[IS_ACTIVE] AS [IS_ACTIVE2], [t4].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t4].[_SEO] AS [[_SEO]]2], [t4].[_METADATA] AS [[_METADATA]]2], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[CODE] AS [CODE3], [t6].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t6].[_METADATA] AS [[_METADATA]]3] FROM [dbo].[tbl_ana_Articles] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[ID], [t1].[ID_LINE], [t1].[ID_GROUP], [t1].[ID_CLASS], [t1].[ID_FAM], [t1].[ID_ARTICLE], [t1].[LINE_CODE], [t1].[GROUP_CODE], [t1].[CLASS_CODE], [t1].[FAMILY_CODE], [t1].[ARTICLE_CODE] FROM [dbo].[tbl_src_ArticlesCategories] AS [t1] ) AS [t2] ON [t2].[ID_ARTICLE] = [t0].[ID] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[BUSINESS_NAME], [t3].[NAME], [t3].[PHONE_01], [t3].[PHONE_02], [t3].[PHONE_03], [t3].[FAX_01], [t3].[FAX_02], [t3].[COUNTRY_01], [t3].[CITY_01], [t3].[ADDRESS_01], [t3].[COUNTRY_02], [t3].[CITY_02], [t3].[ADDRESS_02], [t3].[EMAIL_01], [t3].[EMAIL_02], [t3].[PEC], [t3].[SITE_01], [t3].[SITE_02], [t3].[SITE_03], [t3].[SITE_04], [t3].[VAT_NUMBER], [t3].[SORT], [t3].[GROUPID_01], [t3].[IS_GROUPLEADER_01], [t3].[GROUPID_02], [t3].[IS_GROUPLEADER_02], [t3].[IS_ACTIVE], [t3].[_DOCUMENTS], [t3].[_SEO], [t3].[_METADATA] FROM [dbo].[tbl_ana_Brands] AS [t3] ) AS [t4] ON [t4].[ID] = [t0].[ID_BRAND] LEFT OUTER JOIN ( SELECT 1 AS [test], [t5].[ID], [t5].[CODE], [t5].[_TRANSLATIONS], [t5].[_METADATA] FROM [dbo].[tbl_ana_UoMs] AS [t5] ) AS [t6] ON [t6].[ID] = [t0].[ID_UOM] WHERE ((( SELECT COUNT(*) FROM ( SELECT NULL AS [EMPTY] FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t7] WHERE ([t7].[ID_CHARACTERISTIC] IN (121, 122, 92, 93, 94, 95, 96)) AND ([t7].[ID_ARTICLE] = [t0].[ID]) GROUP BY [t7].[ID_FILTER] ) AS [t8] )) = 2) AND ([t0].[IS_ACTIVE] = 1) ) AS [t9] LEFT OUTER JOIN [dbo].[tbl_src_ArticlesCharacteristics] AS [t10] ON [t10].[ID_ARTICLE] = [t9].[ID] WHERE [t9].[ROW_NUMBER] BETWEEN 1 AND 36 ORDER BY [t9].[ROW_NUMBER], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE] The execution plan is: https://www.brentozar.com/pastetheplan/?id=HJ-HfRfi9 So far so good execution takes less than a second. But if I filter for one more characteristic the query becomes: SELECT [t9].[ID], [t9].[ID_BRAND], [t9].[CODE], [t9].[CODFOR], [t9].[COD_ALT01], [t9].[COD_ALT02], [t9].[COD_ALT03], [t9].[ID_UOM], [t9].[IS_ACTIVE], [t9].[_ATTRIBUTES] AS [_ATTRIBUTES], [t9].[_DOCUMENTS] AS [_DOCUMENTS], [t9].[_SEO] AS [_SEO], [t9].[_TRANSLATIONS] AS [_TRANSLATIONS], [t9].[_TAGS] AS [_TAGS], [t9].[_NOTES] AS [_NOTES], [t9].[_METADATA] AS [_METADATA], [t9].[IS_B2B], [t9].[IS_B2C], [t9].[IS_PROMO], [t9].[IS_NEWS], [t9].[CAN_BE_RETURNED], [t9].[IS_SHIPPABLE], [t9].[HAS_SHIPPING_COSTS], [t9].[IS_PURCHEASABLE], [t9].[test], [t9].[ID2], [t9].[ID_LINE], [t9].[ID_GROUP], [t9].[ID_CLASS], [t9].[ID_FAM], [t9].[ID_ARTICLE], [t9].[LINE_CODE], [t9].[GROUP_CODE], [t9].[CLASS_CODE], [t9].[FAMILY_CODE], [t9].[ARTICLE_CODE], [t9].[test2], [t9].[ID3], [t9].[CODE2], [t9].[BUSINESS_NAME], [t9].[NAME], [t9].[PHONE_01], [t9].[PHONE_02], [t9].[PHONE_03], [t9].[FAX_01], [t9].[FAX_02], [t9].[COUNTRY_01], [t9].[CITY_01], [t9].[ADDRESS_01], [t9].[COUNTRY_02], [t9].[CITY_02], [t9].[ADDRESS_02], [t9].[EMAIL_01], [t9].[EMAIL_02], [t9].[PEC], [t9].[SITE_01], [t9].[SITE_02], [t9].[SITE_03], [t9].[SITE_04], [t9].[VAT_NUMBER], [t9].[SORT], [t9].[GROUPID_01], [t9].[IS_GROUPLEADER_01], [t9].[GROUPID_02], [t9].[IS_GROUPLEADER_02], [t9].[IS_ACTIVE2], [t9].[[_DOCUMENTS]]2] AS [_DOCUMENTS2], [t9].[[_SEO]]2] AS [_SEO2], [t9].[[_METADATA]]2] AS [_METADATA2], [t9].[test3], [t9].[ID4], [t9].[CODE3], [t9].[[_TRANSLATIONS]]2] AS [_TRANSLATIONS2], [t9].[[_METADATA]]3] AS [_METADATA3], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE] AS [ID_ARTICLE2], [t10].[ID_FILTER], ( SELECT COUNT(*) FROM ( SELECT NULL AS [EMPTY] FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t11] WHERE [t11].[ID_ARTICLE] = [t9].[ID] ) AS [t12] ) AS [value] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t0].[COD_ALT01], [t0].[ID], [t2].[ID], [t4].[ID], [t6].[ID]) AS [ROW_NUMBER], [t0].[ID], [t0].[ID_BRAND], [t0].[CODE], [t0].[CODFOR], [t0].[COD_ALT01], [t0].[COD_ALT02], [t0].[COD_ALT03], [t0].[ID_UOM], [t0].[IS_ACTIVE], [t0].[_ATTRIBUTES], [t0].[_DOCUMENTS], [t0].[_SEO], [t0].[_TRANSLATIONS], [t0].[_TAGS], [t0].[_NOTES], [t0].[_METADATA], [t0].[IS_B2B], [t0].[IS_B2C], [t0].[IS_PROMO], [t0].[IS_NEWS], [t0].[CAN_BE_RETURNED], [t0].[IS_SHIPPABLE], [t0].[HAS_SHIPPING_COSTS], [t0].[IS_PURCHEASABLE], [t2].[test], [t2].[ID] AS [ID2], [t2].[ID_LINE], [t2].[ID_GROUP], [t2].[ID_CLASS], [t2].[ID_FAM], [t2].[ID_ARTICLE], [t2].[LINE_CODE], [t2].[GROUP_CODE], [t2].[CLASS_CODE], [t2].[FAMILY_CODE], [t2].[ARTICLE_CODE], [t4].[test] AS [test2], [t4].[ID] AS [ID3], [t4].[CODE] AS [CODE2], [t4].[BUSINESS_NAME], [t4].[NAME], [t4].[PHONE_01], [t4].[PHONE_02], [t4].[PHONE_03], [t4].[FAX_01], [t4].[FAX_02], [t4].[COUNTRY_01], [t4].[CITY_01], [t4].[ADDRESS_01], [t4].[COUNTRY_02], [t4].[CITY_02], [t4].[ADDRESS_02], [t4].[EMAIL_01], [t4].[EMAIL_02], [t4].[PEC], [t4].[SITE_01], [t4].[SITE_02], [t4].[SITE_03], [t4].[SITE_04], [t4].[VAT_NUMBER], [t4].[SORT], [t4].[GROUPID_01], [t4].[IS_GROUPLEADER_01], [t4].[GROUPID_02], [t4].[IS_GROUPLEADER_02], [t4].[IS_ACTIVE] AS [IS_ACTIVE2], [t4].[_DOCUMENTS] AS [[_DOCUMENTS]]2], [t4].[_SEO] AS [[_SEO]]2], [t4].[_METADATA] AS [[_METADATA]]2], [t6].[test] AS [test3], [t6].[ID] AS [ID4], [t6].[CODE] AS [CODE3], [t6].[_TRANSLATIONS] AS [[_TRANSLATIONS]]2], [t6].[_METADATA] AS [[_METADATA]]3] FROM [dbo].[tbl_ana_Articles] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[ID], [t1].[ID_LINE], [t1].[ID_GROUP], [t1].[ID_CLASS], [t1].[ID_FAM], [t1].[ID_ARTICLE], [t1].[LINE_CODE], [t1].[GROUP_CODE], [t1].[CLASS_CODE], [t1].[FAMILY_CODE], [t1].[ARTICLE_CODE] FROM [dbo].[tbl_src_ArticlesCategories] AS [t1] ) AS [t2] ON [t2].[ID_ARTICLE] = [t0].[ID] LEFT OUTER JOIN ( SELECT 1 AS [test], [t3].[ID], [t3].[CODE], [t3].[BUSINESS_NAME], [t3].[NAME], [t3].[PHONE_01], [t3].[PHONE_02], [t3].[PHONE_03], [t3].[FAX_01], [t3].[FAX_02], [t3].[COUNTRY_01], [t3].[CITY_01], [t3].[ADDRESS_01], [t3].[COUNTRY_02], [t3].[CITY_02], [t3].[ADDRESS_02], [t3].[EMAIL_01], [t3].[EMAIL_02], [t3].[PEC], [t3].[SITE_01], [t3].[SITE_02], [t3].[SITE_03], [t3].[SITE_04], [t3].[VAT_NUMBER], [t3].[SORT], [t3].[GROUPID_01], [t3].[IS_GROUPLEADER_01], [t3].[GROUPID_02], [t3].[IS_GROUPLEADER_02], [t3].[IS_ACTIVE], [t3].[_DOCUMENTS], [t3].[_SEO], [t3].[_METADATA] FROM [dbo].[tbl_ana_Brands] AS [t3] ) AS [t4] ON [t4].[ID] = [t0].[ID_BRAND] LEFT OUTER JOIN ( SELECT 1 AS [test], [t5].[ID], [t5].[CODE], [t5].[_TRANSLATIONS], [t5].[_METADATA] FROM [dbo].[tbl_ana_UoMs] AS [t5] ) AS [t6] ON [t6].[ID] = [t0].[ID_UOM] WHERE ((( SELECT COUNT(*) FROM ( SELECT NULL AS [EMPTY] FROM [dbo].[tbl_src_ArticlesCharacteristics] AS [t7] WHERE ([t7].[ID_CHARACTERISTIC] IN (121, 122, 92, 93, 94, 95, 96,97)) AND ([t7].[ID_ARTICLE] = [t0].[ID]) GROUP BY [t7].[ID_FILTER] ) AS [t8] )) = 2) AND ([t0].[IS_ACTIVE] = 1) ) AS [t9] LEFT OUTER JOIN [dbo].[tbl_src_ArticlesCharacteristics] AS [t10] ON [t10].[ID_ARTICLE] = [t9].[ID] WHERE [t9].[ROW_NUMBER] BETWEEN 1 AND 36 ORDER BY [t9].[ROW_NUMBER], [t10].[ID_CHARACTERISTIC], [t10].[ID_ARTICLE] The changed execution plan is: https://www.brentozar.com/pastetheplan/?id=Byf6M0Giq The execution now takes almost 13s. What I have noticed comparing the two execution plans is that even if they have the same shape, (structure and nodes type) they hugely change in the time taken by Hash Match (right outer join) node. But I have no idea on how to improve that. Here I report the structure of involved tables so you have an idea of the indexes involved: CREATE TABLE [dbo].[tbl_ana_Articles]( [ID] [int] IDENTITY(1,1) NOT NULL, [ID_BRAND] [int] NOT NULL, [CODE] [nvarchar](40) NOT NULL, [CODFOR] [nvarchar](40) NOT NULL, [COD_ALT01] [nvarchar](50) NOT NULL, [COD_ALT02] [nvarchar](50) NOT NULL, [COD_ALT03] [nvarchar](50) NOT NULL, [ID_UOM] [int] NOT NULL, [IS_ACTIVE] [bit] NOT NULL, [_ATTRIBUTES] [nvarchar](max) NOT NULL, [_DOCUMENTS] [nvarchar](max) NOT NULL, [_SEO] [nvarchar](max) NOT NULL, [_TRANSLATIONS] [nvarchar](max) NOT NULL, [_TAGS] [nvarchar](max) NOT NULL, [_NOTES] [nvarchar](max) NOT NULL, [_METADATA] [nvarchar](max) NOT NULL, [IS_B2B] [bit] NOT NULL, [IS_B2C] [bit] NOT NULL, [IS_PROMO] [bit] NOT NULL, [IS_NEWS] [bit] NOT NULL, [CAN_BE_RETURNED] [bit] NOT NULL, [IS_SHIPPABLE] [bit] NOT NULL, [HAS_SHIPPING_COSTS] [bit] NOT NULL, [IS_PURCHEASABLE] [bit] NOT NULL, CONSTRAINT [PK_tbl_ana_articles] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[tbl_ana_Brands] Script Date: 06/07/2022 11:21:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tbl_ana_Brands]( [ID] [int] IDENTITY(1,1) NOT NULL, [CODE] [nvarchar](10) NOT NULL, [BUSINESS_NAME] [nvarchar](50) NOT NULL, [NAME] [nvarchar](50) NOT NULL, [PHONE_01] [nvarchar](20) NOT NULL, [PHONE_02] [nvarchar](20) NOT NULL, [PHONE_03] [nvarchar](20) NOT NULL, [FAX_01] [nvarchar](20) NOT NULL, [FAX_02] [nvarchar](20) NOT NULL, [COUNTRY_01] [nvarchar](30) NOT NULL, [CITY_01] [nvarchar](50) NOT NULL, [ADDRESS_01] [nvarchar](100) NOT NULL, [COUNTRY_02] [nvarchar](30) NOT NULL, [CITY_02] [nvarchar](50) NOT NULL, [ADDRESS_02] [nvarchar](100) NOT NULL, [EMAIL_01] [nvarchar](100) NOT NULL, [EMAIL_02] [nvarchar](100) NOT NULL, [PEC] [nvarchar](100) NOT NULL, [SITE_01] [nvarchar](400) NOT NULL, [SITE_02] [nvarchar](400) NOT NULL, [SITE_03] [nvarchar](400) NOT NULL, [SITE_04] [nvarchar](400) NOT NULL, [VAT_NUMBER] [nvarchar](50) NOT NULL, [SORT] [int] NOT NULL, [GROUPID_01] [int] NOT NULL, [IS_GROUPLEADER_01] [bit] NOT NULL, [GROUPID_02] [int] NOT NULL, [IS_GROUPLEADER_02] [bit] NOT NULL, [IS_ACTIVE] [bit] NOT NULL, [_DOCUMENTS] [nvarchar](max) NOT NULL, [_SEO] [nvarchar](max) NOT NULL, [_METADATA] [nvarchar](max) NOT NULL, CONSTRAINT [PK_tbl_ana_Brands] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[tbl_ana_UoMs] Script Date: 06/07/2022 11:21:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tbl_ana_UoMs]( [ID] [int] IDENTITY(1,1) NOT NULL, [CODE] [nvarchar](30) NOT NULL, [_TRANSLATIONS] [nvarchar](max) NOT NULL, [_METADATA] [nvarchar](max) NOT NULL, CONSTRAINT [PK_tbl_ana_UoM] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[tbl_src_ArticlesCharacteristics] Script Date: 06/07/2022 11:21:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tbl_src_ArticlesCharacteristics]( [ID_CHARACTERISTIC] [int] NOT NULL, [ID_ARTICLE] [int] NOT NULL, [ID_FILTER] [int] NOT NULL, CONSTRAINT [PK_tbl_src_ArticlesCharacteristics] PRIMARY KEY CLUSTERED ( [ID_CHARACTERISTIC] ASC, [ID_ARTICLE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Index [IX_ACTIVES_WITH_CODE] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_ACTIVES_WITH_CODE] ON [dbo].[tbl_ana_Articles] ( [IS_ACTIVE] ASC ) INCLUDE([CODE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO /****** Object: Index [IX_BRANDS] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_BRANDS] ON [dbo].[tbl_ana_Articles] ( [ID_BRAND] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_COD_ALT01] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_COD_ALT01] ON [dbo].[tbl_ana_Articles] ( [COD_ALT01] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_COD_ALT02] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_COD_ALT02] ON [dbo].[tbl_ana_Articles] ( [COD_ALT02] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_COD_ALT03] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_COD_ALT03] ON [dbo].[tbl_ana_Articles] ( [COD_ALT03] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_CODE] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_CODE] ON [dbo].[tbl_ana_Articles] ( [CODE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_CODFOR] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_CODFOR] ON [dbo].[tbl_ana_Articles] ( [CODFOR] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO /****** Object: Index [IX_UOM] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_UOM] ON [dbo].[tbl_ana_Articles] ( [ID_UOM] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO /****** Object: Index [MISSING_CHARACTERISTING_INDEX] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [MISSING_CHARACTERISTING_INDEX] ON [dbo].[tbl_ana_Articles] ( [IS_ACTIVE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_CODE] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_CODE] ON [dbo].[tbl_ana_Brands] ( [CODE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO /****** Object: Index [IX_ID_FILTER] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_ID_FILTER] ON [dbo].[tbl_src_ArticlesCharacteristics] ( [ID_FILTER] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO /****** Object: Index [IX_tbl_src_ArticlesCharacteristics] Script Date: 06/07/2022 11:21:05 ******/ CREATE NONCLUSTERED INDEX [IX_tbl_src_ArticlesCharacteristics] ON [dbo].[tbl_src_ArticlesCharacteristics] ( [ID_ARTICLE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_ID_BRAND] DEFAULT ((0)) FOR [ID_BRAND] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_COD_ALT03] DEFAULT (' ') FOR [COD_ALT03] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles__ATTRIBUTES] DEFAULT ('') FOR [_ATTRIBUTES] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_TEST] DEFAULT ((0)) FOR [IS_B2B] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_B2C] DEFAULT ((0)) FOR [IS_B2C] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_PROMO] DEFAULT ((0)) FOR [IS_PROMO] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_NEWS] DEFAULT ((0)) FOR [IS_NEWS] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_CAN_BE_RETURNED] DEFAULT ((0)) FOR [CAN_BE_RETURNED] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_SHIPPABLE] DEFAULT ((0)) FOR [IS_SHIPPABLE] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_HAS_SHIPPING_COSTS] DEFAULT ((0)) FOR [HAS_SHIPPING_COSTS] GO ALTER TABLE [dbo].[tbl_ana_Articles] ADD CONSTRAINT [DF_tbl_ana_Articles_IS_PURCHEASABLE] DEFAULT ((0)) FOR [IS_PURCHEASABLE] GO ALTER TABLE [dbo].[tbl_src_ArticlesCharacteristics] ADD CONSTRAINT [DF_tbl_src_ArticlesCharacteristics_ID_FILTER] DEFAULT ((0)) FOR [ID_FILTER] GO It seems if I omit to select these fields: [_ATTRIBUTES] [nvarchar](max) NOT NULL, [_DOCUMENTS] [nvarchar](max) NOT NULL, [_SEO] [nvarchar](max) NOT NULL, [_TRANSLATIONS] [nvarchar](max) NOT NULL, [_TAGS] [nvarchar](max) NOT NULL, [_NOTES] [nvarchar](max) NOT NULL, [_METADATA] [nvarchar](max) NOT NULL, performance improves a lot. Those fields are meant to be extension point into data structure allowing the program to place few KB of XML inside each rows. Still not clear if this is just a coincidence or may be a problem for the Hash Match operator. I am working with SQL Server 2019 Standard Edition. The program is a C# application written for Net Framework 4.8 using the Linq To SQL ORM. A lot of this query should be rewritten, unfortunately I have to deal with ORM query generation. Seems like that a few nvarchar(MAX) fields slow down the query, even if the result is paged. If I omit them I get a big performance boost.

I have a Azure SQL database (compatibility level = 150). I'm running into a performance problem executing a database query. So after I've rebuilt all my indexes, the query runs fast. When testing, with different parameters, all of a sudden it slows down. The same query with the same parameters is fast in the beginning, and slow after I do around 50 tests. Now when I purge the Query store data via the UI or via SQL:

ALTER DATABASE [mydb] SET QUERY_STORE CLEAR;

the performance is back. I've tried all settings on the Query Store, the problem remains, only deleting the data collected gives me back the initial performance. Ok, so executing DBCC FREEPROCCACHE WITH NO_INFOMSGS; had the same performance impact as clearing the query store. The query is built form my data model in Power BI (direct query). It can be fast for all possible parameter combinations. I tested all of these when the cache was recently cleared. Power BI sends the query to Azure SQL db with a syntax for parameterized queries. I just becomes slower and slower. So is it the right solution, optimize for unknown, knowing that it can be fast for that same parameters? Underneath the that is called from the app, I have 3 table-valued functions. I changed the 3 off them with OPTION (OPTIMIZE FOR UNKNOWN), update the statistics and rebuild all indexes. I must say the performance stays, which is good, although it's still a bit black magic for me.

Why would a proc just run without terminating (increasing IO and CPU in sp_whoisactive, nothing blocking) when all the exact same statements run fine by themselves in SSMS in a few seconds, but the proc never completes? I am EXECing the proc in SSMS WITH RECOMPILE. The proc's logic has changed a little and I have populated some larger test data in order to verify the performance is still acceptable - it was working fine as it was for the small amount of source data (a few hundred rows) that is normally used in the automated build/test cycle. There are other simple logging and auditing procs being called in the proc, but the statement that is apparently hanging according to sp_whoisactive is at the top level not in an inner proc and is just the primary INSERT/SELECT (717 summarized rows to be inserted from a source set of around 50k rows, whole thing runs in about a second when all the steps are run outside the proc) I thought it had to be classic parameter sniffing because the only significant parameter used in the INSERT/SELECT is a date and it normally runs in a loop of dates, and when it got to the first date with rows, that's where it hung, so I figured it must be a plan issue where the empty days had a plan that is just horrible when there are rows for that day in the table. The rows for a particular day go jumps from 0 to 53,536 on the days where I inserted my test data. (Total data in the table is ~3m rows, with that number per day for 60 days - the period I loaded to test). I tried other days in the range with similar data and they all experienced the same behavior. So seems like classic parameter sniffing where the plan is just really bad when the cardinality changes so drastically, but WITH RECOMPILE should rule that out, right? I tried running DBCC FREEPROCCACHE before EXEC the proc or the batch of statements and it makes no difference. The batch of statements completes in a second, the stored proc goes for at least 39 minutes before I cancel it. I am dropping and recreating this entire analytics database containing the proc and the table it is trying to INSERT into in my build process, so it would be clean at the start of all my testing if I run the entire build/test process - it accesses an underlying OLTP database through synonyms, but that database of test data is not changing, and I have updated the statistics on the main table being used where I added the 3m rows. There was an existing CROSS APPLY to a timezone conversion function (https://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server-part-1/) - I didn't think parameter sniffing applied to table-valued functions, the lookup tables it uses definitely are small and uniform and that is in a join in the query unrelated to where my most recent change was made. I'm working to try simplify it to a problem where it still happens and I can actually post the code, but that will take a while. This is SQL Server 2012. I will re-target to my SQL Server 2016 instance for testing there as well, but our product is validated against SQL Server 2012 and 2016, so I can't give up SQL Server 2012 support (yet).

I'm having a very strange issue with an EF linq query, the query runs a dynamic sql, something like this:

EXEC sp_executesql N'select 1 from X',N'@parameter1 int',@parameter1 = 123

The query was taking forever to finish (over an hour...) so my first test was to run it manually with an option (recompile) hint and it ran instantly so to me that would be a case of parameter sniffing, but the strange thing is that I did the test of clearing the entire instance plan cache and I keep getting terrible estimates and the query takes forever, even disabling parameter sniffing on SQL Server I keep getting the same problem. Not sure if it might be somehow related but the query is using a view and the view is using the FOR XML PATH function to extract some data so maybe that could be affecting the estimates? Any ideas on what could be causing that strange behavior? Here is a extract of the plan where the estimates go way off, even after updating statistics and clearing the plan from cache I keep getting the same problem, the only thing that fixes the issue is the recompile hint

We have a multi-tenanted database. FirmID is the partition key and we have lots of different firms. I am running into a parameter sniffing issue and I am having a heck of a time getting around it. I would rather not use any [Options] on the query. My latest thought was to change the name of the parameter I am using for the firm. In the snippet below you will see that instead of using @FirmID I called it @Firm611 where 611 is the actual firm of the ID. This will give me a unique query for every firm.

select
    c.ID [_cid],
    c.Name [Name]  
from vwClaims c with(nolock)  
where c.FirmID=@Firm611       
and (c.Name is not null and c.Name!='')
    
select
    c.ID [_cid],
    c.Name [Name]
from vwClaims c with(nolock)
where c.FirmID=@Firm625
and (c.Name is not null and c.Name!='')

After running Brent Ozar's sp_BlitzCache, I found that it is just compiling down to the same query and causing duplicate cache entries: My question is am I reading that result right? Even though I am changing the parameter name, is it really still using the same plan and parameter sniffing?

I have a table containing 10 years worth of 'package scans'. Somebody scans a package and it records the date and username. Let's pretend for now that retaining 10 years of data actually has a purpose. I have a page to show a summary for the past week, so clearly I only want to read 1 week's worth of data. Here's the query, to be run in SSMS twice, once with a hardcoded **recent date** and again with an **old date in 2013**. It's originally a parameterized query, but in SSMS I'm replacing @p0 with the date: SELECT [t0].[VerifyDate], [t0].[PackageId], [t0].[Username] FROM [dbo].[PackageVerification] AS [t0] INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId] WHERE ([t1].[PackageStatus] 99) AND ([t0].[VerifyDate] > @p0) ORDER BY [t0].[VerifyDate] DESC Before I execute it, I'd like to introduce my date index. Now my date index is **not** on my PackageVerification table, but instead is on a 'helper view' which performs the same join seen above. The query above is able to magically use this indexed view because I have SCHEMABINDING enabled. CREATE NONCLUSTERED INDEX [IX_Helper_PackageVerification_USER_SCAN_HISTORY] ON [dbo].[Helper_PackageVerification] ( [VerifyDate] DESC, [PackageStatus] ASC ) INCLUDE ( [VerifyDateDate], [Username] ) When I run the query in SSMS with an old and new date it uses scan or seek as expected. The threshold seems to be somewhere around 2015. So anything remotely recent should definitely be using a seek. Here's the results of that: When I run it as a parameterized query from my application **I always get a full scan**, which for some reason uses a parallelized plan. At least it's using my helper index. I'm actually not sure why I don't get parameter sniffing for this. I always pass a very recent date so I would have thought it may have preferred a scan but I'm fine with it choosing the above plan given the circumstances. There's a million+ rows and it takes about 150ms. Incidentally this is a SQL Azure database with 2vCores. Parameter sniffing is **enabled** and parameterization is set to **simple**. If I change the query and run my application using OPTION (RECOMPILE) I **do** get the desired **SEEK** and a very good performance of just a few ms. The recompile time seems to be negligible and frankly this is perfectly fine performance I can use. When I look in query store I can verify OPTION RECOMPILE uses the seek for a recent date, and scan for an old date! Awesome. However, and I've never tried this before - I thought how about improving it even further with OPTION (OPTIMIZE FOR @p0 = '4/1/2021'). I was expecting this to also use the seek, but without the need for recompilation every time. I'd just periodically change the date passed to OPTIMIZE FOR - maybe to the beginning of the previous month. However, this is the query in the query store. And it goes and does a full scan of all 1+ million rows when setting the date parameter to 4/7/21! So now I'm lost. I've tried to read about everything I can on the subject but haven't come across this issue. RECOMPILE works, but OPTIMIZE FOR doesn't seem to do anything when I'm expecting it to effectively simulate running the query in SSMS with hardcoded values. ### Query plans This first plan is the only unexpected plan - it's a scan and I want a seek. OPTIMIZE FOR @p1 = '2021/4/1' - https://www.brentozar.com/pastetheplan/?id=H1JB43AUu OPTIMIZE FOR BOTH PARAMS - https://www.brentozar.com/pastetheplan/?id=rkV9U3AUu OPTION RECOMPILE - https://www.brentozar.com/pastetheplan/?id=SJ5cS3CUd These are to prove that the optimizer knows that recent dates should be a seek! HARDCODED 2013 - SCAN - https://www.brentozar.com/pastetheplan/?id=BkeA42RLu HARDCODED 2015 - SEEK - https://www.brentozar.com/pastetheplan/?id=S1c8r3R8O I'm starting to wonder if this version doesn't support OPTIMIZE FOR, even though I can't find anything saying it wouldn't --- **Edit:** (After Paul's answer) I tried a few additional things. First here is the VIEW definition I didn't include before. This does a JOIN and since it uses SCHEMABINDING the optimizer is able to substitute for it: CREATE VIEW [dbo].[Helper_PackageVerification] WITH SCHEMABINDING AS SELECT -- Package Verification columns [t0].PackageVerificationId, [t0].Verfied, -- spelling mistake from long ago! [t0].VerifyDate, -- this is non nullable in [t0] btw [t0].Username, -- Package columns [t1].PackageId, [t1].PackageStatus, [t1].PackedOnDate FROM [dbo].[PackageVerification] AS [t0] INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId] WHERE (Verfied = 1 AND VerifyDate IS NOT NULL AND PackageStatus 99) GO The CLUSTERED index is on PackageVerificationId and the main NON CLUSTERED index is shown above. I actually created half a dozen convering indexes to see which it would pick. 1) I hardcoded PackageStatus 99. It was originally a parameter. 2) I tried adding NOT NULL to the filter on the view to see what would happen. That did actually give me a SEEK, but a useless one since the SEEK predicate was actually on VerifyDate IS NOT NULL. https://www.brentozar.com/pastetheplan/?id=r1HlgF1Dd You can't add a filtered index to an indexed view, so even though the view filters out NOT NULL dates it probably can't be matched against. So that could be the ultimate reason I was unable to get my date to be used for the SEEK predicate? 2) I didn't in this case try using the helper index directly in the query, but I'd pretty much expect that to work with NOEXPAND as I'm doing that elsewhere.

For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance. Being a DBA i understand few of caveats involved when doing sp_recompile and why it would have worked in improving with performance. There has been a constant debate within our management to provide Application/DEV DBA's the access to do sp recompile when they see performance issues like above. Just so that less of DBA involvement is there and APP devs can run on their during perf issues I am very hesitant as i think it can bring some unwanted behaviors where it can sometime even worsen performance of other procs. I am looking for general guidance and experience from your experts why this can be a bad idea under possible different circumstances or when it can be ideal. Your inputs might help me design and may be explain the approach in different way. FYI- Above is being thought not across just one app but across few other as automation when seeing issues like parameter sniffing.

A slow Stored Procedure is extremely faster (going from 2 secs to 8 millisecs) with *QUERYTRACEON 4136* and *ARITHABORT ON*, on the local SQL Server 2008 I use to develop. The "fixed" SP, on another SQL Server 2008, goes back to slowness: with or without *QUERYTRACEON 4136* and *ARITHABORT ON*, it doesn't make any difference. Removing any fix and just adding *WITH RECOMPILE* gives me back the fast performances, so it's clear that the server is ignoring both fixes. I didn't install that second server, while I installed my local dev server and it's "out of the box": I didn't touch any configuration. What I should check on the other server, what could cause it to ignore *QUERYTRACEON* and *ARITHABORT ON*? ---------- **An additional weirdness with *ARITHABORT ON*** Maybe this could help to figure out what's going on. Or maybe it will simply mud things even more. This way: SET ARITHABORT ON SET NOCOUNT ON *ARITHABORT* is ignored. Even using *WITH RECOMPILE*. This way: SET NOCOUNT ON SET ARITHABORT ON *ARITHABORT* is not ignored.

A few days ago I happen to have stumbled on a lot of cases in a new DB I've been given where ARITHABORT set to OFF literally devastate performances. Being my first time facing this specific problem I tried to read as much as possible about it, but I still have some hard time building a definitive picture. The evidence: - ARITHABORT is one of the parameter that is officially documented by MS to be used in the choice of the plan to use - Plans created with ARITHABORT set to ON and set to OFF are clearly different - All versions of SQL Server ships with ARITHABORT set to ON - Microsoft recommends to keep it always set to ON - .NET always set it to OFF, which can, and do, leads to some horrible performances Aside from wondering why .NET always set it to OFF, the matter would look quite settled, and thus I'd proceed to explain it to my tech leader, and that we need to set it back to ON every time we open a connection to the server. But then, it seems there are quite a few voices advocating the contrary, one especially is linked in the second answer to this question: https://dba.stackexchange.com/questions/9840/why-would-set-arithabort-on-dramatically-speed-up-a-query I've tried to go through the entire document, but he lost me quite quickly, especially in the part where he says that ARITHABORT is un-influent when compatibility lever is set at 80 or more, which is not clearly the case. And he lost me even more when, like many others, seems quite firm on stating that Microsoft documentation is wrong about the importance of the ARITHABORT parameter. On top of that, he suggests that if the SP is a lot slower when run from code and it's extremely faster when run from SSMS, the fault is in the SSMS and we should instead correct this one so that it set ARITHABORT to off. Quite honestly...I'm a little lost. Is there some definitive evidence about the entire stuff? Should we always force .Net to use ARITHABORT set to on, accordingly to with what the company who produce SQL Server says, or what?

I just found out that when googling for the parameter sniffing, SQL server is also on the recommended search list but not other RDBMS such as Oracle, MySQL, PostgreSQL. Even in this community, [tag:parameter-sniffing] tag almost comes with [tag:sql-server], but not for the others. Is parameter sniffing just a SQL Server thing? If so, why is that? If not, what is the reason that parameter sniffing is almost only linked with SQL Server?

I routinely "scrub" the parameters of my stored procedures at the top before I run the query like this: -- Scrub params SET @SearchText = NULLIF(@SearchText, '') SET @ThreadId = NULLIF(@ThreadId, 0) SET @Hashtag = NULLIF(@Hashtag, '') But then from this article I read the following: > If the query predicate uses a local variable, consider rewriting the > query to use a parameter instead of a local variable. The value of a > local variable is not known when the Query Optimizer creates the query > execution plan. When a query uses a parameter, the Query Optimizer > uses the cardinality estimate for the first actual parameter value > that is passed to the stored procedure. Does it count as using a local variable if the value originated from a parameter? I'm wondering if my parameter scrubbing could affect the creation of the query execution plan.