Very Computer

    Can any one give me some background info on some thing I am ashamed to
admit to not knowing?
    I an developing a archive / purge system in TSQL to remove aged data
from a rapidly expanding databases.

    When I delete rows from tables, will SQL allocate any free extents
automatically when a new one is required or will it allocate from the last
object onwards? ( i.e. do I need to reload / reindex the tables regularly? )

    I am aware of issues due to fragmentation of data / indexes and of the
need to regularly update statistics - is there anything else I should worry
about?

    I was not involved in the db design, and clustered indexes are used
where I wouldn't.  Any issues there?

    I presume that SEM reports available space from the last object in the
db. Is this correct?

    The sites ( about 85 of them ) run close to 24 * 7. Any windows of
opportunity are limited to a few hours in the early hours of the morning. So
any thing other than routine maintenance needs to happen infrequently and
speedily. I have starting having some success rebuilding clustered indexes
and resorting the data, but the best that this can be done is one
(substantial ) table / night.

    Any information on this would be gratefully received.

    Many thanks,
    Dominic Young.

Dominic,

Some thoughts:

For tables without cl ix:
SQL Server will allocate new pages "at the end" of the tables linked list.
SQL Server will try to get a page "near" the end-page. It can go "backwards"
if free pages are found earlier in the page chain (due to earlier
delete/update). But an "earlier" page is not re-used for insert until it is
empty. End-result is that you will have potentially a lot pf free space
unless your "purging" is (kind of) massive. This is a non-issue in 7, btw
(no linked list).

For tables with cl ix:
Rows go where they belong, according to the clustering key. Due to
page--splits (updates in "the middle"), you can get a fragmented table.

If SQL Server is performing scans, fragmentation can hurt you. Read ahead
will be less effective as well.

Do not trust EM free space reporting. I'd use sp_spaceused (or write my own,
possibly based on sp_spaceused). Make sure that you DBCC UPDATEUSAGE if you
want "correct" free space info.
Free space reporting is _not_ based on last page. It sums the number of used
pages for each table from the sysindexes table and compares that to the
total db size.

Use DBCC SHOWCONTIG is you want to check fragmentation issues.
--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.

Quote:>     Can any one give me some background info on some thing I am ashamed to
> admit to not knowing?
>     I an developing a archive / purge system in TSQL to remove aged data
> from a rapidly expanding databases.

>     When I delete rows from tables, will SQL allocate any free extents
> automatically when a new one is required or will it allocate from the last
> object onwards? ( i.e. do I need to reload / reindex the tables
regularly? )

>     I am aware of issues due to fragmentation of data / indexes and of the
> need to regularly update statistics - is there anything else I should
worry
> about?

>     I was not involved in the db design, and clustered indexes are used
> where I wouldn't.  Any issues there?

>     I presume that SEM reports available space from the last object in the
> db. Is this correct?

>     The sites ( about 85 of them ) run close to 24 * 7. Any windows of
> opportunity are limited to a few hours in the early hours of the morning.
So
> any thing other than routine maintenance needs to happen infrequently and
> speedily. I have starting having some success rebuilding clustered indexes
> and resorting the data, but the best that this can be done is one
> (substantial ) table / night.

>     Any information on this would be gratefully received.

>     Many thanks,
>     Dominic Young.

Dom,

Inline:

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.

Quote:> Tibor,

>     Thank you for your thoughts. Always appreciated.

>     Let me see if I have understood you correctly.

>     I am concerned with the databases running out of space.

>   For tables with no cl ix - this is not a problem, as SQL server will
reuse
> pages. ( A problem for me as most of the tables have cl ix )

Yes, but it will take a while before the space will be reclaimed. All rows
from the page has to be removed until the space can be reclaimed.

Quote:

>     For those with a cl ix, it will of course insert  the data in the
> correct sequence. - Remember that I said I wasn't involved in database
> design etc, a lot of our v large tables have a clustered index on a PK
> consisting of an Identity column ( not my fault, but my problem ;-) ).
This
> means that new data will allways be appended at the end of the object at
the
> next page. ( Right ? ).

Not neccecarily at the next page. Any ol' page will do. The page could have
a lower page address. Check out books online about the "extent" concept. It
is hard to explain without a whiteboard :-). Understanding the extent
concept is the key to understanding space allocation and reporting.

Quote:> Will there be a scenario when the database reports
>  say ) 500Mb free, but one 100byte insert will fail as there is now space
> left at the 'end' of the database?

No. See my above comments.

Quote:

>     I think I am going to have to do away with cl ix  (it is a OLTP
system )
> and copy in / out the tables to reduce fragmentation. ( At one site, on
our
> large tables  ( 3m+ rows ) Scan density was around 18% and I reduced dbcc
> checkdb by one hour by rebuilding the clustered index and resorting the
> data ).

>     Have you ever carried out any archiving processes in the past to
reclaim
> space?

No, but I am certain that there are lots of experience here, all eager to
jump in!

Quote:

>     Thanks,
>     Dom.

Tibor,

    Thank you for your thoughts. Always appreciated.

    Let me see if I have understood you correctly.

    I am concerned with the databases running out of space.

  For tables with no cl ix - this is not a problem, as SQL server will reuse
pages. ( A problem for me as most of the tables have cl ix )

    For those with a cl ix, it will of course insert  the data in the
correct sequence. - Remember that I said I wasn't involved in database
design etc, a lot of our v large tables have a clustered index on a PK
consisting of an Identity column ( not my fault, but my problem ;-) ). This
means that new data will allways be appended at the end of the object at the
next page. ( Right ? ). Will there be a scenario when the database reports
 say ) 500Mb free, but one 100byte insert will fail as there is now space
left at the 'end' of the database?

    I think I am going to have to do away with cl ix  (it is a OLTP system )
and copy in / out the tables to reduce fragmentation. ( At one site, on our
large tables  ( 3m+ rows ) Scan density was around 18% and I reduced dbcc
checkdb by one hour by rebuilding the clustered index and resorting the
data ).

    Have you ever carried out any archiving processes in the past to reclaim
space?

    Thanks,
    Dom.

Tibor,

Thanks for your input.

    My prime objective is to introduce an archiving process with the
intention of preventing the database from growing too quickly. This was
handed to me when I took the assignment. I feel that I can now do this with
out worrying ( at this time ) about the database / index design ( which
everyone admits is a dog's dinner ).

    Once I have got this bedded down and we are seeing some results, I can
go to work on table / index / query design.

    Many thanks,
    Dom.

    PS - It is several months since I last visited this newsgroup. It is
good to see it is still as usefull as it ever was.