October 14, 2007 at 9:07 pm
Hi
I was looking for a script for database reindexing (dynamically), but I couldn’t find it in the script section.
😉 My requirement is depends on fragmentation level I have to do the either reindex or index defrag and save the required output in a table. And I need to pass the required database name as input parameter or all the user databases.
Can you please help me, any ideas would be highly appreciated.
binu john
October 14, 2007 at 9:17 pm
take a look in the scripts section:
http://qa.sqlservercentral.com/scripts/Index+Management/30139/
the above is a script to reindex all tables.
Lowell
October 14, 2007 at 9:37 pm
Thanks Lowell..
It doesn’t help me either.:w00t:
This script reindexes all user and system indexes in the database using the original fill factor allocated to that index.
I am looking for something which dynamically do the reindex based on fragmentation level thus saving the time and output to a table for any reports.
Binu john
October 14, 2007 at 10:15 pm
Hi,
This is there in BOL. You can modify this a bit to run for all the databases
This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.
/*Perform a 'USE ' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (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)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
October 14, 2007 at 10:50 pm
Thanks Arun..
The given script does a index defrag, but my requirement is slightly different. :hehe:
If the fragmentation level is low (eg ~30) then opt for index defrag or if greater than 30 has to do index rebuild based on saved showcontig output on a table. This will save time as defrag on highly fragmented will take more time than to do a index rebuild.
Sorry for trouble you people as am poor in T sql.:D
binu john
October 15, 2007 at 10:33 am
October 17, 2007 at 9:33 pm
Thanks Arun..that too doesnt suit to my requirement.:hehe:
I have a script which does dbcc index defrag and if required reindexing but can do for one database and need to run from same. I have to modufy this so that it will run for all dbs and running from a dba database.
CREATE procedure dbo.IndexMaintenanceForDB
@pDatabaseName varchar(50)
as
/*******************************************************************************************************
* dbo.indexMaintenanceForDB
*
* Outline: 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 500000 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. Ignore indexes less than 100 pages.
* 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
*
* usage: EXECUTE dbo.indexMaintenanceForDB 'admin'
* Notes: proc creates its tracking table if it doesn't already exist
*
declare
@sqlstr nvarchar(4000),
@ObjectOwner varchar(128),
@ObjectName varchar(255),
@IndexName varchar(255),
@status int
if object_id('UWDBA.dbo.fraglist','U') is null
CREATE TABLE UWDBA.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,
constraint pkc_fraglist__DatabaseName__ObjectOwner__ObjectName__IndexName_RecCreatedDt
primary key clustered (DatabaseName, ObjectOwner, ObjectName, IndexName, RecCreatedDt))
set nocount on
delete UWDBA.dbo.fraglist
where DatabaseName = @pDatabaseName
and RecCreatedDt < getdate() - 90
update UWDBA.dbo.fraglist
set status = status + 10
where DatabaseName = @pDatabaseName
and status < 10
declare
@table_id int,
@table varchar(100)
if object_id('#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')
if @table not in ('dtproperties')
insert #fraglist exec ('dbcc showcontig ([' + @table + ']) WITH TABLERESULTS, ALL_INDEXES')
select @table_id = min(id)
from dbo.sysobjects
where type = 'U'
and id > @table_id
end
insert UWDBA.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 f1.IndexId = 0 Then 9
When f1.CountPages < 100 Then 9
When f1.IndexId = 1 Then case
When (f1.ScanDensity > 95
AND f1.LogicalFrag < 5)Then 9
-- and f1.ObjectName in ('ERDailyRollups', 'Detail') Then 9
When (f1.ScanDensity BETWEEN 91
and 95)
AND( f1.LogicalFrag BETWEEN 5
and 15)Then 1
-- and f1.ObjectName in ('ERDailyRollups', 'Detail') Then 1
When (f1.ScanDensity < 91
AND f1.LogicalFrag > 15) Then 4
-- and f1.ObjectName in ('ERDailyRollups', 'Detail') Then 4
When (f1.ScanDensity <= 88 -- Added to rebuild cluster
AND f1.LogicalFrag < 5)Then 4
-- and f1.ObjectName in ('ERDailyRollups', 'Detail') Then 4
end
When f1.IndexId between 2
and 254 Then case
When (f1.ScanDensity > 95
AND f1.LogicalFrag < 10) Then 9
When (f1.ScanDensity BETWEEN 91
and 95)
AND (f1.LogicalFrag BETWEEN 11
and 15) Then 1
else 4
end
When f1.IndexId = 1 and
-- and f1.ObjectName <> 'Detail' and
f1.ObjectName <> 'ERDailyRollups'Then case
When (f1.ScanDensity > 95
or f1.LogicalFrag < 10) Then 9
else case When (f1.ScanDensity BETWEEN 91 AND 95 )OR( f1.LogicalFrag BETWEEN 11 AND 15) Then 1
else 4
end
end
else 9
end
from #fraglist f1
declare indexesToMaintainList insensitive cursor for
select ObjectOwner,
ObjectName,
IndexName,
status
from UWDBA.dbo.fraglist
where DatabaseName = 'PRM70ER'
-- and ObjectName not in ('fraglist',
-- 'Detail')
and status in (1,
4)
union
select ObjectOwner,
ObjectName,
IndexName,
status
from UWDBA.dbo.fraglist
where DatabaseName = 'PRM70ER'
and status in (1,4)
-- and ObjectName in ('ERDailyRollups', 'Detail')
-- and (DATEPART(dw, GETDATE()) = 7
and IndexId = 1
or (Indexid > 1
and IndexId < 255)
Order by status desc
open indexesToMaintainList
fetch next from indexesToMaintainList
into @ObjectOwner,@ObjectName,@IndexName,@status
while @@fetch_status = 0
begin
update UWDBA.dbo.fraglist
set 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 + ')'
exec sp_executesql @sqlstr
end
if @status = 4
begin
set @sqlstr = 'DBCC DBREINDEX(''' + @pDatabaseName + '.' + @ObjectOwner + '.' + @ObjectName + ''',''' +
@IndexName + ''')'
exec sp_executesql @sqlstr
end
update UWDBA.dbo.fraglist
set 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply