?
Solved

Defragmenting the Hard drive to improve SQL DB performance

Posted on 2010-11-15
4
Medium Priority
?
608 Views
Last Modified: 2012-06-22
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.
0
Comment
Question by:ackimc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Ernie Beek earned 668 total points
ID: 34135840
'When you copy the files to another volume, which is nicely fragmented is there a benefit or it gets fragmented the same way?'

I assume the first fragmented should be defragmented...

When you write a file or DB (they're both files) to a clean or defragmented drive, the computer will try to write the file in contingeous free blocks. on a fragmented drive that isn't possible because the empty blocks are spread throughout the disk. After defragmenting the free blocks should (almost) all be contingeous so the fille to be written would also be.
This should mean an improvement in the time neede accessing the file.
0
 
LVL 47

Assisted Solution

by:David
David earned 1332 total points
ID: 34136141
defragging rarely makes any significant improvement outside of marrketing literature from defrag software vendors.

why not exsmine root cause?
sql server does 64kb I/O.   is NTFS set up for 64KB on an ALIGNED partition? is raid10 set up for 64KB stripe?
if not, you could be leaving well over half your performance on the table
0
 

Author Comment

by:ackimc
ID: 34142826
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.

Dlethe, where do I check to see if SQL Server is set top use 64kb I/O and NTFS?

0
 
LVL 47

Assisted Solution

by:David
David earned 1332 total points
ID: 34142854
This walks you through checking/setting NTFS parms & alignment
http://theether.net/kb/100104

To check the RAID controller, you need to check the raid controller using whatever software came with it.  You want each disk to read/write exactly either 32KB or 64KB at a time .

Now above is just a summary of some biggies, certainly the types of things that will make a much bigger gain then messing with defragging.

You might want to check the msft i/o tuning guide for SQL as well
http://technet.microsoft.com/en-us/library/cc966412.aspx
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
New style of hardware planning for Microsoft Exchange server.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question