Tuesday, September 18, 2012 8:21 AMHi,
Below are some of the questions which i need some answer. Consider the below table
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.
Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.
Tuesday, September 18, 2012 9:09 AM
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!)
To see details of indexes you have to set the traceflag 3604 (see example in vid!)
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development
db Berater GmbH
- Edited by Uwe RickenMicrosoft Community Contributor Tuesday, September 18, 2012 1:12 PM traceflag was wrong!
Tuesday, September 18, 2012 9:12 AM
Please go through the below link, most of your doubts will be solved:
Tuesday, September 18, 2012 9:18 AM
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, email@example.com
- Marked As Answer by Iam_Rakesh Tuesday, September 18, 2012 12:57 PM