Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-05-08 : 05:34:29
|
| 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 recordsThanks in advance,Hariarul |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-08 : 07:39:44
|
| The server will look at the cached plans to see if the plan already exists. The check is done on the text of the statement - as these are different then it will ceate a new plan for each statement. The plans are different because of the statistics on the columns (specifically Column1). If you think it's getting an incorrect plan then try updating statistics.If it's still wrong give a hint or use something that will use the same plan for each - a stored procedure or sp_executesql with parameters - make sure you run it first with the value that gives the plan you want whenever it gets purged from cache.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Parameter & Execution Plans.