August 8, 2008 at 3:03 pm
Hi,
Suppose i have following indexes on a table.
1-)
Index on : C1,
Included Columns : X1, X2
2-)
Index on : C1, C2
Included Columns : X1, X2
3-)
Index on : C3
Included Columns : X1, X2, C1
Now which index will be used by sql server for the following query
SELECT A1, X1
FROM TEMP
WHERE C1 = value
August 8, 2008 at 3:17 pm
Probably the first one, but it's hard to tell, because A1 isn't included in any of them, so it's going to have to at least do a bookmark lookup. Depending on stats and selectivity, it might bypass all of them because of that and just do a table scan.
The real way to tell is check the execution plan for the query.
Or, if this is an exam/interview question (which it looks like), and you have to deal with multiple-choice, pick None of the Above.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 3:19 pm
What if A1 is not present in the query?
SELECT X1
FROM Temp
WHERE C1 = Value
August 8, 2008 at 3:57 pm
Again, probably the first one, but the only way to know for sure is to run it and check the plan. The first index seems redundant because it is a sub-set of index 2 so it would probably use the first index, but there may be a chance that it could use 2.
August 11, 2008 at 8:46 am
usman.tanveer (8/8/2008)
What if A1 is not present in the query?SELECT X1
FROM Temp
WHERE C1 = Value
I just did this test:
create table #T (
C1 int,
C2 int,
C3 int,
X1 int,
X2 int)
create index IDX_T_1 on #T(c1) include (X1, X2)
create index IDX_T_2 on #T(c1,c2) include (x1, x2)
create index IDX_T_3 on #T(c3) include (x1, x2, c1)
set nocount on
insert into #t (c1, c2, c3, x1, x2)
select checksum(newid()), checksum(newid()), checksum(newid()),
checksum(newid()), checksum(newid())
from dbo.numbers
where number between 1 and 10000
select x1
from #t
where c1 = 5
I ran the final select with "Show Actual Execution Plan", and it used the second index. I would have placed higher odds on the first one, by a small margin, but SQL chose the second one, at least in my test. Probably, it's ignoring the first one because it's redundant.
I also tested it with creating the indexes after the inserts, to prevent index fragmentation, and it did exactly the same thing. Same when I added a primary key and clustered index (Identity) column to the temp table. In all cases, it used index 2.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 11, 2008 at 9:56 am
Huh, there you have it. I was pretty certain that it would go with the first, but I didn't want to rule out #2. I am surprised, though, because the optimizer will generally choose the path of least resistance in terms of how many pages it must read in to satisfy the query. One would think that the first index, being narrower and consuming possibly less pages, would be the choice. Maybe behind the scenes with a small table, both indexes are the same size, again in terms of pages, so it chooses the second. Who knows. Thanks for going the extra mile Gus.
August 11, 2008 at 10:22 am
I did a nice long calculation after someone asked me a very similar question in my TechEd session. Turned out, with a 100000 rows table, changing a nonclustered index key from 12 bytes to 62 resulted in a seek taking 1 extra page read.
I'll do some confirmation tests then get the whole thing into a blog post. Hopefully this week.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2008 at 8:34 am
GilaMonster (8/11/2008)
I did a nice long calculation after someone asked me a very similar question in my TechEd session. Turned out, with a 100000 rows table, changing a nonclustered index key from 12 bytes to 62 resulted in a seek taking 1 extra page read.I'll do some confirmation tests then get the whole thing into a blog post. Hopefully this week.
As long as you are doing seeks the extra index depth level that comes from larger average key size comes very infrequently thus the 1 page addition. It gets ugly much more quickly I think for scans.
to the OP: your initial question cannot be answered without knowing both the number of rows in the table and the distribution of the value of the where clause column. Very few rows will always be garnered via a table scan and if the where clause column value a reasonable fraction of the rows in the table, same thing - table scan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 12, 2008 at 11:00 am
TheSQLGuru (8/12/2008)
As long as you are doing seeks the extra index depth level that comes from larger average key size comes very infrequently thus the 1 page addition. It gets ugly much more quickly I think for scans.
It does, but I doubt it's too bad, since sequential reads are faster than random (no repositioning of the drive head) and because of the read-ahead read feature. Also index scans should be less frequent than seeks
I'll test it all out and get some hard numbers. Maybe this weekend.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2008 at 12:59 pm
If you do test it out, please report on the total page counts for each index. Remember - more to it than just seek/scan speed/efficiency. More pages mean other (possibly more useful) stuff forced out of RAM too. Also more columns/data means more likelyhood of fragmentation (and thus non-sequential IO) due to DML activity. I have often wondered myself about where the sweet spot is for this type of indexing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply