Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Optimizer? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, April 27, 2009 11:54 PM
 Old 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 test101select 101,10000 union allselect 102,11000 union allselect 103,12000 union allselect 104,13000 union allselect 105,14000 union allselect 106,15000 union allselect 107,16000 union allselect 108,17000 union allselect 109,18000 union allselect 110,19000CREATE 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 tableSELECT * 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=14000ORDER BY CODE--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID"SELECT CODE FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER 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 Group: General Forum Members Last Login: Saturday, December 3, 2016 5:18 AM Points: 45,619, Visits: 44,147
 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 indexSELECT 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=14000ORDER 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 oneSELECT CODE FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER 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 lookupsSELECT 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 fasterSELECT 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 CodeTry 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 ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #705598
 Posted Tuesday, April 28, 2009 3:15 AM
 Old 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 uMithun
Post #705619
 Posted Tuesday, April 28, 2009 4:29 AM
 Old 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 belowSELECT * FROM TEST101--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?--same in second testSELECT * 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=14000ORDER 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 testSELECT * FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"--Now it is going for non clustered index, this is normalSELECT * 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 notrmalSELECT 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 indexbut 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 dayMithun
Post #705657
 Posted Tuesday, April 28, 2009 6:02 AM
 SSCoach Group: General Forum Members Last Login: Yesterday @ 12:17 PM Points: 17,173, Visits: 32,139
 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 testFor 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=14000ORDER 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 testBecause 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=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"--Now it is going for non clustered index, this is normalThis 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 notrmalSELECT 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 indexYep, 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 RooseveltThe Scary DBAAuthor of: SQL Server Query Performance Tuning and SQL Server Execution PlansProduct Evangelist for Red Gate Software
Post #705707
 Posted Tuesday, April 28, 2009 6:28 AM
 Old 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 Group: General Forum Members Last Login: Saturday, December 3, 2016 5:18 AM Points: 45,619, Visits: 44,147
 Grant Fritchey (4/28/2009)SELECT * FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"--Now it is going for non clustered index, this is normalThis 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 ShawMicrosoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #705785
 Posted Tuesday, April 28, 2009 7:23 AM
 SSCoach Group: General Forum Members Last Login: Yesterday @ 12:17 PM Points: 17,173, Visits: 32,139
 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 RooseveltThe Scary DBAAuthor of: SQL Server Query Performance Tuning and SQL Server Execution PlansProduct Evangelist for Red Gate Software
Post #705790

 Permissions