[MSSQL] Indexen en Execution plan probleempje

[djlinsen]

Ik heb een hele grote tabel (zeg 5 miljoen records) In MSSQL Server 2005

code:

Tabel A
key_A bigint  (primary key)
key_B int (foreign key naar een kleine tabel met ongeveer 100 records)
data_1
data_2
data_3
data_4
data_5

Op deze tabel zit een clusterd index op key_A (INDEX1)en een non clusterd index op key_B (INDEX2)

Indien ik een simpele query uitvoer bijv:

code:

select top 1000 * from A where key_B = 100

Dan heb ik het resultaat binnen een seconde, als ik het estimated en actual execution plan bekijk zie ik ook dat het DBMS slim gebruik gemaakt heeft van de indexen.

Echter als ik voor 1 specifieke key_B (110) dezelfde query uitvoer dan is deze dik 30 seconde bezig. Het estimated execution plan is het zelfde als bij key_B = 100 maar het actual executionplan is anders, ik zie daar een tabel scan tussen staan(lijkt me niet bepaald handig op zo'n grote tabel).

Als ik de query ietsje wijzig voor key_B = 110 en ik maak er:

code:

select top 1000 * from A
with (index = INDEX2)
where key_B = 100

Dan is de query weer razend snel klaar zoals de andere query's. De Indexen worden dagelijks gerebuild, ook net na een handmatige rebuild treedt dit effect op. Volgens books online is het gebruik van de with (index) een 'Last Resort' en lijkt mij ook niet bepaald wenselijk om dit in alle query's van de applicatie te gaan proppen.

Heeft iemand enig idee waarom het DBMS niet altijd zelf het juiste executionplan kan bepalen?

[Niemand_Anders]

Heb je misschien een dubbele index op key_b waardoor de engine mogelijk de verkeerde index selecteerd?

Heeft misschien index2 nog een 'include' statement waarmee extra velden aan de index worden toegevoegd welke niet geindexeerd worden, maar wel onderdeel zijn van het resultaat zodat de engine geen record lookup meer hoeft te doen? In zo'n geval zal de 'select *' niet matchen en zal de index worden genegeerd.

Als je de query vanuit verschillende connecties meerdere keren uitvoert, is dan wel altijd het execution plan consistent of wordt dan elke keer een andere index gebruikt?

Kun je anders in de management studio even naar de index gaan (database --> tabel --> indexes) en dan even daarvan het 'script index as' create script afdrukken. Dat geeft misschien nog wat meer inzicht..

[DigiK-oz]

MSSQL gebruikt ook statistics om het path te bepalen toch? Zijn de statistics van de tabel/index up-to-date? Als de optimizer "denkt" dat de waarde 110 erg vaak voorkomt, kan er een full scan gekozen worden omdat dat in dat geval efficienter is.