April 27, 2009 at 11:54 pm
Dear Friends,
first of all Hello to you all .....
I have some confusions regrarding the Optimizer behaviour, so wanted to put some questions to you all so i can get some good answers on that and can clear things in my mind.
Is there any particular rule about how optimizer selects the indexes? if is there then please let me know it , give me any good link so reading that i can make my doubts clear.
I did some testing results were confusing for me, so i m pasting it here please look ito it..
create table test101 (id int identity(1,1),code int ,Acno int)
insert into test101
select 101,10000 union all
select 102,11000 union all
select 103,12000 union all
select 104,13000 union all
select 105,14000 union all
select 106,15000 union all
select 107,16000 union all
select 108,17000 union all
select 109,18000 union all
select 110,19000
CREATE CLUSTERED INDEX C1 ON TEST101(ID)
CREATE NONCLUSTERED INDEX C2 ON TEST101(CODE)
CREATE NONCLUSTERED INDEX C3 ON TEST101(ACNO)
--------------------------------------------------------------------------------
--Now the Testing--there are total 3 indexes on table
SELECT * FROM TEST101
--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?
SELECT CODE FROM TEST101
WHERE ID=5
ORDER BY CODE
--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?
SELECT CODE FROM TEST101
WHERE ID=5 AND CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID"
SELECT CODE FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
SELECT CODE FROM TEST101
WHERE ID=5
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
SELECT CODE FROM TEST101
WHERE CODE=105
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
I will repeat my confusion , can u tell me by seeing the query how can we say which index would be used?
Thanks in Advance......Any guidence to me would be highly appreciated..
Thanks & Regards,
Mithun Gite
April 28, 2009 at 2:58 am
mithun.gite (4/27/2009)
SELECT * FROM TEST101--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?
Because there's no filter of any form. That query reads the entire table, hence the only way to do it is to scan the clustered index
SELECT CODE FROM TEST101
WHERE ID=5
ORDER BY CODE
--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?
Probably because the optimiser thinks that it's faster to scan the index on code and discard rows that don't match the ID than to seek on the cluster and then have to sort the resulting row set.
All the nonclustered indexes have the clustering key included.
SELECT CODE FROM TEST101
WHERE ID=5 AND CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID"
Because SQL generally only uses one index per table when producing a query plan and since the only index that has ID, Code and AcNo is the clustered index, it will seek on that one
SELECT CODE FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
Probably because it thinks that the two nonclustered indexes aren't selective enough and that it's cheaper to scan the cluster than to seek on either index and have to do bookmark/key lookups
SELECT CODE FROM TEST101
WHERE ID=5
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
It shouldn't be. That one should seek on the cluster. Possibly because the table is so small the scan of the noncluster is slightly faster
SELECT CODE FROM TEST101
WHERE CODE=105
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
Because you're filtering on Code
Try adding a lot more data (at least 10000 rows) and try again. I'm guessing that you'll see quite a different behaviour on some of them. 10 rows is far to little to do meaningful analysis of indexing.
As for links, try these:
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
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
April 28, 2009 at 3:15 am
I was just expecting reply from u as i just saw u replyed some other post answers.....
thanks for repl Gail ....fine i will put more data into it and will test again and will post again , and thanks for giving explaination for each one....
thas really kind of u
Mithun
April 28, 2009 at 4:29 am
Try adding a lot more data (at least 10000 rows) and try again. I'm guessing that you'll see quite a different behaviour on some of them. 10 rows is far to little to do meaningful analysis of indexing.
As for links, try these:
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
After adding 200000 rows to same table the out put has changed in my most questions , the observation is asper below
SELECT * FROM TEST101
--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?
--same in second test
SELECT * FROM TEST101
WHERE ID=5
ORDER BY CODE
--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?
--Now it has changed to Clustred index seek, this is normal.
SELECT CODE FROM TEST101
WHERE ID=5 AND CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID" when it is having 3 three diffrent index on three diff columns.
--same in second test
SELECT * FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
--Now it is going for non clustered index, this is normal
SELECT * FROM TEST101
WHERE ID=5
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
--Now this is going for clustered index, this is notrmal
SELECT CODE FROM TEST101
WHERE CODE=105
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
-- same it is going for non clustered index
but still in some cases i m confuse abt optimizer selecting which index...
but now i will go thru this links http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan and then again will come with new doubts...
thanks gaillllll, thank u very much, have a greta day
Mithun
April 28, 2009 at 6:02 am
You should read Gail's articles first. They'll answer a lot of your questions. You might want to pick up Kalen Delaney's book Inside SQL Server 2005: The Storage Engine to get some idea of how things work within SQL Server.
mithun.gite (4/28/2009)
After adding 200000 rows to same table the out put has changed in my most questions , the observation is asper belowSELECT * FROM TEST101
--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?
--same in second test
For the same reason as before. The clustered index defines not only search criteria for an index, but the storage of the data itself. So when you have a table with a clustered index, the cluster is always used for data retrieval (except in the case of covering indexes, etc.)
SELECT * FROM TEST101
WHERE ID=5
ORDER BY CODE
--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?
--Now it has changed to Clustred index seek, this is normal.
There's enough data in the index for it to be selective enough to find what it needs by pulling out a particular value. By the way, since I assume this only will ever return a single row, the ORDER BY clause is pretty redundant.
SELECT CODE FROM TEST101
WHERE ID=5 AND CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID" when it is having 3 three diffrent index on three diff columns.
--same in second test
Because you're using the ID value of the clustered index and the ID is unique, the rest of the values won't really matter. The optimizer is pretty smart.
SELECT * FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
--Now it is going for non clustered index, this is normal
This is an example of a covering index, so it never even goes to the cluster to return since all the columns are included in the nonclustered index (including the cluster key column, ID).
SELECT * FROM TEST101
WHERE ID=5
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
--Now this is going for clustered index, this is notrmal
SELECT CODE FROM TEST101
WHERE CODE=105
ORDER BY ID,CODE
--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"
-- same it is going for non clustered index
Yep, all these are correct.
Definitely read through Gail's articles. I'd also strongly suggest you pick up the book recommend above. There are other books out there that will help as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2009 at 6:28 am
hi Grant,
Thanks for ur reply and i will be surly reading those articles and also will pickup the book Kalen Delaney's Inside SQL Server 2005.....
thanks for ur time and knowledge u shared....
Mithun
April 28, 2009 at 7:21 am
Grant Fritchey (4/28/2009)
SELECT * FROM TEST101
WHERE CODE=105 AND ACNO=14000
ORDER BY CODE
--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"
--Now it is going for non clustered index, this is normal
This is an example of a covering index, so it never even goes to the cluster to return since all the columns are included in the nonclustered index (including the cluster key column, ID).
Actually not. There's no index (other than the cluster) that's covering. I would guess that this query returns relatively few rows and the optimiser's picking one of the nonclustered indexes (probably the one on code, since it will satisfy the order by as well as the seek), seeking on that and then doing a bookmark/key lookup to fetch the other column.
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
April 28, 2009 at 7:23 am
Oops. I misread what they had created. Sorry. I saw it as a compound index. My bad.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply