December 13, 2005 at 1:45 pm
I found a stored procedure on a sqlserver performance website that looked like just what I was looking for. The stored procedure is used to either reindex or defrag fragmented indexes. The logic is to defrag indexes with over 10000 pages and a specified scandensity, and reindex ones with less than 10000 pages and the specified level of scandensity. Unfortunately, it doesn't seem to be working correctly and I can't figure out why. I'm hoping a new set of eyes will see something obvious that I'm missing. The problem I'm running into is I have indexes with scandensity lower than what I specifiy with more than 10000 pages where the clustered index has a higher scandensity and over 10000 pages. The stored procedure should fall through to what is highlighted in blue, but is falling through to what is highlighted in red instead. Can anybody see the problem, or have a similar script that I can use?
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure dbo.usp_IndexMaintenance
@pDatabaseName varchar(128), @ScanDensity decimal
as
/*******************************************************************************************************
* master.dbo.indexMaintenanceForDB
* Bill Wunder
* Identify which indexes need to be maintained and which maintenance operation to
* use. If the scan density is less than 90% maintain the index. If the index has
* over 10000 paged defrag it otherwise reindex it. If a table has a clustered index
* that will be maintained by a reindex do not maintain other indexes because they
* will be rebuilt when the clustered index is reindexed. Keep all fragmentation
* history for further analysis.
*
* Statistic Description
* Pages Scanned Number of pages in the table or index.
* Extents Scanned Number of extents in the table or index.
* Extent Switches Number of times the DBCC statement moved from one extent to another
* while it traversed the pages of the table or index.
* Avg. Pages per Extent Number of pages per extent in the page chain.
* Scan Density [Best Count: Actual Count] Best count is the ideal number of extent
* changes if everything is contiguously linked. Actual count is the actual
* number of extent changes. The number in scan density is 100 if everything
* is contiguous; if it is less than 100, some fragmentation exists. Scan
* density is a percentage.
* Logical Scan Fragmentation Percentage of out-of-order pages returned from scanning the leaf pages of
* an index. This number is not relevant to heaps and text indexes. An out of
* order page is one for which the next page indicated in an IAM is a
* different page than the page pointed to by the next page pointer in the
* leaf page.
* Extent Scan Fragmentation Percentage of out-of-order extents in scanning the leaf pages of an index.
* This number is not relevant to heaps. An out-of-order extent is one for
* which the extent containing the current page for an index is not physically
* the next extent after the extent containing the previous page for an index.
* Avg. Bytes free per page Average number of free bytes on the pages scanned. The higher the number,
* the less full the pages are. Lower numbers are better. This number is also
* affected by row size; a large row size can result in a higher number.
* Avg. Page density (full) Average page density (as a percentage). This value takes into account row
* size, so it is a more accurate indication of how full your pages are. The
* higher the percentage, the better.
*
* status
* 0 - under consideration
* 1 - defrag
* 2 - defrag in progress
* 3 - defrag complete
* 4 - reindex
* 5 - reindex in progress
* 6 - reindex complete
* 9 - eliminated from consideration
* 10 - 19 - historical
*
* Updates:
* 030613 - Magnus Andersson: Updated to handle object names with spaces, use update statistics after defrag and save start and end time.
********************************************************************************************************/
IF @pDatabaseName IN ('master','tempDB')
BEGIN
RETURN
END
declare @sqlstr nvarchar(4000),
@sqlstrStats nvarchar(4000),
@ObjectOwner varchar(128),
@ObjectName varchar(255),
@IndexName varchar(255),
@status int
if object_id('master.dbo.fraglist','U') is null
CREATE TABLE master.dbo.fraglist (
DatabaseName varchar (128),
ObjectOwner varchar(128),
ObjectName varchar (255),
ObjectId int,
IndexName varchar(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal,
RecCreatedDt datetime default getdate(),
Status tinyint default 0,
StartTime datetime,
EndTime datetime,
constraint pkc_fraglist__DatabaseName__ObjectOwner__ObjectName__IndexName_RecCreatedDt
primary key clustered (DatabaseName, ObjectOwner, ObjectName, IndexName, RecCreatedDt))
set nocount on
-- delete archive over 90 days
delete master.dbo.fraglist
where DatabaseName = @pDatabaseName
and RecCreatedDt < getdate() - 90
-- archive previous run
update master.dbo.fraglist
set status = status + 10
where DatabaseName = @pDatabaseName
and status < 10
-- fragmentation study (find 1s and 4s)
select @sqlstr = 'use [' + @pDatabaseName + ']
declare @table_id int
declare @table varchar(100)
if object_id(''tempdb.dbo.#fraglist'',''U'') is not null
drop table #fraglist
CREATE TABLE #fraglist (
ObjectName varchar (255),
ObjectId INT,
IndexName varchar (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
set nocount on
select @table_id = min(id) from dbo.sysobjects where type = ''U''
while @table_id is not null
begin
select @table = object_name(@table_id)
if @table not in (''dtproperties'')
insert #fraglist exec (''dbcc showcontig(['' + @table + '']) WITH FAST, TABLERESULTS, ALL_INDEXES'')
select @table_id = min(id) from dbo.sysobjects where type = ''U'' and id > @table_id
end
insert master.dbo.fraglist (
DatabaseName,
ObjectOwner,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag,
Status)
select db_name(),
isnull(user_name(objectproperty(ObjectId, ''OwnerId'')),''dbo''),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag,
Status = case when IndexId = 0 -- heap
then 9 -- eliminate from consideration
when IndexId = 1 -- clustered index
then case when ScanDensity > ' + CAST(@ScanDensity as varchar(10)) + '
then 9 -- eliminate from consideration
when CountPages < 10000
then 4 -- reindex
else 1 -- defrag
end
when IndexId between 2 and 254 -- nonclustered index
then case when (select count(1) -- clustered will be defragged
from #fraglist
where ObjectName = ObjectName
and IndexId = 1
and ScanDensity <= ' + CAST(@ScanDensity as varchar(10)) + '
and CountPages > 10000) > 0
then case when ScanDensity > ' + CAST(@ScanDensity as varchar(10)) + '
then 9 -- eliminate from consideration
when CountPages < 10000
then 4 -- reindex
else 1 -- defrag
end
when (select count(1) -- clustered will be reindexed
from #fraglist
where ObjectName = ObjectName
and IndexId = 1
and ScanDensity <= ' + CAST(@ScanDensity as varchar(10)) + '
and CountPages < 10000) > 0
then 9 -- eliminate from consideration
else -- no clustered index on table
case when ScanDensity > ' + CAST(@ScanDensity as varchar(10)) + '
then 9 -- eliminate from consideration
when CountPages < 10000
then 4 -- reindex
else 1 -- defrag
end
end
else 9 -- IndexId = 255 for text column pointer
end
from #fraglist'
exec sp_executesql @sqlStr
-- perform identified required maintenance
declare indexesToMaintainList cursor
for
select ObjectOwner, ObjectName, IndexName, status
from master.dbo.fraglist
where DatabaseName = @pDatabaseName
and ObjectName <> 'fraglist'
and status in (1,4)
open indexesToMaintainList
fetch next from indexesToMaintainList into @ObjectOwner, @ObjectName, @IndexName, @status
while @@fetch_status = 0
begin
-- mark as current maintenance candidate
update master.dbo.fraglist
set StartTime = getdate(),
status = case when @status = 1 then 2
when @status = 4 then 5
end
where DatabaseName = @pDatabaseName
and ObjectOwner = @ObjectOwner
and ObjectName = @ObjectName
and IndexName = @IndexName
and status in (1,4)
if @status = 1
begin
set @sqlstr = 'DBCC INDEXDEFRAG([' + @pDatabaseName + '],[' + @ObjectName + '],[' + @IndexName + '])'
set @sqlstrStats = 'UPDATE STATISTICS [' + @pDatabaseName + '].' + @ObjectOwner + '.[' + @ObjectName + ']' + ' [' + @IndexName + '] WITH SAMPLE 100 PERCENT'
exec sp_executesql @sqlstr
exec sp_executesql @sqlstrStats
end
if @status = 4
begin
set @sqlstr = 'DBCC DBREINDEX(''' + @pDatabaseName + '.' + @ObjectOwner + '.' + @ObjectName + ''',''' + @IndexName + ''')'
exec sp_executesql @sqlstr
end
-- mark as maintenance complete
update master.dbo.fraglist
set EndTime = getdate(),
status = case when @status = 1 then 3
when @status = 4 then 6
end
where DatabaseName = @pDatabaseName
and ObjectOwner = @ObjectOwner
and ObjectName = @ObjectName
and IndexName = @IndexName
and status in (2,5)
fetch next from indexesToMaintainList into @ObjectOwner, @ObjectName, @IndexName, @status
end
deallocate indexesToMaintainList
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
December 13, 2005 at 2:30 pm
It looks to me like you are hitting the second "Then 9 -- eliminate from consideration" in the case where the clustered index is greater than the density you specify and you have > 10000 pages.
Regards, Jim C
December 13, 2005 at 2:47 pm
Thanks for the reply. Actually, to troubleshoot this I changed the status to different numbers to figure out where it was actually falling through to and it is going to what is highlighted in red, which doesn't make sense. Here is the indexes from the table in question and what there page count and scan density were. I passed a value of 50 as scandensity and thought indexid 5 would have been defragged based on the value, but it wasn't.
| IndexName | IndexId | page cont | scandensity | status |
| pk1_ship_rec | 1 | 428353 | 100 | 9 |
| IX_customer_location | 2 | 19800 | 81 | 9 |
| origin_shipper_name2 | 3 | 36643 | 90 | 9 |
| origin_zip2 | 4 | 28719 | 91 | 9 |
| shiprec_ShiprecSearchidCustkeyShipreckeyStatusOriginflagNameAddr1CityStateZip | 5 | 127203 | 41 | 9 |
| shiprec_ShiprecShipreckeyStatusOriginflagConsigneeflagNameCityState | 6 | 61572 | 97 | 9 |
December 13, 2005 at 3:00 pm
I'm a little confused on why you are converting the @scanDensity to a varchar for comparison with the Scandensity decimal value. i've never seen that before.
case when ScanDensity > ' + CAST(@ScanDensity as varchar(10)) + '
not that it will make a difference, it just seems odd to me.
Regards, Jim C
December 13, 2005 at 3:07 pm
I found the stored procedure on a sqlserver performance website and made some modifications to it. The original stored procedure was actually checking for a hardcoded logical fragmentation value, but I wanted to use it to check scan density and pass it a value instead of hardcoding it. If you look closely at the stored procedure you'll see that the insert statement is being created into a variable @sqlstr nvarchar(4000) and then executed with exec sp_executesql @sqlStr. Since the variable is nvarchar I had to cast the scandensity variable to varchar. I also tried this with hardcoding the value and it had the same results.
December 15, 2005 at 12:10 pm
...where objectname = objectname. Your count(1) will be > 0 for every row if any row qualifies. Try this:
when (select count(1) -- clustered will be reindexed
from #fraglist sub
where main.ObjectName = sub.ObjectName
and IndexId = 1
and ScanDensity <= ' + CAST(@ScanDensity as varchar(10)) + '
and CountPages 0
then 9 -- eliminate from consideration
else -- no clustered index on table
case when ScanDensity > ' + CAST(@ScanDensity as varchar(10)) + '
then 9 -- eliminate from consideration
when CountPages < 10000
then 4 -- reindex
else 1 -- defrag
end
end
else 9 -- IndexId = 255 for text column pointer
end
from #fraglist' main
December 15, 2005 at 1:32 pm
Thanks for the response. I think you have identified what my problem is. I have decided to use a different stored procedure though, because this one assumes that reindexing a clustered index will automatically reindex all indexes for the object. In testing I see that this is only true for a non-unique clustered index. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply