Disk Defragmentation, Query Performance?

Ok. How does Disk defragmentation and Query Performance relate to each other?
How often should we defragment SQL boxes in DEV and PROD? Do we see any data
retrieval performance benefit if we regularly defragment the disk? Any
pointers on this topic is appreciated. Assuming the box is dedicated SQL
server with no additional applications.
Have a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"S" <S@.discussions.microsoft.com> wrote in message
news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
> Ok. How does Disk defragmentation and Query Performance relate to each
> other?
> How often should we defragment SQL boxes in DEV and PROD? Do we see any
> data
> retrieval performance benefit if we regularly defragment the disk? Any
> pointers on this topic is appreciated. Assuming the box is dedicated SQL
> server with no additional applications.
|||Andrew,
Thanks for your response but my question is different here. I am asking
about Disk defragmenation not Index defragmentation.
"Andrew J. Kelly" wrote:

> Have a look at this:
>
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
>
> --
> Andrew J. Kelly SQL MVP
>
> "S" <S@.discussions.microsoft.com> wrote in message
> news:8CC6A253-6B54-4806-BCF9-E5E2889241BB@.microsoft.com...
>
>
|||On Mon, 28 Nov 2005 14:49:04 -0800, "S" <S@.discussions.microsoft.com>
wrote:
>Thanks for your response but my question is different here. I am asking
>about Disk defragmenation not Index defragmentation.
The logical defrags determine how many pages have to be fetched.
The physical defrags determine what it costs to fetch a physical page.
Obviously, they interact.
But (to generalize), most queries use mostly memory-cached pages, so
the physical fragmentation tends to be hidden behind the logical -
that is the logical generally goes bad first. I'd say logical defrags
are done more often on most systems than physical (though rebuilding
the clustered index does at least some of both). OTOH, if you have a
mostly batch system, with lots of scans and/or with huge tables, you
might have a system where the physical predominates.
Yes, it can help a lot, depending on your local circumstances, but if
you do regular rebuilds of the clustered indexes it tends to go bad
rather slowly, I guess would be the bottom line.
J.
ps - also probably matters less coming off RAID-5 then it would off
isolated spindles.
|||Sorry about that. If you create your database files at or near the size
that you need for a many months to come you should not need to worry about
continuing fragmentation at the file level. This usually comes from having
Auto Shrink or a job that regularly shrinks the files which is a bad
practice in general. So if it is an existing system then you should only
need to defrag the disk once. Dev boxes may require it done more often if
you create and drop dbs a lot.
Andrew J. Kelly SQL MVP
"S" <S@.discussions.microsoft.com> wrote in message
news:454C3E18-23CA-47A8-8B8A-485CFA1D8DA1@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Thanks for your response but my question is different here. I am asking
> about Disk defragmenation not Index defragmentation.
> "Andrew J. Kelly" wrote:
|||Assuming we have Transactional Databases on the server, when databases are
created we will have to create a large file size such that it need not grow
often. But when we have many databases on the server and over the years we
will defintely see growth in file sizes and ultimately leading to disk
fragmentation causing more disk I/O than necessary.
I have read that if the mdf, ldf files are in use then disk defragment will
not be done and might be skipped? If its a 24/7 shop then the possibility of
defragmenting the disk tends to be very less unless you put up a maintenance
page not even allowing users to log in. I wonder how often disk
defragmentation is done in the industry on the sql boxes. Do we see
significant performance improvment if at all we defragment disk?
As J said, I guess this would be at the bottom of the list, making priority
for
1) Tune Sql Queries
2) REINDEX/INDEXDEFRAG
3) Adding more RAM etc.,
And finally what tools are used in the industry apart from windows DISK
Defragmenter and Diskeeper for this purpose?
"Andrew J. Kelly" wrote:

> Sorry about that. If you create your database files at or near the size
> that you need for a many months to come you should not need to worry about
> continuing fragmentation at the file level. This usually comes from having
> Auto Shrink or a job that regularly shrinks the files which is a bad
> practice in general. So if it is an existing system then you should only
> need to defrag the disk once. Dev boxes may require it done more often if
> you create and drop dbs a lot.
> --
> Andrew J. Kelly SQL MVP
>
> "S" <S@.discussions.microsoft.com> wrote in message
> news:454C3E18-23CA-47A8-8B8A-485CFA1D8DA1@.microsoft.com...
>
>
|||A little bit of file fragmentation is not of concern. It has to be fairly
fragmented to make a dent in performance. But unfortunately there is no hard
guideline as to how much is too much. There are tools such as DiskKeeper
that will defragment on-line. You always should have a good backup first
and there may be a performance hit while defragging but they can be done
online. I think you will find that if you defrag all the db's once it will
be quite a while before fragmentation at the file level will be an issue if
you don't autoshrink.
Andrew J. Kelly SQL MVP
"S" <S@.discussions.microsoft.com> wrote in message
news:1122FF26-03BD-4C5D-8E2F-33DD9E8A85BA@.microsoft.com...[vbcol=seagreen]
> Assuming we have Transactional Databases on the server, when databases are
> created we will have to create a large file size such that it need not
> grow
> often. But when we have many databases on the server and over the years we
> will defintely see growth in file sizes and ultimately leading to disk
> fragmentation causing more disk I/O than necessary.
> I have read that if the mdf, ldf files are in use then disk defragment
> will
> not be done and might be skipped? If its a 24/7 shop then the possibility
> of
> defragmenting the disk tends to be very less unless you put up a
> maintenance
> page not even allowing users to log in. I wonder how often disk
> defragmentation is done in the industry on the sql boxes. Do we see
> significant performance improvment if at all we defragment disk?
> As J said, I guess this would be at the bottom of the list, making
> priority
> for
> 1) Tune Sql Queries
> 2) REINDEX/INDEXDEFRAG
> 3) Adding more RAM etc.,
> And finally what tools are used in the industry apart from windows DISK
> Defragmenter and Diskeeper for this purpose?
> "Andrew J. Kelly" wrote:
|||Thanks for getting back to me. I have downloaded a trial version of
Diskkeeper and it has some nice features like exclusion list, scheduling etc
and more over as you said its on-line operation.
Appreciate it for your time!
"Andrew J. Kelly" wrote:

> A little bit of file fragmentation is not of concern. It has to be fairly
> fragmented to make a dent in performance. But unfortunately there is no hard
> guideline as to how much is too much. There are tools such as DiskKeeper
> that will defragment on-line. You always should have a good backup first
> and there may be a performance hit while defragging but they can be done
> online. I think you will find that if you defrag all the db's once it will
> be quite a while before fragmentation at the file level will be an issue if
> you don't autoshrink.
> --
> Andrew J. Kelly SQL MVP
>
> "S" <S@.discussions.microsoft.com> wrote in message
> news:1122FF26-03BD-4C5D-8E2F-33DD9E8A85BA@.microsoft.com...
>
>
sql