August 10, 2008 at 11:36 pm
Can any one explain me like how can we store DBCC Showcontig results can be stored into a table for each tables in a database
August 11, 2008 at 1:17 am
Use the TABLERESULTS option of showcontig
Something like
INSERT INTO ContigCheckTable
EXECUTE ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2008 at 9:49 am
this proc stores results for all tables in targeted db
/*=========================================================================================
Processing Steps:
create temp table #_fraglist
fill with dbcc showcontig for all tables at a time in target DB (with tableresults, all_indexes,all_levels)
Parameters:
@dbname (sysname) name of database to get densities for
Return Value:
-1: error - invalid target DB; 0: OK
Keeps history of defragged indexes in history table
=========================================================================================*/
CREATE procedure dbo.upo_exp_showcontig (
@dbname sysname)
as
set nocount on
declare @err int, @cmd varchar(1000), @tabname sysname, @int int, @cnt int, @indexname sysname, @end_time datetime,
@start_time datetime
-- Create temporary table to hold DBCC SHOWCONTIG output
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)
-- Update statistics first
------------------------------------------------set @cmd = 'use ' + @dbname + '; exec sp_updatestats'
---- exec(@cmd)
if @dbname is not null
begin
-- Insert DBCC SHOWCONTIG output for all tables in database into #fraglist
set @cmd = 'use ' + @dbname + '; exec sp_MSForEachTable @command1 = ''insert into #_fraglist exec(''''dbcc showcontig([?]) with tableresults, all_indexes'''')'''
exec (@cmd)
/*ERRTRAP*/ select @err = @@ERROR if @err <> 0 return @err
end
delete #_fraglist where indexid in (0, 255)
set @start_time = getdate()
set @end_time = getdate()
insert admin..Fragmented_Indexes_History
( DBName, ObjectName,ObjectId, IndexName,
IndexId, Lvl, CountPages, CountRows,
MinRecSize, MaxRecSize, AvgRecSize,
ForRecCount, Extents, ExtentSwitches,
AvgFreeBytes, AvgPageDensity, ScanDensity,
BestCount, ActualCount, LogicalFrag, ExtentFrag,
start_time, end_time)
select @dbname, ObjectName,ObjectId, IndexName,
IndexId, Lvl, CountPages, CountRows,
MinRecSize, MaxRecSize, AvgRecSize,
ForRecCount, Extents, ExtentSwitches,
AvgFreeBytes, AvgPageDensity, ScanDensity,
BestCount, ActualCount, LogicalFrag, ExtentFrag,
@start_time, @end_time
from #_fraglist
update r set r.reindex = 1, r.logical_frag = i.logicalfrag
from admin..reindex r, admin..Fragmented_Indexes_History i
where i.end_time between getdate() - 1 and getdate()
and i.logicalfrag > 30 and i.CountPages > 50
and i.dbname = r.dbname and i.objectname = r.table_name
insert admin..reindex(dbname, table_name, reindex, logical_frag, extentfrag, sql)
select i.dbname, i.objectname, 1, i.logicalfrag, i.extentfrag,
'DBCC indexdefrag (' + rtrim(i.dbname) + ',' + rtrim(i.objectname) + ', ' + rtrim(i.indexname) + ' )WITH NO_INFOMSGS'
from admin..Fragmented_Indexes_History i
where i.end_time between getdate() - 1 and getdate()
and (i.logicalfrag > 30 or i.extentfrag>30) and i.CountPages > 50
and not exists(select 1 from reindex r
where r.dbname = i.dbname and r.table_name = i.objectname)
drop table #_fraglist
return 0
---exec upo_exp_showcontig @dbname ='bx01p'
GO
August 18, 2008 at 10:04 am
Thank You for the reply...:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply