I have a Dell PowerEdge 2850 with Windows 2003 server. 2 disk drives with RAID. My databases are fragmented on the drive and I need to defrag the drive at least once a week. My database files are fragmented. Defragger cleans up the files but as soon as I start running processes again, the files fragment and system response time suffers.
What can I do to keep the files from fragmenting?
calculate the database growth
and set the file increment in the db option
bigger than that. i meann just a little bigger
|||
Question : does file fragmentation really impact the performance of a DB ?
I mean : by construction, the data in files are fragmented, and access to data in DB always require a lot of disk seek. Sequential access through files should be marginal. So, fragmentation of the whole file should not imply a big performance impact.
Am I correct ?
|||How are you measuring the fragmentation?
There are two types of fragmentation here:
1) The normal filesystem fragmentation of the database files. This should have minimal to nil impact unless you do a lot of table-scans. On the other hand, you shouldn't be getting this sort of fragmentation unless you are constantly growing your database. Far better to size it for growth initially than to allow it to grow incrementally.
2) Internal fragmentation of the information within the database files (as measured by commands like DBCC SHOWCONTIG). THis has nothing to do with the physical files being fragmented on disk, and is resolved by using SQL techniques such as DBCC INDEXDEFRAG or ALTER INDEX REBUILD.
