File Defragmantation | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

File Defragmantation

Hi ! Just analyzed defragm. level and system suggests to defragment. On a drive with backups : Total fragmentation =49%
File fragm. = 99%
free space fragmentation=0%
On a drive with database files: Total fragmentation =21%
File fragm. = 43%
free space fragmentation=0%
when do I start to worry about defragmentation?
Should I monitor total fragm. or file fragm.?
What tool are you using?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Disk Defragmenter<br /><br />Start-&gt;&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rograms-&gt;&gt;Accessories -&gt;&gt;system tools-&gt;&gt;Disk defragmenter
Run this command DBCC SHOWCONTIG from QA to accurately monitor fragmentation levels of your SQL Server Database. <br /><br />Also check this article out<a target="_blank" href=http://www.sql-server-performance.com/rd_index_fragmentation.asp>http://www.sql-server-performance.com/rd_index_fragmentation.asp</a><br /><br />or read the article then command [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
I run this command regularly and
once in 2 month manually defragment indexex and every 6 month rebuild indexes But as I am undestanding rebuild indexex/ defrag indexes isn’t the same thing as
defragment disk
Looks like I found what was looking for :
now the question is what exactly is considered to be On small-scale environments ?
we defenetly do not have SAN….
Physical Disk Fragmentation
Disk fragmentation can contribute to poor performance on some systems. To determine whether disk fragmentation exists, use system tools provided in Microsoft Windows or from third parties to analyze drives on which SQL Server databases reside.
On small-scale environments with more conventional I/O subsystems, it is recommended that you correct disk fragmentation before running index defragmentation tools. On large-scale environments that benefit from more intelligent disk subsystems, such as SAN (storage area networks) environments, correcting disk fragmentation is not necessary.

I use diskeeper (that’a why I ask), and this tool defrag databases too. No a index level, just a files level. I really don’t know if typical windows tools do the same. One way to resolve this, is to backup database, delete all database files, defrag disk and restore again. Of course, no SAN systems.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
If you are going to be running any disk defragmentation tools you must bring the sever down. Also you should keep disks defragmented as much as possible.
You can exclude MSSQL directories in order to run disk defrag tools working, no issues at our end. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
We have RAID 5 .
Is that considered a large scale – enviropment?
Raid 5 has nothing to do with your environment being large scale a large scale environment is determined by the number of clients and size of your database(s). With respect to SAN technology, SANs can perform Online defragmentaion that’s why the article you read said you do not have to worry about it. I don’t think you can get around not defraging your system, try to find a maintenance window for this kind of activity, if you are running a 24/7 I assume you already have a stand by server or cluster to perform these duties.
Total fragmentation =49%
File fragm. = 99%
free space fragmentation=0% You might want to start now on this. You should probably just empty the drive and defrag between a backup window if you can. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
quote:Originally posted by NewDBA Hi !
when do I start to worry about defragmentation?

Now….
quote:Originally posted by LuisMartin I use diskeeper (that’a why I ask), and this tool defrag databases too. No a index level, just a files level. I really don’t know if typical windows tools do the same. One way to resolve this, is to backup database, delete all database files, defrag disk and restore again. Of course, no SAN systems.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

Sorry : I don’t understand.
Don’t I need to defragment database files as well?
If i don’t why do i have to worry about other files on that drive that keep db?
There are two differents frag.
One is inside database fragmentation and you can deal with that in a way member suggest before.
Other is outside database, disk fragmentation witch include database. When database grow not neccesary grow in continuos files.
So, some times, is neccesary to defrag disks.
In one post in this forum, don’t remember witch one, one member improve performace after backup database, delete it, defrag disk and restore database. The preliminary conclusion, by Gaurav, was disk defragmentation. And that sound razonable to me.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Defragmentation of disk files is cumbersome and you may need to stop the SQL services in order to defrag SQL related files or atleast exclude them if you cannot afford downtime. Best option is to check SYSMON (PERFMON) physical disk counters for assesment on performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>