Defrag / Reindex

Oct 21, 2007

I#ve been doing disaster recovery on a web box that died today.

So I thought I'd do some "downtime" maintenance on the DB server

I ran a BDREINDEX on all tables, all indexes. (I know this is the 2000 way, but I assume its as good as the proper 2005 way??).

5 minutes on a 10GB database. Not bad!

I checked the DEFRAG and UPDATE STATS processes that run overnight.

They are basically defragging only tables with SHOWCONTIG indicating fragmentation. And then doing an UPDATE STATISTICS WITH FULLSCAN on all tables

That is taking an average of 30 minutes ...

Is DBREINDEX the equivalent of an UPDATE STATISTICS WITH FULLSCAN, or is it in some way a smaller-sample version?

I'm wondering why I don't just lock the DB and do a REINDEX of everything in 5 minutes ...

Kristen

View 4 Replies


ADVERTISEMENT

Defrag Or Not To Defrag

Mar 14, 2007

I have been reading many things on the internet and I wanted to create a thread asking my question here. We currently do all the re-indexing and show contig's etc to maintain my sql data and to ensure everything is good to go there.

My question is, what about the physical drive and data. We house our mdf's on a raid 1_0 and our ldfs on raid 5. I am wondering if I need to defrag these drives b/c if not am i impacting my I/O on that box. If so should I stop the sql service so that it does not corrupt SQL data? Any help on this topic would be great.



-patrick

View 1 Replies View Related

DBCC Reindex

Feb 19, 2002

Hi guys.
I an application here developed by a third party software house.
In the past, for some reason, the database would fail daily. The software
house recommended that we use dbcc reindex on all tables within the
databases twice daily. This was scheduled and is now running. Now the
database no longer fails.
The fix works and I don't understand why.
I don't understand why this would fix the problem. Why would reindex
twice daily solve the problem.
It seems excessive to have to reindex every user table twice daily.


Parg

View 1 Replies View Related

DBCC REINDEX

Apr 26, 2001

I tried to run dbcc reindex on all user tables in a database. There are no
clustered indexes, but multiple non-clustered indexes on each table.

The output file from dbcc reindex shows that it should have worked. But when I
run DBCC SHOWCONTIG, the scan density of the indexes that were in bad shape did
not improve.

Any ideas?
Thanks,
Ben Reeder
.

View 1 Replies View Related

Is Any Way To Reindex 27 Gig Db Without Logging

Sep 25, 2002

Hello everybody.
1. I have 28 Gig database with 4 tables above 4 Gig each with very bad
fragmenataion, each table has between 3 and 5 indexes
2. Database set for full recovery and I use custom log Shipping to restore
db on stand by server every 15 min.

I tried to run DBCC INDEXDEFRAG on one index on 4Gig table .
following took place
1. It took 4 hrs to complite DBCC INDEXDEFRAG
2. log shipping fail.
3. log file size of 2 Gig generated after DBCC INDEXDEFRAG complited

I tried to run drop and create clustered index on table it create same
problem - log growing, log shipping fails

(if log shipping fails and stand by database get suspended it will take 6- 8
hrs to restore it from backup and apply all logs)

So my question is

What would be best way to rebuild- reindex - defragment 28 Gig database
when it set to fully recovery and log shipping


Thank you
Alex

View 1 Replies View Related

Reindex Tables

Apr 16, 2002

Hi guys,
I need a help with this question. In SQL 2000, Can i run update,insert or delete queries while the Indexes of that table is being Rebuilt? Will
i get blocked by the DBCC DBreindex process?

thank you

View 1 Replies View Related

SQL Import And Reindex

May 1, 2008

I have a process that runs each day and it imports about 550K records into a database. My questions is it appears I have to reindex the database after each import otherwise the sp's that I have written will just run and run and run. After the reindex job things run within 60 seconds. I am just looking for some insight on why, I understand why a reindex is done, but dont know if I understand why I have to reindex every time.

View 9 Replies View Related

Reindex Error

Jul 12, 2007

Hi experts, I would like to ask for this error that occurs upon executing my reindexing script

here is my script

USE mydatabase

DBCC DBREINDEX('outpatient', '', 70)
go

Then this error message will appear. I researched for this error and from the site that I've found they say that the table is corrupted? That I need to restore a better backup..?

The statement has been terminated.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x55555545). It occurred during a read of page (1:353409) in database ID 10 at offset 0x000000ac902000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataBizbox_HS7.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


Darren Bernabe Blanco

View 4 Replies View Related

Dbccd Reindex

Jul 18, 2007

how often should be done and if it don't do it what will happen?

=============================
http://www.sqlserverstudy.com

View 1 Replies View Related

Question On Reindex

Sep 27, 2006

Hi,i have several tables in production whose contents are renewd totally in 1week. So everyd day we delete ~15% records and then insert 15% new.And after a few days, the performances drops :TABLE level scan performed.- Pages Scanned................................: 169617- Extents Scanned..............................: 21630- Extent Switches..............................: 153827- Avg. Pages per Extent........................: 7.8- Scan Density [Best Count:Actual Count].......: 13.78% [21203:153828]- Logical Scan Fragmentation ..................: 45.06%- Extent Scan Fragmentation ...................: 52.66%- Avg. Bytes Free per Page.....................: 5042.5- Avg. Page Density (full).....................: 37.70%I can't program a dbcc reindex every day because of concurrent access (itlocks the tables too long), actually i can only program it on sunday.What else can i do ? I can adjust the fill factor but how to find the goodvalue if i don't want to waste space.The total size of the database is ~150GB.Thx

View 1 Replies View Related

How To Defrag A Table?

Jul 30, 2004

I have a large table with no PK but has other indexes. How to defrag the table (not the indexes)? Do I need to drop and re-create the table or something?

Thanks.

View 3 Replies View Related

Defrag SQL SERVER

Feb 13, 2004

Folks

Our sql server machine is badly in need of a defrag however I'm a bit weary of doing this as I'm really not sure of the implications or indeed how to do it.

I'm really not a server type of person so any assistance would be gratefully received. If anyone can explain it to me like I'm a 5 year old then that would probably be best!



Cheers

View 8 Replies View Related

Database Defrag

Jul 14, 2004

Hello,
I am working with a very large db (850+gig) that is in desperate need of defragging. I am familiar with DBCC Reindex and DBCC IndexDefrag but am concerned that these processes will take an extremely long time to run. Has anyone had any experience with any 3rd party tools that better/faster then DBCC? Or any ideas or suggestions how best to attack this? Among the many tables with large footprints and millions of rows, I have 3 tables with reserved space over 100g also 3 tables with over 500Mil rows (1 tbl with > 1bil rows). This is a 24x7 db.

Thanks in advance for any ideas and suggestions
Jeff

View 2 Replies View Related

Defrag The Drive

Apr 17, 2006

Hi

Can I de-frag the Drive in which the Data files & Log Files of the SQl Server Exists ???

Please Advice.


Thanks

View 5 Replies View Related

Automating Defrag.exe

Jul 19, 2007

Isn't there any way to automate Windows defrag? Isn't there any undocumented parameters for DEFRAG.EXE?

Canada DBA

View 4 Replies View Related

Index Defrag

Dec 5, 2007

I am new to sql server and we have sql server 2000

DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PS_ORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PS0ORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSAORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSBORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSCORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSEORD_LINE )
DBCC INDEXDEFRAG ( 0, PS_ORD_LINE, PSFORD_LINE )

Please somebody give me for sql server 2005 scripts for above.

View 1 Replies View Related

DBCC REINDEX Command

Oct 2, 2001

I am currently running the Back Office Resource Kit Log shipping option for a database running on an SQL 7 installation. As part of the on-going maintenance work that we are being asked to perform by the application vendor I need to run a DBCC REINDEX run on most of the tables in the database. Currently this is done by stopping the log shipping routine and then running the reindex script, then taking a full backup and restoring the backup to the secondary server then restarting the log shipping scripts. This is a very time consuming task that has to be performed at unsociable hours.

Has anybody got an opinion as to if this would work at the same time as the log shipping scripts or do I have to continue as at present.

Responses gratefully received.

Regards
Phil Corby
Geest IT Services

View 3 Replies View Related

Update Statistics Vs. Reindex

Dec 14, 1999

I am maintaining a large table with millions of rows that has two non clustered indexes and data changing frequently, I need to keep the indexes fresh. Update Statistics runs much quicker than Reindex. What is the appropriate situation for each and why?
Thanks in advance.

View 1 Replies View Related

Disk Space After Reindex

Nov 29, 2007

All,

I first ran indexdefrag on a table with 1.5 billion rows.
logical fragmentation was at 95%.
logical frag went down to 3% with no real effect on disk.

DBCC reindex had previously been bombing undetected.


Now I've run a reindex on this table:
Reindex Job with Fillfactor =100
Ran in 3:05
Free Disk went from ~150GB before operation to 49GB
File4 went from 347GB to 504GB

Why has so much free disk been consumed by this operation and not released ??????????

Is my only choice to shrink data file???

thanks

Env.
Win2k ENT os
SQL 2k5 std 64bit

View 4 Replies View Related

Automating Table Defrag

May 26, 1999

I'd like to build a process that will identify all the tables in a database with a scan density less than 100% and generate 'dbcc dbreindex' statements for them. The 'dbcc showcontig' command displays the information I want but I don't know how to access this information from within a script. Any ideas out there?

View 5 Replies View Related

Defrag Indexed Views?

Jul 7, 2004

How do we defrag indexed views? Can any one give me a query to loop thru all the indexed views in the database and find out the fragmentation levels and also defrag them?
Thanks in advance!

View 1 Replies View Related

Does Index Defrag Get Logged?

Sep 15, 2006

I've noticed a huge transaction log size after having run an
index defragmentation. Does a defrag get written to the transaction
log really? (Assuming the full recovery model.)

View 1 Replies View Related

How To Defrag SQL Server 2000

Nov 15, 2006

Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.

View 5 Replies View Related

Defrag Table With No Clustered Idx

Aug 22, 2007

Is there any easy way to defrag a table with no clustered idx other than bcp out then back in ? (SQL 2000)Actually I have a 100GB db that I deleted data from other tables, about 10GB worth, but access to the table in question (38GB) seems to have slowed down dramatically, Or does the entire disk need to be defragmented and a bcp out/in would be a waste of time on this table ?I run an index defrag every night, reindex weekly.Thanks.

View 14 Replies View Related

YADS -- Yet Another Defrag Script

Oct 2, 2007

This script was based on the original posted here: http://sql-server-performance.com/Community/forums/p/20584/114940.aspx#114940

I use the REORGANIZE option of ALTER INDEX. It's default is ONLINE, no matter what the engine edition and makes for simpler code.

I also make heavy use of my own logging tables, which are included at the bottom of the sp.

Please, feel free to comment. I like getting feedback about my scripts and it seems rarely that happens...


USE Admin
GO

IF EXISTS (SELECT [name] FROM Admin.sys.objects WHERE [name] = 'usp_DB_DeFrag' AND TYPE = 'P')
DROP PROCEDURE dbo.usp_DB_DeFrag
GO

CREATE PROCEDURE dbo.usp_DB_DeFrag (@DBName sysname, @Percentage float = 10)
-- EXEC Admin.dbo.usp_DB_Defrag @DBName = 'FooDB', @Percentage = 10
AS

