DBCC SHOWCONTIG
-
Does anyone know how resource intensive of a process that running DBCC SHOWCONTIG really is? Also, is there a way to store the results of a DBCC SHOWCONTIG if it is run as a scheduled job? Any input is appreciated!
Anne
-
Answer to your first question : I am not sure how much resource intensive dbcc showcontig() is but yes if you are using this for rebuilding your indexes then the process of rebuliding index is time and resource intensive.
And in SQL Server 2000 yes you can store the result of output of a DBCC Showcontig()
We use the command
DBCC SHOWCONTIG()
One of the big advances between SQL Server 2000 and previous versions of SQL Server was in this simple, yet crucial command. DBCC SHOWCONTIG is the tool supplied with SQL Server to check how fragmented an index is. In previous versions of SQL Server (7.0 and earlier), this command would only output text. This is fine if the command is being used on a manual basis. However, for automation purposes, it creates serious problems. It means you need to cycle though each table and output to text file, then construct a cumbersome process for reading and interpreting the textual output in order to obtain the information you’re after.
SQL Server 2000 introduced a key clause to the DBCC SHOWCONTIG() command, namely WITH TABLERESULTS. This means you can run the command and capture the output straight into a table, rather than having to output to text file and include a layer of clumsy XP_CMDSHELL manipulation of text files.
This means that in SQL Server 2000, you can construct a procedure to cycle through your tables, running DBCC SHOWCONTIG on them, capturing the fragmentation information from the command on each into a table. You can then cycle through the results, conditionally taking defragmentation action on the indexes, depending on how fragmented they are. This is what the accompanying stored procedure does.
Thanks,
-
I have a scheduled job that defrags all indexes in all databases based upon DBCC SHOWCONTIG results. Over the weekend the job runs in about 30 seconds to handle about 1000 tables in our Quality Assurance environment and a couple minutes to do the same thing in our Production environment. During the week the same job takes about 20 minutes in Production and about 3 minutes in Quality Assurance due to the defragmentation part of the job (no significant changes over the weekend). However the defrag DBCC command is very cooperative so the longer run time isn't a concern. Our Production databases total to about the 20 GB I think (guestimate). This obviously isn't a terribly scientific set of data, but it might give you some sort of idea of what to expect. Perhaps someone else has went to the trouble to capture better statistics for you.
When defining the job, open the step that performs the DBCC SHOWCONTIG (or whatever output you wish to capture) and switch to the Advanced tab. You can check "Append output to step History" so that it is recorded with the job history. In my case I also specified an Output File so that the output of the step is written to that file and then on failure I have it jump to another step that exits with failure after sending an email to me with the output file attached to the email using SMTP.
-
Have you exported the results to a table before? In BOL it describes TABLERESULTS as this:
Displays results as a rowset, with additional information
I was thinking that referred to how the information was displayed in Query Analyzer. It also seems to relate to the number of rows/depth of information returned. I wanted it to mean that the results could be stored in a table, but it seems to mean something else...
Please let me know if I am interpreting TABLERESULTS in an incorrect manner. And thanks for your response!
-
Thank you Aaron - I think that Advanced tab - Output file is exactly what I was looking for! I appreciate the non-scientific statistics as well - I will be dealing with some 20 - 30 GB databases here too!
-
Following is the procedure I use to defragment the database. 90% of it was originally stollen from somewhere. I think BOL. If not then I stole it from this site someplace. If after scaning the script and possibly trying it out, if you have any questions about it feel free to ask. In a nut shell, if you don't pass a database name it defrags ALL databases (except tempdb). If you pass a database name it only defrags that database. It then gets a list of all user tables in the database and runs the DBCC SHOWCONTIG command on each one capturing the results into a variable based temp table. It then walks through the results and anything with a fragmentation level reported above 30% is defragmented. Note that really small tables are almost always "fragmented" and will be done all the time unless you put a size filter in the process. I haven't bothered since they are small and it runs incredibly fast as a result.
CREATE PROCEDURE ADMDefragIndexes
@Database varchar(128) = NULL /* Do not pass a database name to defag all user table indexes in all databases. */
AS
SET NOCOUNT ON
DECLARE @databaseName VARCHAR (128)
DECLARE @DefragDatabases table ( Name varchar(128) )
INSERT INTO @DefragDatabases
SELECT RTRIM( CATALOG_NAME )
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME = ISNULL( @Database, CATALOG_NAME ) /* NULL to do all databases */
AND CATALOG_NAME <> 'tempdb' /* Never check the temp database. */
DECLARE databases CURSOR FOR
SELECT Name
FROM @DefragDatabases
OPEN databases
-- loop through the databases
FETCH NEXT FROM databases INTO @databaseName
WHILE @@FETCH_STATUS = 0
begin
EXEC( '
PRINT ''Beginning database ' + @databaseName + ' : '' + CONVERT(varchar, CURRENT_TIMESTAMP, 109)
RAISERROR('' '',0,1) with NOWAIT
USE ' + @databaseName + '
DECLARE @tablename VARCHAR (128)
DECLARE @tableschema 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, TABLE_SCHEMA
FROM ' + @databaseName + '.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, @tableschema
WHILE @@FETCH_STATUS = 0
begin
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC (''DBCC SHOWCONTIG (''''['' + @tableschema + ''].['' + @tablename + '']'''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'' )
FETCH NEXT FROM tables INTO @tablename, @tableschema
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 (' + @databaseName + ', '' + RTRIM(@tablename) + '', '' + RTRIM(@indexid) + '') - fragmentation currently ''
+ RTRIM(CONVERT(varchar(15),@frag)) + ''%''
SELECT @execstr = ''DBCC INDEXDEFRAG (' + @databaseName + ', '' + 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
' )
FETCH NEXT FROM databases INTO @databaseName
end
-- Close and deallocate the cursor
CLOSE databases
DEALLOCATE databases
PRINT 'FINISHED : ' + CONVERT(varchar, CURRENT_TIMESTAMP, 109)
RAISERROR(' ',0,1) with NOWAIT
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply