Fragmentation (part 3): What are extents?

SQL Server Storage Engine Fragmentation part 3 What are extents | storage bench

SQL Server Storage Engine Fragmentation part 3 What are extents | storage bench — Fri, 19 Jun 2009 11:06:19 GMT

PingBack from http://thestoragebench.info/story.php?id=4137

SQL Server Storage Engine Fragmentation part 3 What are extents | Toe Nail Fungus

Tue, 09 Jun 2009 09:11:10 GMT

PingBack from http://toenailfungusite.info/story.php?id=4805

SQL Server Storage Engine Fragmentation part 3 What are extents | Uniform Stores

SQL Server Storage Engine Fragmentation part 3 What are extents | Uniform Stores — Mon, 01 Jun 2009 23:31:10 GMT

PingBack from http://uniformstores.info/story.php?id=19080

Fragmentation on the database - close friend of a DBA

SSQA- Users & SQL tools — Mon, 23 Apr 2007 12:42:57 GMT

On day-to-day basis a DBA might come across with the issues on the fragmentation on the database, it

re: Fragmentation (part 3): What are extents?

Paul Randal - MSFT — Sat, 29 Jul 2006 02:40:44 GMT

Hi jccondor,

Apologies for the delay in replying - I missed this comment when it was posted.

I'll go into detail on clustered indexes in a later post but I'll address some of your concerns here:
- multi column indexes have all index key columns in the tree-nodes, otherwise building a b-tree would be impossible.
- INCLUDEd columns are not index keys and so do not have to be in the tree nodes
- in the example you give, the 'id' column will come first in the clustered index because its the primary key, unless you specify non-clustered. You'll need to post the actual index schema you're working with.

Thanks

re: Fragmentation (part 3): What are extents?

jccondor — Tue, 11 Jul 2006 19:42:22 GMT

Paul, could you touch the physical and logical structure of clustered indexes and how there navigated.

My main concern is that (from what I knew or thought I knew SQL 2000) Indexes had the following structure: multi-column indexes the first only the first column was used to construct the tree-nodes. Leafs had the remaining columns. (BOL for 2005 now state differently we now have INCLUDE for adding columns to the leaf nodes and tree nodes contain all columns). I imagine though that the order is still important when navigating them ! ?

My doubt has all ways been the following regarding clustered indexes. ( suppose a table with a US-STATE column (non-unique), id (primary key) filed ) now it's clear that sql server will order data by us-state and within each state by id.

Select * from table where us-state='MA' is efficient because of the clustered index having us-state first.

select * from table where id=@id: will probably require looking at primary key index then fetching us-state and then looking at the clustered index by state,id. HOW IS THIS ACHIVED (how can SQL make this efficient, is it doing a partial region scan on us-state and looking for the id?)

B/W grate articles.

re: Fragmentation (part 3): What are extents?

Paul Randal - MSFT — Sat, 08 Jul 2006 01:01:58 GMT

Hi Ryan,

I'm going to cover all of this in later posts but here are some simplified snippets to (hopefully) satisfy you in the meantime:
- yes, index and data pages can coexist, but as the fanout size increases, the odds of hitting such an extent decreases.
- What you've heard is incorrect though. The whole extent is not read into memory when a single page is read, unless the read is being driven by range-scan readahead. Also, the upper levels of a well-used index with a decent fanout are typically memory resident and so do not incur IO costs - the IO cost comes from reading the leaf-level page.
- having interleaved index and data pages in extents will impact the ability of the readahead code to generate multi-page contiguous IOs, but again, that's a negligible cause compared to page fragmentation
- readahead is driven from the level above the leaf level - I'm not going to explain it all now

Hope this helps in the short-term.

re: Fragmentation (part 3): What are extents?

rhunt — Mon, 03 Jul 2006 17:29:28 GMT

Paul, I'm not sure where this will fit in, but I was wondering if you could discuss the mixture of page types for clustered Index/tables on extents. I've heard before that leaf and intermediate level pages for clustered indexes often coexist on the same extents - this makes single row or small group lookups very fast because the leaf level pages are often on the same extent already paged-in to perform the CI seek.

However, it seems to be that this would have a negative impact on range queries. One of the benefits of ranges on CI's is that SQL is supposed to be able to traverse the intermediate index pages until locating the first occurrence of "true" based on the selection predicate, and then jump down to the leaf level to finish off selection (until leaf level keys result in selection predicate = false). At least this is what I think it does...

With intermediate and leaf pages on the same extents, it seems that the I/O load would be increased when intending to scan only the leaf level (I/O wise - wouldn't you also be scanning the intermediate level of the index as well??).

Thanks. Ryan