Defrag Script

Hi all,
I'm writing my own defrag script. I've set it up so under certain conditions
it will run an IndexDefrag and under others a Reindex. I wanted to post the
conditions I've set and see if anyone has any feedback that can improve them.
I have two temporary tables 1 contains the tables and indexes, and one the
ShowContig information.
The IndexDefrag test is:
UPDATE #TableIndexList
SET ReqAction = 'I'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE b.Pages BETWEEN 16 AND 4096
AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
OR b.AveragePageDensity < 75)
The reindex criteria is:
UPDATE #TableIndexList
SET ReqAction = 'R'
FROM #TableIndexList AS a
INNER JOIN #FragStats AS b
ON a.ObjectID = b.ObjectID
WHERE (b.Pages > 16
AND (b.LogicalFragmentation > 40
OR b.AveragePageDensity > 95))
OR (b.Pages > 4096
AND (b.LogicalFragmentation > 20
OR b.AveragePageDensity < 75))
As advised any feedback would be greatly appreciated...Have you compared the before and after results. Sometimes due to the way you
have constructed your table you will find residual fragmentation after
running such scripts will be almost the same.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> Hi all,
> I'm writing my own defrag script. I've set it up so under certain
> conditions
> it will run an IndexDefrag and under others a Reindex. I wanted to post
> the
> conditions I've set and see if anyone has any feedback that can improve
> them.
> I have two temporary tables 1 contains the tables and indexes, and one the
> ShowContig information.
> The IndexDefrag test is:
> UPDATE #TableIndexList
> SET ReqAction = 'I'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE b.Pages BETWEEN 16 AND 4096
> AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> OR b.AveragePageDensity < 75)
> The reindex criteria is:
> UPDATE #TableIndexList
> SET ReqAction = 'R'
> FROM #TableIndexList AS a
> INNER JOIN #FragStats AS b
> ON a.ObjectID = b.ObjectID
> WHERE (b.Pages > 16
> AND (b.LogicalFragmentation > 40
> OR b.AveragePageDensity > 95))
> OR (b.Pages > 4096
> AND (b.LogicalFragmentation > 20
> OR b.AveragePageDensity < 75))
> As advised any feedback would be greatly appreciated...|||Thats interesting.. ..I'm identifying heaps for manual defrags, I'm excluding
blobs, is it a scenario where the table is busy and the IndexDefrag is
skipping the pages?
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>|||or maybe when there's not enough disk space to run DBCC Reindex on a large
table
"Hilary Cotter" wrote:
> Have you compared the before and after results. Sometimes due to the way you
> have constructed your table you will find residual fragmentation after
> running such scripts will be almost the same.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "BenUK" <BenUK@.discussions.microsoft.com> wrote in message
> news:AD82F542-DD91-4973-B0C1-8DAB523A7EF8@.microsoft.com...
> > Hi all,
> >
> > I'm writing my own defrag script. I've set it up so under certain
> > conditions
> > it will run an IndexDefrag and under others a Reindex. I wanted to post
> > the
> > conditions I've set and see if anyone has any feedback that can improve
> > them.
> > I have two temporary tables 1 contains the tables and indexes, and one the
> > ShowContig information.
> >
> > The IndexDefrag test is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'I'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE b.Pages BETWEEN 16 AND 4096
> > AND ((b.LogicalFragmentation BETWEEN 20 AND 40)
> > OR b.AveragePageDensity < 75)
> >
> > The reindex criteria is:
> > UPDATE #TableIndexList
> > SET ReqAction = 'R'
> > FROM #TableIndexList AS a
> > INNER JOIN #FragStats AS b
> > ON a.ObjectID = b.ObjectID
> > WHERE (b.Pages > 16
> > AND (b.LogicalFragmentation > 40
> > OR b.AveragePageDensity > 95))
> > OR (b.Pages > 4096
> > AND (b.LogicalFragmentation > 20
> > OR b.AveragePageDensity < 75))
> >
> > As advised any feedback would be greatly appreciated...
>
>

Deferred Updates versus In-Place Updates

