March 3, 2009 at 10:23 am
I am using the script from SQL 2000 BOL (same code in SQL 2005 BOL) to reindex my tables as necessary. It has worked wonderfully for years, on SQL 2000 and 2005, but recently, in the contact position I just came into, I encountered a problem. It doesn't take Schemas into account.
I am trying to figure out a way to take schemas/user-owned objects into account so this will work for 2000 and 2005. Unfortunately, the DBCC ShowContig command does not return the schema of a table, so when the DBReindex command is called it cannot differentiate the table properly if it belongs to anything besides DBO.
I was thinking I could use the object ID, as it is unique across schemas, but no, DBReindex only accept a table name and index name. In 2005 I could use the functions oibject_schema_name and object_name, but the object_schema_name function does not exist in SQL 2000.
Anyone found a way around this?
BOL Code:
/*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
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
March 3, 2009 at 11:04 am
It looks like you can just replace this code
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
with this
DECLARE tables CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
March 3, 2009 at 12:01 pm
It helps, but doesn't fox the problem. The problem is the Showcontig output, which gets pumped straight into the FragList table, then the data from that table is pulled out to pass to DBReindex, and the data in the fraglist table, due to what ShowContig puts out, does not contain the schema name.
My best solution so far is to put a user function on SQL 2000 which will look up the schema/user in sysusers, joined with sysobjects, then pass it to DBReindex. Unfortunately you have to call user function with a "dbo." prefix, and in SQL 2005 there is no prefix required for the built-in "object_schema_name" function.
Part of the new code for SQL 2000:
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @Schema, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @Schema + '.' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @Schema, @tablename
END
My latest mods:
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC DBREINDEX (''' + RTrim(@TableName) + ''', ' + RTRIM(@IndexName) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC DBREINDEX ([' + (Select dbo.OBJECT_SCHEMA_NAME(@objectid) + '.' + Object_Name(@objectid)) +'],''' + RTRIM(@IndexName) + ''', 90)'
--Print @execstr
EXEC (@execstr)
SELECT @execstr = 'DBCC UPDATEUSAGE (0,[' + (Select dbo.OBJECT_SCHEMA_NAME(@objectid) + '.' + Object_Name(@objectid)) + '])'
--Print @execstr
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @IndexName, @frag
END
Unfortunately, while it does not igve me any errors, it also does not output any information about what it did, like the original did. Trying to figure that out now.
I'd still like to be able to come up with one script like this that can run on both versions and handle schemas.
Thanks,
Chirs
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
March 3, 2009 at 12:56 pm
I think it works... seems OK to me. Yes showcontig only gives the table name back but its uses the object ID to run the defrag
DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
If you need to see in the output what object it defrags, just output a select from the sys.objects table using the object id used in the indexdefrag.
March 4, 2009 at 10:46 am
Sorry, apparently I confused some of the code. I am using DBREINDEX, not INDEXDEFRAG, and it wil not accept an objectID.
After more research though, I don't think I am going to try to bridge 2000 and 2005 with this script, as 2005 should use Alter Index, a command that does not exist in 2000.
I have gotten the script to work, once I created my own object schema name function, which will have to be used for SQL2000 and SQL 2005 through SP1. In SP2 MS added the object schema name function.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply