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/pr...n/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:
>
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
>
> --
> 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 havin
g
> 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 ha
rd
> 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 wil
l
> be quite a while before fragmentation at the file level will be an issue i
f
> 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...
>
>
2012年3月22日星期四
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
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
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/prodtechnol/sql/2000/maintain/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/prodtechnol/sql/2000/maintain/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.
>
>|||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...
> 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/prodtechnol/sql/2000/maintain/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.
>>|||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...
> > 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/prodtechnol/sql/2000/maintain/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.
> >>
> >>
> >>
>
>|||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...
> 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...
>> > 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/prodtechnol/sql/2000/maintain/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.
>> >>
>> >>
>> >>
>>|||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...
> > 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...
> >> > 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/prodtechnol/sql/2000/maintain/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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
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/prodtechnol/sql/2000/maintain/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/prodtechnol/sql/2000/maintain/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.
>
>|||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...
> 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/prodtechnol/sql/2000/maintain/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.
>>|||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...
> > 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/prodtechnol/sql/2000/maintain/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.
> >>
> >>
> >>
>
>|||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...
> 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...
>> > 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/prodtechnol/sql/2000/maintain/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.
>> >>
>> >>
>> >>
>>|||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...
> > 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...
> >> > 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/prodtechnol/sql/2000/maintain/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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Disk defragmentation on SQLserver
SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
Bill
First off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
Bill
First off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
Disk defragmentation on SQLserver
SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
--
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
--
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
Disk defragmentation on SQLserver
SQLserver 200 SP3 running on Win2000
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
I check disk D: where all the databases reside and found that it is heavily
fragmented. The list of most fragmented files include some important
database (MDF) files.
I would like to defragment the disk but also concerned about possible
problem with the databases in question since they are up and running during
the defragmentation process.
What's a safe procedure to perform defragmentation on the the disk and how
to reduce fragment in the furture?
Any suggestion is greatly appreciated.
BillFirst off always make sure you have FULL and tested backups before
attempting something like that. While some vendors state they can defrag
the sql files while running I personally would not trust my data to it. If
you want to prevent fragmentation in the first place you should always start
with a clean disk and each time you add a new database you should do the
following:
One at a time:
1. Create the database with the files at say 10MB in size. (this is so you
can always shrink it down later if need be)
2. Immediately after creating the database, alter the database and grow
each file to the size you think you will need for the next year or two
allowing plenty of room for free space in each file.
If you do that for each database as you create them the files will be
contiguous on disk and there will be no need to defrag them in the future
unless you srink or grow them again.
Andrew J. Kelly SQL MVP
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:%2368scSPcEHA.2520@.TK2MSFTNGP12.phx.gbl...
> SQLserver 200 SP3 running on Win2000
> I check disk D: where all the databases reside and found that it is
heavily
> fragmented. The list of most fragmented files include some important
> database (MDF) files.
> I would like to defragment the disk but also concerned about possible
> problem with the databases in question since they are up and running
during
> the defragmentation process.
> What's a safe procedure to perform defragmentation on the the disk and how
> to reduce fragment in the furture?
> Any suggestion is greatly appreciated.
> Bill
>
订阅：
博文 (Atom)