Optimizer?

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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