November 1, 2006 at 9:01 am
I found the following script which seems to work fine for re-creating the indexes and removing fragmentation, but I'd like to get some suggestions on how I can modify it to use 90% fill factor vs the current fill factor.
Appreciate replies
Here's the script:
CREATE PROCEDURE dbo.usp_DBREINDEX
/*
Created: 12/18/2002
Created By: AJ Ahrens - SQL DBA AT&T Labs x3375
Purpose(s): Dynamically reindex table(s)
Note-This script keep original fill factor
*/
AS
DECLARE @TableName VARCHAR(255)
DECLARE @IndexName VARCHAR(255)
DECLARE @OrigFillFactor INTEGER
DECLARE @OuterCounter INTEGER
DECLARE @InnerCounter INTEGER
DECLARE @OuterLoop INTEGER
DECLARE @InnerLoop INTEGER
DECLARE @ClusteredYN INTEGER
DECLARE @strSQL NVARCHAR(2000)
SELECT SO.[name] tblName, MAX(SI.OrigFillFactor) OrigFillFactor,
'01/01/1900 12:00:00AM' ReIndexedDtTm, SI.[name] IndexName,
CASE WHEN SI.indid = 1 THEN 1 ELSE 0 END ClusteredYN
INTO #IndexedTables
FROM sysindexes SI
LEFT JOIN sysobjects SO ON SI.[id] = SO.[id]
WHERE xtype = 'U' AND SI.indid <> 0 AND SI.indid <> 255
GROUP BY SO.[name], SI.[name], SI.indid
SET @OuterCounter = 1
SET @OuterLoop = (SELECT COUNT(DISTINCT tblName) FROM #IndexedTables)
WHILE @OuterCounter <= @OuterLoop
BEGIN
SET @TableName = (SELECT TOP 1 tblName FROM #IndexedTables WHERE ReIndexedDtTm = '01/01/1900 12:00:00AM')
SET @InnerCounter = 1
SET @InnerLoop = (SELECT COUNT(*) FROM #IndexedTables WHERE tblName = @TableName)
WHILE @InnerCounter <= @InnerLoop
BEGIN
SET @IndexName = (SELECT TOP 1 IndexName FROM #IndexedTables WHERE ClusteredYN = 1
AND tblName = @TableName AND ReIndexedDtTm = '01/01/1900 12:00:00AM')
SET @ClusteredYN = 1
IF RTRIM(@IndexName) IS NULL
BEGIN
SET @IndexName = (SELECT TOP 1 IndexName FROM #IndexedTables WHERE ClusteredYN = 0
AND tblName = @TableName AND ReIndexedDtTm = '01/01/1900 12:00:00AM')
SET @ClusteredYN = 0
END
SET @OrigFillFactor = (SELECT OrigFillFactor FROM #IndexedTables
WHERE tblName = @TableName AND ClusteredYN = @ClusteredYN
AND IndexName = @IndexName)
SET @strSQL = 'DBCC DBREINDEX ' + '(' + CHAR(39) + @TableName + CHAR(39)
+ CHAR(44) + SPACE(1) + CHAR(39) + @IndexName + CHAR(39) + CHAR(44)
+ SPACE(1) + CONVERT(VARCHAR(3), RTRIM(@OrigFillFactor)) + ')'
PRINT @strSQL
EXEC sp_executesql @strSQL
UPDATE #IndexedTables SET ReIndexedDtTm = GETDATE() WHERE tblName = @TableName
AND ClusteredYN = @ClusteredYN AND IndexName = @IndexName
SET @InnerCounter = @InnerCounter + 1
END
SET @OuterCounter = @OuterCounter + 1
END
GO
November 1, 2006 at 9:29 am
I am bit confused as to why you want to do this, but if all you want to do is use a fixed fill factor of 90 for ALL tables, then you could replace the set @OrigFillFactor statement
SET @OrigFillFactor = (SELECT OrigFillFactor FROM #IndexedTables
WHERE tblName = @TableName AND ClusteredYN = @ClusteredYN
AND IndexName = @IndexName)
with the following statement.
SET @OrigFillFactor = 90
However, this will end up setting the fillfactor for all tables to be 90. Are you sure you want to be doing that?
November 1, 2006 at 6:41 pm
Thnxs for you input, its did the trick. Fill factors from what I have read, benefit with a large fill if the database is mainly read, and doesn't have to read multiple pages like s small fill would. However write I/O would suffer with a large fill factor as multiple pages would need to be accessed. Our current fill is 0, but one of our Peoplesoft Databases is mainly read.
It would be nice if one had the ability to run a script that would give the statistics on % of how much a table was read activity vs write.
November 2, 2006 at 2:39 pm
If you can isolate your table on physical disk, then you can set a perfmon counters with physical writes\reads for that drive, or you can set a profiler trace for that table with writes\reads, capturing all that data to the table and then writing a query for a particular time interval
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply