Parameter & Execution plans.

Hi all,

I have a table TableA with few million rows. When I query TableA , the execution plans changes based on the input parameter as shown below . Why this happens ? How to resolve this ? Any inputs would be appreciated.

SELECT * FROM TableA WHERE Column1 = 1 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 2 => SELECT -> Clustered Index Scan (100%)

SELECT * FROM TableA WHERE Column1 = 3 => SELECT -> Parallelism (3%) -> Clustered Index Scan (97%)

SELECT * FROM TableA WHERE Column1 = 4 => SELECT -> Nested Loops -> Index Seek (50%) -> Clustered Index Seek (50%)

(takes a very long time to retrieve the records)

Thanks in advance,

DBLearner.

Hi DBLearner,

SQL Server recompiles your SQL each time becuase you are running Adhoc SQL. You can either create a stored procedure or use Forced Parameterization database option (sql 2005 ONLY).

The reason its using different access methods could be down to what STATISTICS are stored for those values. You might want to update stats and try it again.

Jag

|||

what all are the indexes created on this table/column. ALso tell us the approx occurence of this column value (if its not PK).

Madhu