working with extents

  • I have a question, this may sound like a stupid question but i am pretty new to this.

    I was wondering the showfilestats is not a type of table you can query from? I just want to get the extentsize and the extentused from this and report it. is there a way to do this? And if possible can you explain how extents and pages works. I've read that "the first eight pages of a table are allocated from mixed extents. Only after the table has reached eight pages does SQLServer allocate uniform extents of eight pages each." i'm not quite understanding this concept.

    thank you for your help

    RICHARD KIRMSS


    RICHARD KIRMSS

  • okay....I have figured out what I asked before through extensive reasearch and reading. One thing i'm not sure abuot though is, if an extent gets near its max size, is it possible to make the extent grow? or are the extent sizes a fixed size and it would be better to reorganize the indexes?

    thank you,

    Rich

    RICHARD KIRMSS


    RICHARD KIRMSS

  • Extents are fixed sizes. The data gets fragmented when the extents used for a table are not physically near each other. Rebuilding the clsutered index will reorganize these.

    Steve Jones

    steve@dkranch.net

  • Extents are fixed sizes. The data gets fragmented when the extents used for a table are not physically near each other. Rebuilding the clsutered index will reorganize these.

    Steve Jones

    steve@dkranch.net

  • What are the advantages of both clustered and non clustered indexes. is one better then the other?

    RICHARD KIRMSS


    RICHARD KIRMSS

  • Clustered index stores the data in the order of the index (physically). Nice for columns used in range queries (like dates). Only one per table as you can only store data in physical order one way. Good when there is not a good selectivity for a column.

    Non clustered are pointers to data, thus a range query may actually retrieve lots of data from different places, more pages read, takes longer, more IO, etc. Good for queries (and columns) with high selectivity, like PKs.

    Steve Jones

    steve@dkranch.net

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply