Large amount of pages for few rows

[Jakob]

Hello,

I have experienced that some of my tables occupies an extremely large amount
of pages but with few rows. An example is a table with 37 rows over 22000
pages !. The columns are simple integer and char. I fixed the problem by
introducing a clustered index. Now it only uses 1 page. But can anyone
explain this behaviour in SQLServer 2000 ?
regards Jakob Mathiasen

[getinked]

the original fill index might be really low. check to make sure your
default fill factor isnt set to like 10% or something like that.

[getinked]

the original fill index might be really low. check to make sure your
default fill factor isnt set to like 10% or something like that.

[Erland Sommarskog]

Jakob (ja*************@hotmail.com) writes:

I have experienced that some of my tables occupies an extremely large
amount of pages but with few rows. An example is a table with 37 rows
over 22000 pages !. The columns are simple integer and char. I fixed the
problem by introducing a clustered index. Now it only uses 1 page. But
can anyone explain this behaviour in SQLServer 2000 ?

So there was no clustered index on the table previously? Well, that's
the moral: always have a clustered index on your tables (unless you
really know that it's bad for some table.)

A table without a clustered index is a heap. If memory serves, new rows
in heaps are always inserted at the end of the table. This means that if
rows are inserted and deleted frequently, you will use many pages, but
the table will be largely fragmented. Note that this also applies to
UPDATE statments, when these cannot be carried out in place.

It may seem obvious that SQL Server should insert new rows in a gap,
but in such case it would have to find a gap somewhere.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gang He [MSFT]]

Empty pages in a heap don't always get deallocated when they become empty.
Only when LCK_M_X table lock is held when the page is changed to empty, the
page is deallocated. There are design reasons behind this in order for the
concurrent scan to work, which I am going to go in detail here.

In conclusion, if the heap used to contain a lot of rows and most of the
rows were deleted subsequently without a X table lock, it will end up with
lots of empty pages. In that case, build a clustered index(plus dropping
the index later if you really want just a heap) on it will deallocate those
empty pages.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Jakob" <ja*************@hotmail.com> wrote in message
news:41*********************@dread11.news.tele.dk. ..

Hello,

I have experienced that some of my tables occupies an extremely large amount of pages but with few rows. An example is a table with 37 rows over 22000
pages !. The columns are simple integer and char. I fixed the problem by
introducing a clustered index. Now it only uses 1 page. But can anyone
explain this behaviour in SQLServer 2000 ?
regards Jakob Mathiasen