Posted by Emin on 01/03/07 18:57

One more question, is there a process similar to defragmentation that I
should run periodically to collect split pages together?

Thanks again,
-Emin

On Jan 3, 1:42 pm, "Emin" <emin.shop...@gmail.com> wrote:
> Dear Roy, Erland, and Hugo,
>
> Thank you very much for your excellent responses. It sounds like a
> single logical table is the way to go since the frequency of inserts is
> not very high. If necessary, where would I get more information about
> low level details like page splits (e.g., how to control the page size,
> how to determine when and how many page splits are occuring, etc.)?
>
> Thanks again,
> -Emin
>
> On Jan 2, 5:53 pm, Hugo Kornelis <h...@perFact.REMOVETHIS.info.INVALID>
> wrote:
>
> > On 2 Jan 2007 08:57:21 -0800, Emin wrote:
>
> > (snip)
>
> > >if I insert a record for a given stock does the database have to
> > >physicall "move down" all the records that are below that or does it do
> > >something smarter?Hi Emin,
>
> > Roy answered most of your questions, but was friendly enough to leave
> > this bit for me to comment on.
>
> > Yes, it does something smarter. Rows are *NOT* physically stored in the
> > order imposed by the clustered index. The clustered index imposes a
> > *logical* ordering on the data, which is implemented by pointers that
> > form a logical chain of database pages.
>
> > If a row in inserted, SQL Server first navigates the clustered index to
> > find the correct location (database page). Then, there are two
> > possibilities:
>
> > 1. There's still space left on the page. The row is added and the insert
> > is finished.
>
> > 2. There's not enough free space on the page. A page split occurs: half
> > of the data on the page is moved to a newly allocated page and pointers
> > are updated to reflect the location of the new page in the chain. After
> > that, the new row is inserted.
>
> > If your database has to process *LOTS* of inserts (in the order of
> > thousands per second or more), it makes sense to define your clustered
> > index such that new rows are always inserted at the logical end of the
> > pointer chain. In that case, page splits will never happen.
> > For lower amounts of inserts, the overhead of a page split is
> > insignificant.
>
> > --
> > Hugo Kornelis, SQL Server MVP
> > My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]