Defragmenting the Hard drive to improve SQL DB performance
Hi All,
Recently my SQL DB has really slowed down and while trying to look for things to optimse to try and improve the situation we found the following;
1. MCafee Antivirus scanning
2. The DB was sitting on a RAID 5 Volume.
3. File fragmentation is at 86%
We are trying to do the following to optimse the following to correct the situation.
1. For the Antivirus, we have put the DB files under exceptions option.
2. We have created a RAID 10 volume and that is where we will move the Db data files
3. We would like to leave the log file on the current volume. But because Fragmentation is resource intensive we wanted to move the log files to another volume and then defrag the current volume. My question is, if you move the busy DB files to another volume and defrag the former volume and then copy back the files, is there a benefit. When you copy the files to another volume, which is nicely fragmented is there a benefit or it gets fragmented the same way? I need your advice on this one.
Recently my SQL DB has really slowed down and while trying to look for things to optimse to try and improve the situation we found the following;
1. MCafee Antivirus scanning
2. The DB was sitting on a RAID 5 Volume.
3. File fragmentation is at 86%
We are trying to do the following to optimse the following to correct the situation.
1. For the Antivirus, we have put the DB files under exceptions option.
2. We have created a RAID 10 volume and that is where we will move the Db data files
3. We would like to leave the log file on the current volume. But because Fragmentation is resource intensive we wanted to move the log files to another volume and then defrag the current volume. My question is, if you move the busy DB files to another volume and defrag the former volume and then copy back the files, is there a benefit. When you copy the files to another volume, which is nicely fragmented is there a benefit or it gets fragmented the same way? I need your advice on this one.
Also check your partition alignments. This can give you 10%-20% more disk io.
http://support.microsoft.com/kb/929491
Is the disk sub-system SAN or Directly attatched as SANs (depending on the manufacturer) arent nessecarily effected by file fragmentation.
http://support.microsoft.com/kb/929491
Is the disk sub-system SAN or Directly attatched as SANs (depending on the manufacturer) arent nessecarily effected by file fragmentation.
You have auto statistics on and/or regular index maintenance plan?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi all,
Am now inclined to looking at other issues as well, after reading your comments here is what we found;
1. Using performance monitor, we noticed that during peak hours we are having disk queues
2. We restored the DB on the back and ran the command DBCC Showcontig, we noticed that tables commonly used had extent fragmentation up to 99.99%, I suspect this could be a big problem. Our application developers had give us the impression that there is auto indexing within the application. My question is can I rebuild indexes while we are running live, am worried about the server becoming too slow. Any ideas or how best can we do this. Which one should we do first, rebuild indexes first or defragment the drive first.
Am now inclined to looking at other issues as well, after reading your comments here is what we found;
1. Using performance monitor, we noticed that during peak hours we are having disk queues
2. We restored the DB on the back and ran the command DBCC Showcontig, we noticed that tables commonly used had extent fragmentation up to 99.99%, I suspect this could be a big problem. Our application developers had give us the impression that there is auto indexing within the application. My question is can I rebuild indexes while we are running live, am worried about the server becoming too slow. Any ideas or how best can we do this. Which one should we do first, rebuild indexes first or defragment the drive first.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With regards to the order in your final line. I would focus on the indexes first. As to defragment the drive you will need to take you SQL instance offline as otherwise it could corrupt your database.
Best way to defragment the disk would be to copy the files off, format the drive, copy them back. I have done this before and it actually saves a lot of time. (Depending on the size of the database)
Best way to defragment the disk would be to copy the files off, format the drive, copy them back. I have done this before and it actually saves a lot of time. (Depending on the size of the database)
Was just thinking about other reasons that you may have 99.9% fragmentation. If you are doing any shrink operation you should really turn this off unless it is absolutely needed. You may as well rename any shrink database command to fragment_all_data.....
http://www.diskeeper.com/defrag/dk-boost-sql-server.aspx