Fixing smallish databases which are less than 1-2 gb may be just annoying when you are dealing with multiple indexes, but try managing some of those larger ERP databases with literally thousands of tables! Talk about a database from hell, having to sift through 10 of thousands of indexes can be a real chore if you're searching for performance bottle necks. There are some great solutions out there that all cost money per instance or per site license and can get quite pricey. But just about all of those products are charging you for something you can do on your own.
The article below is an extension of my previous blog on maintaining those indexes It's the script that has evolved from some very basic loops and DMV ( dm_db_index_physical_stats ). In my script (follow the link below) You'll find that I chose to stick to a SAMPLED stats, which essentially looks at the number of compressed pages. If you're talking millions of row of data and a very short maintenance window, you're going to want to stick to SAMPLED, otherwise, you'll opt for the full DETAILED search if
A) your maintenance window is greater than the amount of time it takes for a DETAIL report to be returned
B) your database is small enough
One of the problems that I've ran into with my production databases that use a FULL recovery model is the maintaining the size of the transaction logs. The problem is that I COULD switch them over to be SIMPLE recovery models, and then the transaction log would not grow out of control during the rebuild / reorganization (Defrag) of the indexes, but I've found this not to be an ideal situation, since in my organization even though I have a maintenance window on the weekends, the business comes first, so if they require access to their data, I must make it available. Having good backups that allow the company to return up to the minute before failure using the FULL recovery model is important to us. So in the script (see link below)
Keep Your SQL Server Indexes Fragmentation Free
My DevX article was all about Sql Server 2005 and Sql Server 2008, but what about Sql Server 2000? surely you don't want to be stuck just running a performance monitor on your all your 2000 servers right? Here is the updated 2000 script to find all fragmented tables in 2000 along with the DBCC DBREINDEX wich will rebuild them for you.
Catch-22: if the table has less than 1000 rows, a rebuild will not change anything and you may be left with having to drop and recreate the index manually anyways.
The Code:
...I created a temp table manually to capture the results of SHOWCONTIG which I pipe to a grid result using the WITH TABLERESULTS hint, next this is joined against the information schema tables table. In the Where clause I specified that we are interested in 'Base Table's only so that we don't get a lot of system table specific stuff. I also added the cirteria that we want to see only fragmented tables of 5% and that have over 1000 rows. In my case my legacy databases are all well under 50gb and thus this query makes sense. This example use a cursor, as it's an old script, but I'll post up a cursor-less version as I prefer cursor-less solutions.
As always watch those log files when you are defragging indexes!
USE YourDatabaseName--Enter the name of the database you want to reindex
CREATE TABLE #db_defrag
( ObjectName VARCHAR(255),
ObjectID INT,
IndexName VARCHAR(255),
IndexID INT,
[LEVEL] INT,
Pages INT,
[ROWS] BIGINT,
MinRecordSize INT,
MaximumRecordSize INT,
AverageRecordSize INT,
ForwardedRecords INT,
Extents INT,
ExtentSwitches INT,
AverageFreeBytes DECIMAL(20,2),
AveragePageDensity DECIMAL(20,2),
ScanDensity DECIMAL(20,2),
BestCount INT,
ActualCount INT,
LogicalFragmentation DECIMAL(20,2),
ExtentFragmentation DECIMAL(20,2))
DECLARE @sql AS VARCHAR(100)
SET @sql ='DBCC SHOWCONTIG WITH TABLERESULTS'
INSERT INTO #db_defrag
EXEC (@sql)
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables tbl
INNER JOIN #db_defrag dfrg ON tbl.table_name = dfrg.ObjectName
WHERE table_type = 'base table' AND dfrg.LogicalFragmentation >= 5 AND [ROWS] > 1000
ORDER BY LogicalFragmentation DESC
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
SET @sql = 'UPDATE STATISTICS ' + @TableName
EXEC (@Sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
DROP TABLE #db_defrag
Posts
