SQL Optimisation Vs Disk Defrag?
-
What type of performance gain have people experienced by defragmenting the hard disk as opposed to running SQL server optimisations (dbcc reindex, update statistics, recompile stored procedures).
Also what is the best disk defrag utility out there, in terms of the results and it's speed of execution?
And what do people think of the basic Windows 2000 Defrag?
Tom.
-
SQL Server Optimizations should always be run. This should be part of your ongoing monitoring and maintenace of your production databases (which IMHO you should not do via maintenance plans, but with explicitly created jobs).
Disk Defragmentation should be run as needed - I've yet to find a really good external tool (although I haven't searched hard, to be honest) that I would trust running on a production server - so that leaves you with windows. I don't know whether you can schedule this - my attempts have been iffy at best (on my workstation), so I leave this activity to server admin's. Obviously, you should not be doing this during up-time, so carefully review the stat's, and arrange downtime if the file fragmentation deserves it (imho this should be a very infrequent activity).
-
Thanks for the advice. That is what we have more or less decided to do here. We have a full 24 hours of down time to do all the optimisations that we normally cannot do, so we have been looking at disk defragmention.
What we are really looking at here is the defrag of the database data files.
Question:
If we backed up the database files onto another hard disk, dropped the databases and deleted the DB files, and then recreated the databases and restored them from the dumps on the other hard disk, would this restore force Windows to defrag the database files by default?
Tom.
-
Out of interest, what version of SQL are you using? Your reference to "dump" makes me think of 6.5 etc., in which case I don't have any practical experience in it, and I understand restore back then was not a simple thing.
For the purposes of answering, I'm going to assume SQL 7 or 2000.
If you backup the database, the drop and restore the databases, you will force windows to re-allocate the files that form the dataabse. Assumming enough free space, one would hope that Windows will allocate one contiguous file, but that hasn't really been my experience.
That said, I would consider the following:
(1) Backup - always a good thing to do.
(2) shutdown SQL Server. We want those files to be inactive.
(3) run windows defrag to analyse
(4) assuming sufficient space (it usually want 15% free, and I always want at least 20% free on any drive that holds my SQL database), run the defrag
(5) review the report to see what files were not defragmented, and see if they were SQL's (.mdf, .ldf, ndf etc.)
(6) start SQL server, ensure that your database is up and working.
This could be faster since you are not having to re-allocate the files, which can take a while - I can't really comment on performance though. One advantage of this method is that you have not dropped your database, and don't need to concern yourself with applying/fixing permissions etc.
HTH
-
Both SQL 7.0 and 2000 on different physical servers.
I've just moved over from Sybase to SQL Server recently so my use of "dump" is related to that.
Tom.
-
The built-in Windows 2000 defrag is absolutely rubbish. You need at least 15% free disk-space to do a defrag and even if you have the tool will tell you that only a percentage of that is available for the defrag and therefore the results won't be guaranteed.
This means that on a 100Gb system you are going to need over 15Gb free!
The DisKeeper product has some good reviews. It certainly doesn't need stupid amounts of free space to do its stuff.
-
I had been lead to understand that the Windows defrag IS the Diskkeeper engine with none of the automation features available...
In any case the Diskkeeper product is usually well worth the purchase price.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I would be extremely suprised to find that Windows defrag and Diskeeper were the same product.
No-one would willingly buy a product that was as bad as Windows defrag.
-
I have used the diskeeper product and yes, I have also heard that it is the grander version of microsoft's bundled offering (many of MS utilities are licensed, cut-down versions of others - which serve most ppl just fine).
Diskeeper has always done a nice job for my machines and appears to do it with a reasonable speed, although you should never use the "set it and forget it" mode as it will defrag in the background and supposedly not affect your work - rubbish
Definitely not a good thing on a server! -
I've also heard that Windows defrag was based on Diskkeeper. In my experience the "WIndows" tool is a lot slower and less efficient. As suggested, if you have the maint window, shut the server down and run Diskeeper on the entire disk subsystem. It can't hurt!!
-
Microsoft does indeed appear to have licensed at least some portion of the Diskkeeper engine for the Defrag utility. If you look at Help->About Disk Defragmenter... it mentions Executive Software Int'l.
Kind of like the Windows Backup util and Veritas.
Anyway, I'll put my two bits in for Diskkeeper, it does a good enough job for me.
-- J.Kozloski, MCDBA, MCITP
-
Hi there.
Your discussion is the reason I joined the forum.
I am not as SQL expert, because I concentrate on DISK FRAGMENTATION, which you will probably call "External Fragmentation" because you know the difference.
The problem with Fragmentation (Both External and Internal) that until now nobody really measured it, and you do not know what is your real situation.
To make my story short, I decided to declare war on Internal Fragmentation too. If it is possible to see the physical location of the data components within the Database, like you can see the extents of the files within a disk - It will be possible.
If someone is challanged, please reply.
Koby BILLER
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply