Solved

Defragmenting the Hard drive to improve SQL DB performance

Posted on 2010-11-15
9
336 Views
Last Modified: 2012-05-10
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
9 Comments
 
LVL 10

Expert Comment

by:c_a_n_o_n
ID: 34135830
I guess it would depend on the defragmentation software, Diskeeper is an excellent product that does support defragmentation of SQL servers and may be a better defragmentation solution.
http://www.diskeeper.com/defrag/dk-boost-sql-server.aspx
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34136153
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.
0
 
LVL 21

Expert Comment

by:mastoo
ID: 34136244
You have auto statistics on and/or regular index maintenance plan?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 16

Accepted Solution

by:
EvilPostIt earned 300 total points
ID: 34136278
You may want to do a bit of monitoring around the disk itself to ensure its not actually the database. I would suggest looking at the disk queue lengths.
0
 
LVL 20

Assisted Solution

by:Iain MacMillan
Iain MacMillan earned 200 total points
ID: 34136782
the latest version of Diskeeper Server is very good at defragging when the server is not @ peak usage, and has new Intelliwrite prevention routines which can reduce the need for defragging in the first place.

I agree with EvilPostIt's reply, you need to check that its not your disk's I/O or RAID controller performance that's letting you down, as i have run several large SQL systems in RAID 5 over the years on HP Proliant direct attached storage, and never encounter many issues (keeping spare disks is a must though, even with hot-spares enabled).

if you do decide to move the DB, as long as the destination has been defragged beforehand, it should copy contiguously, which will then allow you to quickly defrag the rest of the log partition.  I usually set my DK to allow manual defragging at high priority, but the automated jobs run at low priority (InvisiTasking) in the background and you can specify jobs to be done out-with peak times, and backup schedules (like weekends).

PDF doc at bottom of page regarding Exchange and SQL DB - 3rd Link from bottom, you can also get a 30 day trial if you want to test it out - http://www.diskeeper.com/business/diskeeper/server/default.aspx
0
 

Author Comment

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

0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 300 total points
ID: 34143064
Hi ackimc,

Yes you can re-index online, though depending on the version of SQL Server you have will give you how many options you have. If you have enterprise version the you can use a rebuild statement
ALTER INDEX [INDEXNAME/ALL] ON [TABLE] WITH ONLINE=ON

Open in new window

.

You can still use the REBUILD statement in non enterprise versions is just means that during the operation a table lock will be placed on the base table.

If you do not want to use the rebuild statement then you still have another option (although it will take a lot long with fragmentation for 99%). You could use the
ALTER INDEX [INDEXNAME/ALL] ON [TABLE] REORGANIZE 

Open in new window

statement which would also defragment your indexes.

Just as a note though, you mentioned that you are using DBCC SHOWCONTIG and that you are running 2005. Microsoft have added some useful DMV's in SQL Server 2005. Have a look at running this
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('[DBNAME]'),NULL,NULL,NULL,NULL)

Open in new window

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34143074
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)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34143968
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.....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 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