The following question was posted on http://support.microsoft.com/kb/195760/en-us:

Q. What are the conditions under which deferred updates occur in SQL Server 7.0?

A. In SQL Server 7.0, all updates are in-place (direct), provided that the column or columns participating in the clustered index key are not changed. If a change is made to a UNIQUE clustered key, the update plan runs in a hybrid mode where the query processor combines direct and deferred updates. If the clustered key is not UNIQUE, all of the updates are run as deferred.

You can use SHOWPLAN to see examples of how this behavior works. Look for the SPLIT and COLLAPSE operators in the plan. If you find a SPLIT below the clustered index update, one of the clustering keys must have been changed. If a COLLAPSE operator is found, the update is running in a hybrid mode; SQL Server collapses delete and insert to the same key values into an in-place update.

NOTE: This behavior holds true for any index.

Has this behavior changed in SQL Server 2005?

Ken, whenever you are modifying the key columns of an index, the operation will be implemented as a delete followed by an insert, as the row needs to move within the B-Tree from the old location to the new. This can be explictly visible in the query plan showed in SHOWPLAN with the SPLIT operator, or be implicitly performed at the Storage Engine level. In SQL 2005, the number of cases where the SPLIT operator is employed is somewhat decreased, but it is still used. See the following trivial example:

use tempdb
go

create table t(p int primary key clustered)
go

update t set p = p + 1
go

Please let me know if you have more questions on this.
Thanks

|||

Okay, that tells me how changes to the primary key columns will be processed. Now what will happen if only the non-key columns are changed?

Ken

|||When only non key columns are updated, changes are performed in place, i.e. without moving the row inside the B-Tree. This is of course a much cheaper operation. You might want to take a look at a new SQL 2005 feature, the ability to include nonkey columns in nonclustered indexes.

Bottom line: an update statement will not maintain the nonclustered indexes whose columns are not being modified, will trigger row movements inside the indexes whose keys are being updated, and perform in place update to indexes whose only non key columns are being modified.

Deferred Updates versus In-Place Updates

The following question was posted on http://support.microsoft.com/kb/195760/en-us:

Q. What are the conditions under which deferred updates occur in SQL Server 7.0?

A. In SQL Server 7.0, all updates are in-place (direct), provided that the column or columns participating in the clustered index key are not changed. If a change is made to a UNIQUE clustered key, the update plan runs in a hybrid mode where the query processor combines direct and deferred updates. If the clustered key is not UNIQUE, all of the updates are run as deferred.

You can use SHOWPLAN to see examples of how this behavior works. Look for the SPLIT and COLLAPSE operators in the plan. If you find a SPLIT below the clustered index update, one of the clustering keys must have been changed. If a COLLAPSE operator is found, the update is running in a hybrid mode; SQL Server collapses delete and insert to the same key values into an in-place update.

NOTE: This behavior holds true for any index.

Has this behavior changed in SQL Server 2005?

Ken, whenever you are modifying the key columns of an index, the operation will be implemented as a delete followed by an insert, as the row needs to move within the B-Tree from the old location to the new. This can be explictly visible in the query plan showed in SHOWPLAN with the SPLIT operator, or be implicitly performed at the Storage Engine level. In SQL 2005, the number of cases where the SPLIT operator is employed is somewhat decreased, but it is still used. See the following trivial example:

use tempdb
go

create table t(p int primary key clustered)
go

update t set p = p + 1
go

Please let me know if you have more questions on this.
Thanks

|||

Okay, that tells me how changes to the primary key columns will be processed. Now what will happen if only the non-key columns are changed?

Ken

|||When only non key columns are updated, changes are performed in place, i.e. without moving the row inside the B-Tree. This is of course a much cheaper operation. You might want to take a look at a new SQL 2005 feature, the ability to include nonkey columns in nonclustered indexes.

Bottom line: an update statement will not maintain the nonclustered indexes whose columns are not being modified, will trigger row movements inside the indexes whose keys are being updated, and perform in place update to indexes whose only non key columns are being modified.