Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Optimizer? Expand / Collapse
Author
Message
Posted Monday, April 27, 2009 11:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 2:15 AM
Points: 327, Visits: 364
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



Post #705517
Posted Tuesday, April 28, 2009 2:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #705598
Posted Tuesday, April 28, 2009 3:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 2:15 AM
Points: 327, Visits: 364
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
Post #705619
Posted Tuesday, April 28, 2009 4:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 2:15 AM
Points: 327, Visits: 364

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/
[url]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
Post #705657
Posted Tuesday, April 28, 2009 6:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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 below

SELECT * 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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #705707
Posted Tuesday, April 28, 2009 6:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 2:15 AM
Points: 327, Visits: 364
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
Post #705732
Posted Tuesday, April 28, 2009 7:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #705785
Posted Tuesday, April 28, 2009 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #705790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse