December 3, 2009 at 8:48 pm
Hi, please help. I've got a weird one. the query below is only selecting top 1 from a table with 2M rows.
if i'm using a variable (query 2) this will produce a table scan on 2M and a key look up.
but if i'm puting the value as query 1, it will give an index seek.
i can't understand how this works. even more weird, the query 2 actually doing a scan on primary key index, which only have column1, but in the execution plan, it says that the output is column1 and column4. and it's doing a key lookup using column1 and column4 as seek predicate. column4 is not even part of that index, and not in the query at all. i've updated the statistics with full scan, and no luck. has the statistics or index gone corrupt?
/* query 1 */
DECLARE @column1 bigint
DECLARE @column2 bigint
SELECT TOP 1
@column1 = Column1,
@column2 = Column2
FROM [dbo].[myTable] WITH (NOLOCK)
WHERE Column3 = '1234567'
ORDER BY column1 DESC
/* query 2 */
DECLARE @column1 bigint
DECLARE @column2 bigint
Declare @column3 varchar(50)
set @column3 = '50831670'
SELECT TOP 1
@column1 = column1 ,
@column2 = column2
FROM [dbo].[myTable] WITH (NOLOCK)
WHERE column3 = @C
ORDER BY column1 DESC
December 3, 2009 at 9:58 pm
btw, it can be fixed by adding another index or using a hint. but i just wanted to know what is going on. cheers.
December 4, 2009 at 12:22 am
Please post table definitions, index definitions and execution plans, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Root of your issue is parameter sniffing. The optimiser knows the value of the constant, it doesn't of the variable. Hence different row count estimates (look at the exec plan, you'll see it yourself) and different plans
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 6, 2009 at 9:52 pm
tx GilaMonster.
December 7, 2009 at 7:55 am
1) the primary key 'scan': I will bet that your PK is an identity integer with clustered PK? In any case, the clustered index scan is actually a table scan by another name.
2) do you have some values of the column you are seeking that have many more rows (perhaps a few percent or even a few tens of percent) than most of the other values?
3) check out the OPTIMIZE FOR clause. also PLAN GUIDES. Be aware that if you DO have skewed data values both of those could get you really bad executions for some inputs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply