Data fragmentation questions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Data fragmentation questions

Author

Topic

Elidas
Starting Member

33 Posts

Posted - 2008-03-25 : 05:40:43

I have some questions about data fragmentation in sql server I hope someone can answer.

1- What is the impact of disk defragmentation task of windows operating system in sql server?
2- Do I have to stop the server before launching this task?
3- 99% of the space in the partition where sql server works is physically fragmented, is it normal?
4- If I shrink everyday the databases will it fragment too much the data?
5- In sql server 2000 database maintanance plans I can choose to reorganize data and index pages, does this defragment the tables and indexes?

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-25 : 22:53:16

1 & 2. You have to stop sql when run disk defrag, that means down time.
3. Possible if db file set to auto grow and db option auto shrink is enabled.
4. Will frag disk not data in db.
5. Yes.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-27 : 15:12:21

I think becasue of the following jobs every sunday PM, the database is getting really slow, could you please suggest
what changes to be done in order to improve the performance, Thanks.
1. The following code runs as a job every sunday @1PM:
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
DECLARE @db sysname, @sql nvarchar(4000)
DECLARE CRDB CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model')
OPEN CRDB
FETCH CRDB INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '=================== '+QUOTENAME(@db)+' "'+CAST(GETDATE() as varchar(100))+'" ====================='
SET @sql=N'SET XACT_ABORT OFF'
SET @sql=@sql+N' DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
SET @sql=@sql+N' IF EXISTS (SELECT * FROM sysindexes WHERE indid=1 AND id=OBJECT_ID(''?''))'
SET @sql=@sql+N' DBCC INDEXDEFRAG (0, ''?'', 1) WITH NO_INFOMSGS'
SET @sql=@sql+N' UPDATE STATISTICS ? EXEC sp_recompile ''?'''
SET @sql=N'USE ['+@db+'] EXEC sp_msforeachtable N'''+REPLACE(@sql,'''','''''')+N''''
EXEC(@sql)
DBCC UPDATEUSAGE (@db) WITH NO_INFOMSGS
DBCC CHECKDB (@db) WITH NO_INFOMSGS
FETCH CRDB INTO @db
END
CLOSE CRDB
DEALLOCATE CRDB

2. Then optimizations maint plan(Reorganize data,index pages) runs every sunday @5PM

4. Integrity checks runs every sunday @1PM
Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-27 : 22:29:38

Why do integrity checks and reindex at same time? Why rebuild index twice a day.

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2008-03-28 : 15:14:21

I repeatedly see mentions that shrinking the databases daily is not necessary. If your databases grow and contract it is best to allow sql its room ahead of time.
it wont have to stop, and "unbutton its pants" before it keeps eating.
:D

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-03-28 : 17:30:50

As our system is slowing down every sunday afternoon while this sqlcode runs as a job, I deleted few lines from the code, please advice for any changes, thanks!!

SQLCode:

--deleting these two lines for perf issues
--DBCC FREEPROCCACHE WITH NO_INFOMSGS
--DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

DECLARE @db sysname, @sql nvarchar(4000)
DECLARE CRDB CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model')
OPEN CRDB
FETCH CRDB INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '=================== '+QUOTENAME(@db)+' "'+CAST(GETDATE() as varchar(100))+'" ====================='
SET @sql=N'SET XACT_ABORT OFF'
SET @sql=@sql+N' DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
SET @sql=@sql+N' IF EXISTS (SELECT * FROM sysindexes WHERE indid=1 AND id=OBJECT_ID(''?''))'

--deleting two lines since Reindex does all that a defrag and stats update does
--SET @sql=@sql+N' DBCC INDEXDEFRAG (0, ''?'', 1) WITH NO_INFOMSGS'
--SET @sql=@sql+N' UPDATE STATISTICS ? EXEC sp_recompile ''?'''

SET @sql=N'USE ['+@db+'] EXEC sp_msforeachtable N'''+REPLACE(@sql,'''','''''')+N''''
EXEC(@sql)
DBCC UPDATEUSAGE (@db) WITH NO_INFOMSGS

--Deleting this as Integrity checks will take care of this--
--DBCC CHECKDB (@db) WITH NO_INFOMSGS

FETCH CRDB INTO @db
END
CLOSE CRDB
DEALLOCATE CRDB

1. How often do we need to run DBCC UPDATEUSAGE?
In what order does the following need to run:
1. Reorganize data and index pages
2. Update stats
3. Check database integrity
If we do Reorganize data and index pages then do we need update stats??

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-28 : 23:04:12

1. Depends on how often data changed, we do it daily on most of dbs.
2. Check database integrity first then reorganize data and index pages.
3. Don't need update stats after reindex.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources