April 29, 2010 at 8:02 am
GilaMonster (4/29/2010)
Grant Fritchey (4/29/2010)
But... almost forgot this, part of why you use monotonically increasing values as clustered keys so often (or identities defaulted to the primary key) is because inserts always occur at the end of the chain, so you don't see page splits nearly as often.However updates that grow the row can still cause splits if the fill factor was too high. There shouldn't be as many as say a cluster on a random guid, but there could still be lots of splits.
Exactly. And that's when you get into trying to balance the FILLFACTOR to prevent page splits.... It's all such a dance. I'd hate for it to get too simple. It just wouldn't be as much fun.
"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 29, 2010 at 7:37 pm
When we rebuild the index, can SQL Server make the physically and logically order to be corresponding?
And if the logically does not match the physically ,does it mean the fragements come?
---------------------------------------
Thorn Bird...
April 29, 2010 at 7:51 pm
kinzent (4/29/2010)
When we rebuild the index, can SQL Server make the physically and logically order to be corresponding?And if the logically does not match the physically ,does it mean the fragements come?
Index rebuilding is a bit like disk defragmentation: the goal isn't to achieve 100% perfect defragmentation, it's to improve the performance of the index. So - if the DB engine doesn't see there being any improvement in rebuilding (say - the index is so small that it will always fit in memory), you may find that asking for a rebuild does, well - nothing. This is why some amount of fragmentation remains after a rebuild in many cases, it's why pages are ordered logically only, and that's why what's inside the page may be slightly out of order (the maintenance cost is just too darned high).
In the same way - the effort to completely rewrite a database so that the physical storage is 100% ordered (all the pages in order, all of the records IN the pages in order) would in most cases be ridiculous to maintain (you issue a single row update in the "middle" of the table, causing a page split in a multi-TB database: if you had to maintain physical order, that one row would force 500GB of data to be shifted).
The thing is - it's really healthiest to just banish from the mind any conception of "physical order" unless you're tunig for page splits (like has been discussed before). The only order that counts is the LOGICAL order, and that's controlled through ORDER BY's.
Tables and recordsets are random-access, the engine will use whatever method it find most efficient to do the job you asked it for. Order is not a requirement unless you FORCE it to be, and even then, that only guarantees the order in which they're displayed (NOT processed).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2010 at 9:06 pm
Matt Miller (#4) (4/29/2010)
kinzent (4/29/2010)
Index rebuilding is a bit like disk defragmentation: the goal isn't to achieve 100% perfect defragmentation, it's to improve the performance of the index. So - if the DB engine doesn't see there being any improvement in rebuilding (say - the index is so small that it will always fit in memory), you may find that asking for a rebuild does, well - nothing. This is why some amount of fragmentation remains after a rebuild in many cases, it's why pages are ordered logically only, and that's why what's inside the page may be slightly out of order (the maintenance cost is just too darned high).
Matt ,thank you ,very appreciate your answer.
And what the principle of the defragmentation? Make the splitted page to a larger page or another new page?
🙂
---------------------------------------
Thorn Bird...
April 29, 2010 at 11:12 pm
Matt Miller
So - if the DB engine doesn't see there being any improvement in rebuilding (say - the index is so small that it will always fit in memory), you may find that asking for a rebuild does, well - nothing.
Rebuilding always rebuilds, regardless of the size of an index.
A rebuild also results in the physical order of row data on the page matching the logical (slot) order.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 3:39 am
Stefan_G (4/29/2010)
Paul, could you possibly expand a little on what you mean here? What do you mean by logical and physical in this context?
Hey, Stefan. I see Grant 'n' Gail have covered the answer pretty fully already, but seeing as you asked, I'll respond with what I had in mind at the time:
The physical row data might be arranged in any order on the page. The row data is stored in the data region of the page, starting after the 96-byte page header.
The row offset array (two bytes per row, expanding backward from the end of the page toward the data region) is a simple array of pointers to the row data, and this is maintained in logical order on a clustered table. This is an example, from a real clustered table:
Row Offset
3 (0x3) 141 (0x8d)
2 (0x2) 126 (0x7e)
1 (0x1) 111 (0x6f)
0 (0x0) 172 (0xac)
As you can see, row 0 is stored at a later offset on the page than rows 1-3 (which sort later, logically). The offset for row 0 was originally 96 (the first byte of the data region after the page header) but the row moved on the page when a variable-length column was expanded in size by an UPDATE operation.
So, anyway, that was what I had in mind at the time. The second point (about pages existing in the physical file in other than logical order) is also valid, but I wasn't actually thinking about fragmentation at the time.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2010 at 3:49 am
Paul, thanks for the explanation.
I thought this was what you where referring to, but I wasnt sure.
/SG
May 1, 2010 at 8:46 pm
kinzent (4/29/2010)
Matt Miller (#4) (4/29/2010)
kinzent (4/29/2010)
Index rebuilding is a bit like disk defragmentation: the goal isn't to achieve 100% perfect defragmentation, it's to improve the performance of the index. So - if the DB engine doesn't see there being any improvement in rebuilding (say - the index is so small that it will always fit in memory), you may find that asking for a rebuild does, well - nothing. This is why some amount of fragmentation remains after a rebuild in many cases, it's why pages are ordered logically only, and that's why what's inside the page may be slightly out of order (the maintenance cost is just too darned high).
Matt ,thank you ,very appreciate your answer.
And what the principle of the defragmentation? Make the splitted page to a larger page or another new page?
🙂
No - the page size is fixed. The main thing I was getting at is it's focusing on performance. Like Paul described (adding onto Gail and Grant excellent points as usual)- by far and large it will spend its time putting everything in order. That said - the really small stuff might never defragment, and frankly - that's okay, so the engine doesn't work at it excessively hard.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 1, 2010 at 8:53 pm
Paul White NZ (4/29/2010)
Matt Miller
So - if the DB engine doesn't see there being any improvement in rebuilding (say - the index is so small that it will always fit in memory), you may find that asking for a rebuild does, well - nothing.Rebuilding always rebuilds, regardless of the size of an index.
A rebuild also results in the physical order of row data on the page matching the logical (slot) order.
You're right on the interenals. I was focused on the defrag issue a bit much in that response.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply