Defragmenting Local Disk

Is there any performance gain from defragmenting the SQL
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy

Defragmenting issues

I would like to know how many DBAs take care of their file systems
defrag status's server.
I would like to know also if someone here is using some commercial tool
(diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
when they are in use.
Any feedback appreciated.
Regards.
--
Fabri
"Eat just one little baby and everyone remembers your name!"
- CELKODiskkeeper states that their tool is safe to use while sql server is
running. There will obviously be a performance hit while this is running
but it is up to you if you want to do it live or not.
--
Andrew J. Kelly SQL MVP
"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
> Regards.
> --
> Fabri
> "Eat just one little baby and everyone remembers your name!"
> - CELKO|||"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
>
I don't generally worry about fragmentation of database files. Regular best
practices like having extra space, pre-sizing data files and using large
auto-grow %, minimize both the fragmentation and the impact of
fragmentation.
A data file, or especially a log file, in a few large fragments has a
negligible impact on performance.
David

Defragmenting issues

I would like to know how many DBAs take care of their file systems
defrag status's server.
I would like to know also if someone here is using some commercial tool
(diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
when they are in use.
Any feedback appreciated.
Regards.
Fabri
"Eat just one little baby and everyone remembers your name!"
- CELKO
Diskkeeper states that their tool is safe to use while sql server is
running. There will obviously be a performance hit while this is running
but it is up to you if you want to do it live or not.
Andrew J. Kelly SQL MVP
"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
> Regards.
> --
> Fabri
> "Eat just one little baby and everyone remembers your name!"
> - CELKO
|||"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
>
I don't generally worry about fragmentation of database files. Regular best
practices like having extra space, pre-sizing data files and using large
auto-grow %, minimize both the fragmentation and the impact of
fragmentation.
A data file, or especially a log file, in a few large fragments has a
negligible impact on performance.
David

Defragmenting issues

I would like to know how many DBAs take care of their file systems
defrag status's server.
I would like to know also if someone here is using some commercial tool
(diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
when they are in use.
Any feedback appreciated.
Regards.
Fabri
"Eat just one little baby and everyone remembers your name!"
- CELKODiskkeeper states that their tool is safe to use while sql server is
running. There will obviously be a performance hit while this is running
but it is up to you if you want to do it live or not.
Andrew J. Kelly SQL MVP
"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
> Regards.
> --
> Fabri
> "Eat just one little baby and everyone remembers your name!"
> - CELKO|||"Fabri" <no@.sp.am> wrote in message
news:OlOLe.72251$fm.4850956@.news4.tin.it...
>I would like to know how many DBAs take care of their file systems defrag
>status's server.
> I would like to know also if someone here is using some commercial tool
> (diskeeper, speed disk or perfect disk or..) to defrag also LDF and MDF
> when they are in use.
> Any feedback appreciated.
>
I don't generally worry about fragmentation of database files. Regular best
practices like having extra space, pre-sizing data files and using large
auto-grow %, minimize both the fragmentation and the impact of
fragmentation.
A data file, or especially a log file, in a few large fragments has a
negligible impact on performance.
David

Defragmenting for SQL server performance?

Can Anybody guide me whether DEFRAGMENTING the drive improve the SQL Server performance in general.

What should be the location of the DATABASE files and the TRANSACTION log files.

Should they be in a single drive or seperate drives?

Thanks in advance

Jacx

For best I/O performance they should reside on different drives.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Defragmenting Data Files

Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still
attached?
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are sti
ll attached?
>
Nope... You have to stop SQL Server, or detach the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com.
.
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windo
ws
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Defragmenting Data Files

Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still attached?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are still attached?
>
Nope... You have to stop SQL Server, or detach the database.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are
>> still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windows
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
>> I don't know for sure if that is true for the Windows defragger but
>> certainly not for things like DiskKeeper. I am pretty sure that the
>> windows defragger was based on DiskKeepers technology so I suspect it may
>> be true for that as well. The makers of DiskKeeper vividly state that
>> their tool will defrag sql server files even if the server is live. Not
>> that I am recommending that practice but it is certainly possible.
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com