I am sending a parametrized named query in Java using Hibernate. Locally on app it is taking milliseconds and using Toad also. While on uat api on app after calling the query, it is taking up to 5 min to return results and testing it on Toad on uat it is taking milliseconds.

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