High fragmentation with 0 rows in the table

I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
rows into an empty table and then deletes them and keeps going in the loop
for about 6 hours. If the table is not truncated before the job starts again
the following night, the job performance degrades considerably. It turns out
that after the job is done running, there are hundreds of data pages
allocated for this table with high extent fragmentation.
My question is why these pages are not de-allocated when there are no rows
in the table to begin with? If I truncate the table and add 6 rows manually,
then only one data page is allocated to the table. The table has about 6
columns with 4 columns of data type smallint, one decimal and a couple of
char(5).Hi,
There could be several reasons. Are you commiting your transactions after
each loop? Also, the delete statement is a logged transaction, that means
everytime your script deletes the data, it is written in the transaction log
that causes space (data pages) to be allocated. Not sure how your script is
coded, so can't really be too specific.
The truncate statement is NOT logged so when you issue it, it wipes out the
table clean. That is why you are seeing this behaviour.
HTH
DeeJay Puar
MCDBA
"Adam" wrote:
> I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts again
> the following night, the job performance degrades considerably. It turns out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||do you have any indexes on this table, specificaly a clustered one, or is
this a heap ?
and how is the "delete" done, with a 'delete tablename' ?
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
>I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> rows into an empty table and then deletes them and keeps going in the loop
> for about 6 hours. If the table is not truncated before the job starts
> again
> the following night, the job performance degrades considerably. It turns
> out
> that after the job is done running, there are hundreds of data pages
> allocated for this table with high extent fragmentation.
> My question is why these pages are not de-allocated when there are no rows
> in the table to begin with? If I truncate the table and add 6 rows
> manually,
> then only one data page is allocated to the table. The table has about 6
> columns with 4 columns of data type smallint, one decimal and a couple of
> char(5).
>|||On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:
>do you have any indexes on this table, specificaly a clustered one, or is
>this a heap ?
>and how is the "delete" done, with a 'delete tablename' ?
What he said.
Would a simple "truncate table" be a quick fix?
(not that it's so hard to defrag anyway)
J.|||I am not 100% if the app is doing a commit after each delete but even if that
was not the case, after the final commit at the end of the 6th hour,
shouldn't the empty pages get deallocated?
"DeeJay Puar" wrote:
> Hi,
> There could be several reasons. Are you commiting your transactions after
> each loop? Also, the delete statement is a logged transaction, that means
> everytime your script deletes the data, it is written in the transaction log
> that causes space (data pages) to be allocated. Not sure how your script is
> coded, so can't really be too specific.
> The truncate statement is NOT logged so when you issue it, it wipes out the
> table clean. That is why you are seeing this behaviour.
> HTH
> DeeJay Puar
> MCDBA
> "Adam" wrote:
> > I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts again
> > the following night, the job performance degrades considerably. It turns out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >|||At the moment, there are no indexes and yes, my next plan of attack is to put
a clustered index to see how that pans out. I don't know for sure if that
will solve the problem 100% because now, the delete would also have to do
more IOs to maintain the clustered index. Also, a table with the clustered
index will get fragmented over time.
The SQL is doing "delete table".
"David J. Cartwright" wrote:
> do you have any indexes on this table, specificaly a clustered one, or is
> this a heap ?
> and how is the "delete" done, with a 'delete tablename' ?
> "Adam" <Adam@.discussions.microsoft.com> wrote in message
> news:58830C32-198D-4DFF-9149-DBA76F8FB444@.microsoft.com...
> >I have a job in SQL 7 with sp3 that goes in a loop and inserts about 2 to 6
> > rows into an empty table and then deletes them and keeps going in the loop
> > for about 6 hours. If the table is not truncated before the job starts
> > again
> > the following night, the job performance degrades considerably. It turns
> > out
> > that after the job is done running, there are hundreds of data pages
> > allocated for this table with high extent fragmentation.
> >
> > My question is why these pages are not de-allocated when there are no rows
> > in the table to begin with? If I truncate the table and add 6 rows
> > manually,
> > then only one data page is allocated to the table. The table has about 6
> > columns with 4 columns of data type smallint, one decimal and a couple of
> > char(5).
> >
>
>|||The current fix in fact is to truncate the table before the batch starts,
however, if SQL Server is accumulating empty pages with 0 rows in the table
then the performance starts to degrade over time while the 6 hour job is
running. If I can figure out the reason and the solution to why SQL Server is
not deallocating empty pages, then I can stop it from taking the time for
doing table scan through these empty pages and can tweek many other nightly
jobs that I am sure are facing a similar situation.
thoughts?
"jxstern" wrote:
> On Tue, 13 Dec 2005 15:12:23 -0500, "David J. Cartwright"
> <davidcartwright@.hotmail.com> wrote:
> >do you have any indexes on this table, specificaly a clustered one, or is
> >this a heap ?
> >and how is the "delete" done, with a 'delete tablename' ?
> What he said.
> Would a simple "truncate table" be a quick fix?
> (not that it's so hard to defrag anyway)
> J.
>
>|||On Thu, 15 Dec 2005 06:21:02 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>At the moment, there are no indexes and yes, my next plan of attack is to put
>a clustered index to see how that pans out.
It should.
If you delete from a table without a clustered index, SQLServer leaves
the pages deallocated until ... The Ghost wakes up and reclaims them,
which it does when it feels like it, and then it takes as long as it
takes.
Fun, huh?
Add the clustered index, if for no other reason than this.
Google the newsgroup archives or the web for more about the ghost,
there's very little about it in MSDN or BOL.
Josh