DBCC DBREINDEX Questions
-
After running dbcc showcontig on a database with roughly 1000 tables, I have found that quite a few of the tables need to be reindexed. Is there a way to run dbcc dbreindex and specify that all tables are reindexed? Next question: Is it possible to reindex or defrag system tables? Unfortunately we are working with some terribly ignorant vendors and they have let this database go, so even though I don't officially have "ownership" of the database yet, I have to step in before something bad happens.
Any thoughts are greatly appreciated
Thanks,
Jared
-
Two thoughts for you....
1. You could probably do the DBREINDEX on all tables using the undocumented stored procedure "sp_msforeachtable". But it might not be a 'wise' idea. DBREINDEX requires the database to be offline.
2. Have you considered DBCC INDEXDEFRAG instead? And this you really don't want to do on all tables at once. This basically does the same thing as DBREINDEX, but it leaves the database available for users. The last thing this command does is shrink the database. This causes TEMPDB to grow and if the space being released is large, TEMPDB might grow too large.
-SQLBill
-
Yes, I have thought of doing "indexdefrag", and I think I might give that a try. I wouldn't be running this job during production hours anyway, but should the job run long, indexdefrag would certainly be better.
Thanks for the imput SQLBill!
Anyone know if it is possible to reindex system tables?
-
I work with a lot of small (<500,000 rows) tables and find that they have to be reindexed frequently. To help with this I have created a job that goes out every night, when the servers are pretty much idle with few or no connections, and runs and pumps the results of a showcontig into a table. It then puts into another table the information for all indexes that have a scan density of < 80% (this limits the amount of indexes). I use this table in a script which sends me a reindex script every day for all of the indexes that really need to be reindexed. As I am in early I am able to run this script before anyone gets in and reindex everything. It also put the information into an archive table so that I can report against those indexes that have to be remedied frequently and change the fillfactor on some of them to reduce the problem. This script could also be run automatically, however I prefer to do a manual run because I am here early enough to do so and can monitor possible blocking issues while it happens.
-
Hi stacenic,
Liked your post and would be grateful if you could post this script or email it to me on andrewkane17@hotmail.com.
Regards
Akane
-
The whole thing is pretty involved...I'm going to look at creating an article for it...but the basics are that you want to run a dbcc showcontig for all the tables and insert this into another table, then you can do what you want with the data....you can use this sp to get the data (you'll want to create the table to hold the final information first)
CREATE PROCEDURE USP_DBshowcontig_single_db @name varchar(50)
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @dbname VARCHAR(20)
DECLARE @sql VARCHAR(1000)
DECLARE @inserttable VARCHAR(3200)
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Create the table
CREATE TABLE #DBFRAGMENT (
ObjectName VARCHAR (50),
ObjectId INT,
IndexName VARCHAR (100),
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)
create table #tablename
(table_name varchar(400))
--DECLARE DB Cursor
DECLARE databases CURSOR FOR
SELECT NAME
FROM MASTER.DBO.SYSDATABASES
--WHERE NAME IN('EM_ORDER')
WHERE NAME = @NAME -- INSERT DATABASE NAME HERE
--Open the cursor
OPEN databases
FETCH NEXT
FROM databases
INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
-- declare @sql varchar(1000)
-- declare @dbname varchar(200)
--set @dbname = 'CDL'
set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''
print @sql
insert into #tablename exec(@sql)
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM #tablename
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @TABLENAME
-- Do the showcontig of all indexes of the table
INSERT INTO #DBFRAGMENT
EXEC ('USE ' + @dbname + ' DBCC SHOWCONTIG (''' + @tablename + ''')
WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
ALTER TABLE #DBFRAGMENT ADD DBname varchar(20) NOT NULL DEFAULT 'Pricing_Storage' -- INSERT DATABASE NAME HERE
set @inserttable =' INSERT INTO ADMINDB.DBO.INDEX_INFO_'+@NAME+'(
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
select ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> '''' '
--PRINT @INSERTTABLE
EXEC (@inserttable)
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
delete from #tablename
delete from #DBFRAGMENT
FETCH NEXT
FROM databases
INTO @dbname
END
CLOSE databases
DEALLOCATE databases
drop table #tablename
-- Delete the temporary table
DROP TABLE #DBFRAGMENT
GO
-
Thanks
Akane
-
You can also use the maintenance wizard to reindex all tables on a specific database, leaving free space (or not) on each index for subsequent writes to the table.
Terry -
Stacenic,
Definitely work up an article on that script. But until you do so, you might want to just submit your script as is.
-SQLBill
-
Above is the proc that does the majority of the work...it will go through, check the indexes on each table and then insert into a perm table the results of the showcontig. Once you have that you can easily go through and select the information where the scandensity is below a certain value.
I'll work on putting something a little more comprehensive together going through all the steps, including the email a script or execute automatically.
-
I used to reindex everything until I read MS's white paper on reorganization where it talked about no benefit being usually realized unless the object size exceeded 1,000 pages or more. I also used to create and manage SQL scripts and jobs to do my reorgs until I started using Embarcadero's DBArtisan, which has the Space Analyst plug-in. It builds dynamic reorg jobs that you can schedule and customize with reorg thresholds so only the objects that really need a reorg get it each time the jobs run. Works well for our server farm.
-
you may want to consider this script
exec sp_MSforeachdb 'use ?
insert into Admin.dbo.contigInfo
( ObjectName ,
ObjectId ,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize ,
MaxRecSize ,
AvgRecSize ,
ForRecCount,
Extents ,
ExtentSwitches ,
AvgFreeBytes ,
AvgPageDensity ,
ScanDensity ,
BestCount ,
ActualCount ,
LogicalFrag ,
ExtentFrag )
exec (''dbcc showcontig with tableresults, all_indexes, no_infomsgs'') Update Admin.dbo.contigInfo Set DBName = ''?'' where Admin.dbo.contigInfo.DBName IS NULL'
It assumes that an Admin DB is on the server and a ContigInfo Table is present there
* Noel -
This code reindexes all tables and seems to work fine for as long as no one is on the system. So run about 1am
Rgds
BillD
SET NOCOUNT ON
DECLARE table_object CURSOR FOR
SELECT
'Sirius..' + name
FROM
Sirius..sysobjects
WHERE
xtype = 'U'
ORDER BY
name
DECLARE @table_name varchar(80)
/* Get output. */
OPEN table_object
/* Get first entry. */
FETCH NEXT FROM table_object INTO @table_name
/* Loop while no more tables. */
WHILE (@@fetch_status <> -1)
BEGIN
if (@@fetch_status <> -2)
BEGIN
--SELECT ('DBCC DBREINDEX (' + @table_name + ')')
EXEC ('DBCC DBREINDEX (' + @table_name + ')')
END
/* Get next entry. */
FETCH NEXT FROM table_object INTO @table_name
END
CLOSE table_object
DEALLOCATE table_object
SET NOCOUNT OFF
-
Hi all,
My problem, probably not regarding the topic here, but its related. I have a SQL Server application thats running off Sql Server 2000. While some users can log in and work fine, others as soon as they connect and initialize the values from the database get Invalid Book Mark error. Any thoughts would be appreciated.
Thanks,
pro2003
-
Never mind fixed it.
Thanks y'all
Pro2003
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply