Same query - different execution plans?

Query 2 uses a variable. At the time the batch is compiled SQL Server does not know the value of the variable so just falls back to heuristics very similar to OPTIMIZE FOR (UNKNOWN) For > it will assume that 30% of the rows will end up matching (or 3000 rows in your example data). This can be seen in the execution plan image as below.

This is significantly over and above the 12 rows (0.12%) which is the tipping point for this query in whether it uses a clustered index scan or a non clustered index seek and key lookups. You would need to use OPTION (RECOMPILE) to get it to take account of the actual variable value as shown in the third plan below. Script CREATE TABLE #Sale ( SaleId INT IDENTITY(1, 1) CONSTRAINT PK_Sale PRIMARY KEY, Test1 VARCHAR(10) NULL, RowVersion rowversion NOT NULL CONSTRAINT UQ_Sale_RowVersion UNIQUE ) /*A better way of populating the table!

*/ INSERT INTO #Sale (Test1) SELECT TOP 10000 NULL FROM master..spt_values v1, master..spt_values v2 GO SELECT * FROM #Sale WHERE RowVersion > 0x000000000001C310-- Query #1 DECLARE @LastVersion rowversion = 0x000000000001C310 SELECT * FROM #Sale WHERE RowVersion > @LastVersion-- Query #2 SELECT * FROM #Sale WHERE RowVersion > @LastVersion OPTION (RECOMPILE)-- Query #3 DROP TABLE #Sale.

Thank you. This worked and makes complete sense to me. – Alex Aza May 6 at 14:38 So, was it tipping or not, sorry can't tell?

– John Leidegren May 6 at 14:51.

Try creating a covering index for the actual data that you need to retrieve and avoid select *, depending on the data in your table that's the only sure thing that will force SQL Server to not tip and fallback to a scan. A covering index is an index in which the search filter is in the same order and each output column is included in the index. Also, since we're dealing with parameterization, it's worth trying to see if optimize for unknown has any impact on the execution plan here.

Mitch Wheat - Changed it, thanks for pointing it out. I'm a neat freak myself. – John Leidegren May 6 at 6:24 +1 Optimize for unknown should do it.

– Goran May 6 at 6:48 1 @Goran - No it shouldn't. It is actually the fact that it is doing something very similar to OPTIMIZE FOR (UNKNOWN) that is causing the OP's problem. – Martin Smith May 6 at 9:00 I'm with @Matrin on this as well, hence, I'm a bit vauge, there are several things when dealing with parameters that cause the execution plan to behave differently and once it's cached, you'll have to recompile it to see the effect of any changes you make (invalidate the cache).

– John Leidegren May 6 at 9:06 Yes, covering index would help, but it would not explain why execution plans a different for two similar queries. Also,covering index is not a good option in my case, as my real table has about 20 columns and I need all of them in my query. So covering index would need even more space to store than the table itself, and the table is huge.

Also, query always suppose to retrieve very small subset of rows (close to zero). – Alex Aza May 6 at 14:45.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.

Related Questions