query engine using index

Question

Hi,

Below are some of the questions which i need some answer. Consider the below table

test
(
col1 int,
col2 varchar(100)
)

1. If there are 2 non-clustered indexes on the same column and if a select query is run with where clause on the indexed column. Which index will the optimizer choose? Will this choose the index recently created or is it randomly chosen.

2. If there are 2 non-clustered indexes on the same column when a DML operation is performed, will the query write on both the index segments? I mean will this affect the performance?

3. How can I see how indexes are segmented? I mean if a index is created what actually happens in the background.

Thanks,
Rakesh.

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 1

Iam,

I would strongly recommend to get more familiar with INDEXES.
To get a deep dive into indexes see this video from Kimberly Tripp (really excellent!)

http://technet.microsoft.com/en-us/sqlserver/gg508877.aspx

To see details of indexes you have to set the traceflag 3604 (see example in vid!)

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 2

Please go through the below link, most of your doubts will be solved:

http://www.sqlservercentral.com/articles/Indexing/68439/

Answer 3

1) It's not random, but I would not think that it is deterministic. It will choose one of the indexes.

2) Yes, both indexes will be updated, so there is a cost fdor thise.

3) What do you mean with "segmented"?

---

Erland Sommarskog, SQL Server MVP, [e-mail]