How to get rid of Ghost Pages

For those of you who may not have heard of ghost pages, when SQL Server
deletes all the rows in a data page, it doesn't re-use it untill a seperate
process cleans up such pages. These pages are called ghost pages.
I have a SQL7 production database where the process to clean up the ghost
pages is not waking up. I just read some where that there is a trace that can
be turned on in order to stop this process from waking up. Can someone please
tell me what the trace number is that I can turn off?
Here are some more details... I have a table in production that is used as
"scratch pad" and therefore encounters a lot of inserts/deletes. The table at
any given time will have a couple of rows to may be a couple of hundred rows.
Over time, the number of ghost pages increase and the SQLs doing joins
against this table treat it as if it has a lot of rows and as a result, the
underlying query plan changes and causes the SQLs to run for a long time.
Here is the interesting part... I noticed at one point there were only a
couple of rows in the table and the number of data pages showing up in DBCC
SHOWCONTIG output where in hundreds and the extent fragmentation was very
high. Also, in some cases, every data page was in a different extent even
though the table only had a couple of rows. When I created a clustered index
and droped the index, the ghost pages disappeared and made a day and night
difference in performance. When I left the clustered index on the table, the
ghost pages eventually appeared again. From that point onwards, when I
dropped and re-created the clustered index, the ghost pages disappeared one
more time and the underlying SQLs started running much faster again.
Does anyone know of a permenant fix to this?
Thanks.
Adam
Can you post the list of trace flags that you have on?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:6DB63A27-CBE7-49E3-9AD7-B61C1BD49C53@.microsoft.com...
> For those of you who may not have heard of ghost pages, when SQL Server
> deletes all the rows in a data page, it doesn't re-use it untill a
> seperate
> process cleans up such pages. These pages are called ghost pages.
> I have a SQL7 production database where the process to clean up the ghost
> pages is not waking up. I just read some where that there is a trace that
> can
> be turned on in order to stop this process from waking up. Can someone
> please
> tell me what the trace number is that I can turn off?
> Here are some more details... I have a table in production that is used
> as
> "scratch pad" and therefore encounters a lot of inserts/deletes. The table
> at
> any given time will have a couple of rows to may be a couple of hundred
> rows.
> Over time, the number of ghost pages increase and the SQLs doing joins
> against this table treat it as if it has a lot of rows and as a result,
> the
> underlying query plan changes and causes the SQLs to run for a long time.
> Here is the interesting part... I noticed at one point there were only a
> couple of rows in the table and the number of data pages showing up in
> DBCC
> SHOWCONTIG output where in hundreds and the extent fragmentation was very
> high. Also, in some cases, every data page was in a different extent even
> though the table only had a couple of rows. When I created a clustered
> index
> and droped the index, the ghost pages disappeared and made a day and night
> difference in performance. When I left the clustered index on the table,
> the
> ghost pages eventually appeared again. From that point onwards, when I
> dropped and re-created the clustered index, the ghost pages disappeared
> one
> more time and the underlying SQLs started running much faster again.
> Does anyone know of a permenant fix to this?
> Thanks.
> Adam