/******************************************************************************
**Name: Admin.dbo.usp_DB_DeFrag.sql
**
**Description: Defragment indexes using REORGANIZE for online operation.
**Record historical fragmentation information to a permanant table
**for trend/history analysis.
**
**Depends on: SQL2005 >= SP2 due to object_name() usage. See BOL for details.
**Admin.dbo.Process_Log - Table
**Admin.dbo.FragTracking - Table
**
** TODO: Open to suggestions...
**
**Author: G. Rayburn <grayburn@---.com>
**
**Date: 10/02/2007
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 10/02/2007 G. Rayburn <grayburn@---.com>
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON;

DECLARE @DynFragList varchar(1024)
, @DynDBAlter varchar(256)
, @DynDefragDriver varchar(max)
, @DynUpdateStats varchar(1024)
, @OrigRecoveryModel nvarchar(128)
, @Process_Name varchar(150)
, @Message varchar(256)
, @Error int

-- Cursor objects:
, @SchemaName sysname
, @ObjectName sysname
, @IndexName sysname
, @IndexType nvarchar(60)
, @AvgFrag int
, @PageCount int
, @RecordCount int
--, @GhostRecordCnt bigint
--, @Partition int
;


-- DEBUG:
--SET @DBName = 'FooDB'
--SET @Percentage = 10;


SET @Process_Name = 'usp_DB_Defrag run on [' + @DBName + ']';


-- Ensure that @DBName is a valid db for db_id() usage.
IF (db_id(@DBName)) IS NULL
BEGIN
SET @Message = '[' + @DBName + '] is not a valid database on ' + @@SERVERNAME + ', please check your spelling and try again.'

INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', 9999, @Message)

RETURN
END;


-- Record startup message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[START] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.')


-- Check & alter recovery model if neccessary:
SET @OrigRecoveryModel = (SELECT CONVERT(varchar(55),DATABASEPROPERTYEX(@DBName, 'Recovery')))

IF @OrigRecoveryModel = 'FULL'
BEGIN
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']
SET RECOVERY BULK_LOGGED';

EXEC (@DynDBAlter);

SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully set database [' + @DBName + '] to BULK_LOGGED recovery model.')
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to set database [' + @DBName + '] to BULK_LOGGED recovery model.')
END;
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, 'Database [' + @DBName + '] is in ' + @OrigRecoveryModel + ' recovery model so no need to change it.')
END;



-- Temp table of initial DBCC results:
CREATE TABLE #_FragList
(
ObjectName varchar(100)
, [Object_ID] int
, Index_ID int
, Partition_Number int
, IndexType varchar(60)
, alloc_unit_type_desc nvarchar(60)
, avg_fragmentation_in_percent float
, avg_fragment_size_in_pages float
, avg_page_space_used_in_percent float
, fragment_count bigint
, page_count bigint
, record_count bigint
, forwarded_record_count bigint
, ghost_record_count bigint
);

INSERT INTO #_FragList

SELECT
LEFT(object_name([object_id], db_id(@DBName)),100)
, [object_id]
, index_id
, partition_number
, index_type_desc
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count

FROM sys.dm_db_index_physical_stats (db_id(@DBName), NULL, NULL, NULL, 'DETAILED')

WHERE avg_fragmentation_in_percent >= @Percentage
AND index_id >= 1
AND page_count >= 1000

ORDER BY -- Ensure Clustered indexes are rebuilt first.
[object_id]
, index_id ASC;

CREATE INDEX IDX_ObjNameIndexID ON #_FragList (ObjectName, Index_id);


-- Historical tracking:
INSERT INTO Admin.dbo.FragTracking

SELECT @DBName
, ObjectName
, [Object_ID]
, Index_ID
, Partition_Number
, IndexType
, alloc_unit_type_desc
, avg_fragmentation_in_percent
, avg_fragment_size_in_pages
, avg_page_space_used_in_percent
, fragment_count
, page_count
, record_count
, forwarded_record_count
, ghost_record_count
, getdate()

FROM #_FragList

ORDER BY [Object_ID]
, Index_ID ASC;


-- Create & populate Temp table to drive defrag operations from.
CREATE TABLE #_DefragDriver
(
IdentID int IDENTITY(1,1)
, SchemaName sysname
, ObjectName sysname
, IndexName sysname
, IndexType varchar(60)
, avg_fragmentation_in_percent float
, page_count int
, record_count int
, ghost_record_count bigint
, partition_number int
);


SET @DynDefragDriver = '
USE [' + @DBName + ']

INSERT INTO #_DefragDriver

SELECT schema_name(so.schema_id)
, fl.[ObjectName]
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number

FROM #_FragList fl
, [' + @DBName + '].sys.indexes si
, [' + @DBName + '].sys.objects so

WHERE object_id(fl.ObjectName) = si.object_id
AND fl.index_id = si.index_id
AND object_id(fl.objectname) = so.object_id
AND si.is_disabled = 0
AND si.allow_page_locks = 1

GROUP BY so.schema_id
, fl.[ObjectName]
, fl.[object_id]
, fl.index_id
, si.[name]
, fl.IndexType
, fl.avg_fragmentation_in_percent
, fl.page_count
, fl.record_count
, fl.ghost_record_count
, fl.partition_number

ORDER BY fl.[object_id]
, fl.index_id ASC; '

EXEC (@DynDefragDriver);



-- Do the defrag.
DECLARE curDBFrag CURSOR
FOR

SELECT SchemaName
, ObjectName
, IndexName
, IndexType
, avg_fragmentation_in_percent
, page_count
, record_count
--, ghost_record_count
--, partition_number

FROM #_DefragDriver

ORDER BY IdentID ASC;

OPEN curDBFrag

FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

-- ALTER INDEX operations:
SET @Message = 'Table: [' + @ObjectName + '] with record count: ' + CONVERT(varchar(15),@RecordCount) + ' and page count: ' + CONVERT(varchar(15),@PageCount) + '. Index: [' + @IndexName + '] of type: ' + @IndexType + ' is ' + CONVERT(varchar(5),@AvgFrag) + '% fragmented.';

SET @DynFragList = 'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @ObjectName + '] REORGANIZE;'

EXEC (@DynFragList);

SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)
END;


-- UPDATE STATISTICS operations:
SET @Message = 'UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + '];'

SET @DynUpdateStats = '
USE [' + @DBName + ']

UPDATE STATISTICS [' + @SchemaName + '].[' + @ObjectName + '] [' + @Indexname + ']; '

EXEC (@DynUpdateStats);

SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), @Message)
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), @Message)
END;


-- Friendly WAITFOR operation:
WAITFOR DELAY '00:00:05.000'

END;
FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag, @PageCount, @RecordCount --, @GhostRecordCnt, @Partition
END;

CLOSE curDBFrag
DEALLOCATE curDBFrag;


-- Reset FULL recovery model.
IF @OrigRecoveryModel = 'FULL'
BEGIN
SET @DynDBAlter = 'ALTER DATABASE [' + @DBName + ']
SET RECOVERY FULL';

EXEC (@DynDBAlter);

SET @Error = (SELECT @@ERROR)
IF @Error = 0
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'SUCCESS', CONVERT(varchar(15),@Error), 'Successfully reset database [' + @DBName + '] back to FULL recovery model.')
END;
ELSE
BEGIN
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'ERROR', CONVERT(varchar(15),@Error), 'Failed to reset database [' + @DBName + '] back to FULL recovery model.')
END;
END;


-- Record complete message:
INSERT INTO Admin.dbo.Process_Log
VALUES (getdate(), @Process_Name, 'INFO', 0, '[COMPLETE] - usp_DB_Defrag @DBName = [' + @DBName + '], @Percent = ' + CONVERT(varchar(3),@Percentage) + '.');


-- Cleanup:
DROP TABLE #_FragList;
DROP TABLE #_DefragDriver;


-- Dependancies:
----
----USE [Admin]
----GO
---- DROP TABLE Admin.dbo.FragTracking
----CREATE TABLE FragTracking
----( TrackID int IDENTITY(1,1) -- PRIMARY KEY CLUSTERED
----, DBName sysname
----, ObjectName sysname
----, Object_ID int
----, Index_ID int
----, Partition_Number int
----, IndexType varchar(60)
----, alloc_unit_type_desc nvarchar(60)
----, avg_fragmentation_in_percent float
----, avg_fragment_size_in_pages float
----, avg_page_space_used_in_percent float
----, fragment_count bigint
----, page_count bigint
----, record_count bigint
----, forwarded_record_count bigint
----, ghost_record_count bigint
----, SnapDate datetime
----);
----
----USE [Admin]
----GO
---- DROP TABLE Admin.dbo.Process_Log
----CREATE TABLE [dbo].[Process_Log](
----[MessageID] [int] IDENTITY(1,1) NOT NULL,
----[Date] [datetime] NOT NULL,
----[Process_Name] [varchar](150) NULL,
----[Severity] [varchar](15) NULL,
----[ErrorCode] [int] NULL,
----[Message] [varchar](255) NULL,
---- CONSTRAINT [PK_Process_Log] PRIMARY KEY CLUSTERED
----(
----[Date] ASC,
----[MessageID] ASC
----)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Admin_Data]
----) ON [Admin_Data]
----GO
GO

View 18 Replies View Related

Index Rebuild Does Not Defrag

Oct 3, 2007

Hi,

After issuing an index rebuild on a primary key index (and updating statistics), the index still shows a scan density of 12.5%!

Any ideas on why the rebuild doesn't seem to do anything on the fragmentation levels?

I'm using sql 2005

Thanx

View 12 Replies View Related

DEFRAG Disk Drive

Jul 23, 2005

SQL 2000We took SQL Server offline last night and defragged the SAN. Should wereindex or will be be okay ?Thanks,Craig

View 2 Replies View Related

Food For Thought (ReIndex And Log Shipping)

Dec 29, 2003

I have a production 60GB database set to Full Recovery and every 15 minutes I am log shipping to a Stand by Server .

During the production hours there are no problems but at night when I run DBCC DBREINDEX, the log grows to 22GB and because of this I have a problem sending this over the network to the stand by server.

I tried changing the recovery model to Bulk_Logged but the there is no difference in log file backup size.

AnyIdea

View 1 Replies View Related

Reindex Script In Replication Environment

Jun 19, 2008

Dear All,
is it ok to run index rebuild script on publisher and after that in subscriber? what are the steps i need to take to do this?

will it affect the replication? please help me here.

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

Dbcc Reindex Issue - - I Don't Understand!!

Jun 26, 2006

Hi Folks,SQL Server 2000 SP3 on Windows 2000. I have a database on which I ranthe command :dbcc dbreindex ('tablename')gofor all tables in the database. Then I compared the dbcc showcontigwith all_index output from before and after the reindex and on thelargest table in the database I found this. First output is prior toreindex:Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:7TABLE level scan performed.- Pages Scanned................................: 184867- Extents Scanned..............................: 23203- Extent Switches..............................: 23324- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]- Logical Scan Fragmentation ..................: 11.13%- Extent Scan Fragmentation ...................: 35.46%- Avg. Bytes Free per Page.....................: 60.0- Avg. Page Density (full).....................: 99.26%Second output is from after the reindex:DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:8TABLE level scan performed.- Pages Scanned................................: 303177- Extents Scanned..............................: 37964- Extent Switches..............................: 42579- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]- Logical Scan Fragmentation ..................: 43.19%- Extent Scan Fragmentation ...................: 24.78%- Avg. Bytes Free per Page.....................: 75.1- Avg. Page Density (full).....................: 99.07%Following are my concerns:The following numbers are all higher after reindex than before reindex:pages scanned, extent switches, logical scan fragmentation, avg bytesfree per page, avg page density.scan density is lower after reindex than before reindexSeems to me that the numbers that are higher after reindex should belower and numbers that are lower after reindex should be higher? Ididn't specify the fill factor in the dbcc reindex command so it shouldhave used the default fill factor. The fill factor has never beenchanged on this machine.Am I missing something?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***

View 3 Replies View Related

MS SQL Question Regarding Page/index Defrag.

Apr 17, 2005

I know the shortcut to 'defragging' the database to eliminate and
reclaim 'whitespace' in data and index pages is to setup a maintenance
plan. This doesn't work for me for two reasons:
1. I like to know how things work.
2. I can not create maintenance plans with my hosted provider.
I use the following T-SQL to rebuild all indexes on all tables:

-- exec sp_MSforeachtable "DBCC DBREINDEX ('?', ' ', 90)

But, does that not only address indexes? What about getting whitepace
back from data in tables themselves?
Am I over-thinking this?
When you're paying for SQL by the MB and generate a lot of data (with a
fair amount of churn) you want to keep it optimized and efficient,
right?

View 1 Replies View Related

Dbreindex Vs Index Defrag Question

Mar 9, 2007

Does anyone know if dbreindex and index defrag ideally perform the same function? I have been told that index defrag does not hold locks on a table when executed and dbreindex does. Other than this is there any difference between the two functions? My understanding was that dbreindex reindexes the data stored in a table for faster reads and index defrag removes purged data. Am I correct? I am currently running both functions on my SQL server and was advised that I really only need to run the index defrag job. Is this advise correct?

View 6 Replies View Related

Does Dbcc Reindex Update Usage Information?

Feb 3, 2000

Does running DBCCReindex update the space allocated columns in sysindexes? I understand that running dbcc updateusage updates the space allocated columns in the sysindexes table. But, I cannot find any documentation that indicates whether dynamically rebuilding the indexes as opposed to drop and recreating the indexes updates the space allocated columns in the sysindexes table?

Any information would be helpful.
Thanks.
Gail Wade
Database Administration
Raymond James Financial
gwade@it.rjf.com

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved