October 27, 2009 at 11:20 am
Very nice article Gail. It's always nice to read articles that have such a large impact on performance. I'm looking forward to the rest of the series. Just one question, did you mean to say "physical" instead of "logical" in the following statement? Clustered indexes define the logical order of the table.
October 27, 2009 at 11:35 am
GLIC DBA (10/27/2009)
Just one question, did you mean to say "physical" instead of "logical" in the following statement? Clustered indexes define the logical order of the table.
No. I meant exactly what I said. A clustered index defines the logical order of the table. It says what pages follow what pages when reading the index. It says nothing about where those pages should be on disk (or even within the data file).
Think of a book and imagine that the page numbers are the clustering key. I cut the pages out of a book, mix them up and drop them on the floor. The page number says which page follows which page, but nothing about where the pages are.
If there is no disk fragmentation and no index fragmentation and the file system isn't doing strange storage tricks, then the logical order of the pages will match the physical (on disk) order. If we ignore the disks and look only at the data files, the logical order will be the same as the physical order (location of pages within the data file) only when there is 0% index fragmentation.
Clustered indexes define the logical order of the table. The physical order of the table is affected by many other factors.
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
October 27, 2009 at 11:43 am
Thanks so much for straightening this out for me. I appreciate you taking the time.
October 28, 2009 at 12:07 am
Superb!! this is excellent introduction...Thanks Gail.
Waiting for yr 2nd and 3rd article...
October 28, 2009 at 3:01 am
Thanks Gail..really useful one...
October 28, 2009 at 4:24 am
Thanks Gail - nice article - helped me understand about index seek.
October 28, 2009 at 6:12 am
I didn't realize the working of a cluster index in SQL Server is different from that of a clustered index in Sybase. I erroneously thought that they are the same since at one time the two were the same product. The following is from the Sybase ref man. "With a clustered index, Adaptive Server sorts rows on an ongoing basis so that their physical order is the same as their logical (indexed) order. The bottom or leaf level of a clustered index contains the actual data pages of the table." This is just another difference between the two RDBMSs which I'll have to remember. Thanks again for following up on yesterday's question.
October 28, 2009 at 7:45 am
Another fine example of why I want to read this column every day I get the opportunity, As I am just starting, I can only offer my appreciation for the detailed roadmaps!:-)
October 28, 2009 at 9:21 am
For those who have not taken the deep dive into indexes let this serve as a wake up call. Knowing the way the product actually works can have quite an impact on capacity planning.
So say you are about to add an index to a 28 million plus row table. How big will that index be? Its fairly easy to SWAG at a size by taking the length of the key times the number of rows. That maybe OK in a bunch of circumstances. If I have NO clustered index on the table my pointer is 8 bytes. If I make a clustered index on the auto number column then my pointer is 4 bytes. That is a saving of over 106 Meg.
Gail: Are you going to talk about page overhead? (I hope. I hope. I hope.) I could use that to then get a real picture of how many nodes (key plus pointer) will fit at the leaf level. Does fill factor (not the author. Hi Phil.) only apply at the leaf?
ATBCharles Kincaid
October 28, 2009 at 9:49 am
Charles Kincaid (10/28/2009)
Gail: Are you going to talk about page overhead? (I hope. I hope. I hope.)
Not in an introductory article. Row header, null bitmap, slot indexes, page header and such stuff is pretty deep internals, not introductory material. If you're interested in that, pick up either Inside SQL Server 2005: The Storage Engine or Inside SQL Server 2008 and then go and read Paul Randal's blog.
Does fill factor (not the author. Hi Phil.) only apply at the leaf?
Depends. Look up Pad Index in Books Online.
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
October 29, 2009 at 1:48 am
awesome Gail...
ill make sure to pass this example to others ...
thanks...
October 29, 2009 at 3:45 pm
<<
Think of a book and imagine that the page numbers are the clustering key. I cut the pages out of a book, mix them up and drop them on the floor. The page number says which page follows which page, but nothing about where the pages are.
>>
I love this interpretation 8^)
Thanks for the article, it is great read!!
-Adam
November 3, 2009 at 9:40 am
waiting fr post
November 3, 2009 at 2:52 pm
Gail's excellent article caused me to re-examine a slow running query. I changed the WHERE clause slightly and examined the resulting execution plan. I implemented the changes and got a 5X speed improvement. :w00t:
Thanks!
- Randall Newcomb
November 3, 2009 at 4:44 pm
omkarred (11/3/2009)
waiting fr post
What are you waiting for?
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
Viewing 15 posts - 31 through 45 (of 124 total)
You must be logged in to reply to this topic. Login to reply