| Author |
Topic |
|
TSQLMan
Posting Yak Master
160 Posts |
 Posted - 2004-03-28 : 02:02:59
|
| 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
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-28 : 03:20:14
|
| 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 |
 |
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-03-28 : 03:22:52
|
| I have 69GB Free, and my largest database is 8GB. Hmmmmmmmm!Will try again Monday, it is getting late. |
|
|
|
LearningSQLDBA
Starting Member
10 Posts |
Posted - 2004-03-28 : 20:59:09
|
| 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 INDEXDEFRAGDBCC DBREINDEXDBCC REINDEXDBCC SHOW_STATISTICSCREATE STATISTICSsp_createstatsFILL FACTORCREATE INDEX (pad_index and fillfactor options)Pages - fillfactor (second level entry)HTHsLearningSQLDBA |
|
|
|
LearningSQLDBA
Starting Member
10 Posts |
Posted - 2004-03-28 : 21:01:52
|
| Cancel my comments.Sorry!LearningSQLDBA |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-28 : 21:09:20
|
| 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?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-03-29 : 06:59:01
|
| 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. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-29 : 08:32:34
|
| 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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-03-29 : 08:36:03
|
| 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, |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-03-29 : 09:04:17
|
| 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.JimUsers <> Logic |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-29 : 09:07:33
|
| 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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-03-29 : 09:08:20
|
| 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. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-29 : 09:23:26
|
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 |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-03-29 : 09:26:22
|
| 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, |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-03-29 : 09:31:12
|
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 |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-29 : 10:01:50
|
| That's a nice tool. Hadn't ever heard of it before.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
Disk Performance