SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Disk Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/28/2004 :  02:02:59  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
My Physical disk drive on one of my SQL Servers has become very fragmented. I took SQL Server down on the hot server, and attempted to use diskeeper to defragment it. To no avail it wouldn't defragment the databases. Any of you had any experience with this issue.

Thanks,

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/28/2004 :  03:20:14  Show Profile  Reply with Quote
how full is the disk? There may not be enough contiguous freespace to do much of anything. If you are going to do a defrag, you might want to shrink your databases beforehand.

-ec
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/28/2004 :  03:22:52  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
I have 69GB Free, and my largest database is 8GB. Hmmmmmmmm!

Will try again Monday, it is getting late.
Go to Top of Page

LearningSQLDBA
Starting Member

USA
10 Posts

Posted - 03/28/2004 :  20:59:09  Show Profile  Visit LearningSQLDBA's Homepage  Reply with Quote
You did not actually state what problem you are trying to solve, but it sounds like you are not happy with the performance of your DB(s).

Bad database performance can be for many reasons, so I would not want to steer you in the wrong direction.

However one of the reasons for bad Database performance has to do with the internal handling of Database pages. There are page splits to consider, index page splits, padding of indexes, the fill factor for pages, etc. Where indexes are heavily maintained, they periodically need to be rebuilt (& statistics maintained).

May I suggest you refer to BOL for a review of the following:
DBCC INDEXDEFRAG
DBCC DBREINDEX
DBCC REINDEX
DBCC SHOW_STATISTICS
CREATE STATISTICS
sp_createstats
FILL FACTOR
CREATE INDEX (pad_index and fillfactor options)
Pages - fillfactor (second level entry)

HTHs

LearningSQLDBA
Go to Top of Page

LearningSQLDBA
Starting Member

USA
10 Posts

Posted - 03/28/2004 :  21:01:52  Show Profile  Visit LearningSQLDBA's Homepage  Reply with Quote
Cancel my comments.

Sorry!

LearningSQLDBA
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/28/2004 :  21:09:20  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Did it give an error when you tried to defrag the drives? Is this just a single drive, or an array? If so, is it hardware or software? What's the brand of server/RAID controller?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/29/2004 :  06:59:01  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
It is a Compaq 5300 series SCSI Raid Controller. There were no errors, and the drives are raid 5, which I know is a performance no no. I am happy with the performance of server, the drive is just defragmented badly.

Thanks for your comments.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/29/2004 :  08:32:34  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Have you done a diagnostic on the drives and controller? If you have a hot server, you might just destroy the RAID array and rebuild it. That should definitely take care of any defragmentation issues; and there isn't a whole lot more to do if the defrag utility didn't work.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/29/2004 :  08:36:03  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
I ran the diagnostics on the controler with no errors, but that is actually looking like the plan if I want to get the server defragged, and that is what I was afraid of.

Thanks,
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1535 Posts

Posted - 03/29/2004 :  09:04:17  Show Profile  Visit JimL's Homepage  Reply with Quote
I have run into alot of folks thinking their performance was due to drive fragmentation.

But when they ask me to look the most common problem I find is not enough memory.

where SQL is concerned Memory, Memory, Memory.

Jim
Users <> Logic
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/29/2004 :  09:07:33  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Anytime you can add memory that's a good thing, but:

I am happy with the performance of server, the drive is just defragmented badly.

This would seem to indicate he's not having problems with performance.

:) Maybe you can get them to add some memory though to help out. That would be kind of kewl.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/29/2004 :  09:08:20  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
I wasn't so concerned about performance. The server is probably a little overkill for the number of users. Reading and writing contiguous files, is a lot easier on a server than reading fragmented files.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/29/2004 :  09:23:26  Show Profile  Reply with Quote
Have you tried the contig utility from www.sysinternals.com? It will defrag a single file, or a directory of files from the command line. Of course, the database(s) need to be offline to run the tool. I either take the entire database down, or defrag them one at a time by using detach/attach commands.

I use a command like the following to generate a fragmentation report:
contig -a "R:\Program Files\Microsoft SQL Server\MSSQL\Data\*"


This command would defrag the data directory:
contig -v "R:\Program Files\Microsoft SQL Server\MSSQL\Data\*"



-ec

Edited by - eyechart on 03/29/2004 09:30:05
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/29/2004 :  09:26:22  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
Do you have to take the database off line? I woudl think so. At any rate, that is great.
I will definitely give it a try.

Thanks,
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 03/29/2004 :  09:31:12  Show Profile  Reply with Quote
quote:
Originally posted by TSQLMan

Do you have to take the database off line? I woudl think so. At any rate, that is great.
I will definitely give it a try.



yes, I either take the entire SQL server down and defrag all the datafiles, or I take a single database offline and defrag just it's files.


-ec
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 03/29/2004 :  10:01:50  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
That's a nice tool. Hadn't ever heard of it before.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000