defrag for a SQL Server?

Was wondering what others do about defragging their SQL Server?
Is there a schedule, do you do it when ever, or ?
Do you have to take the server offline while in defrag? What is the
performance hit if you don't?
How much do you really gain with the defrag?
Thanks,
Scott<-
Great questions Scott.
The answer (as it so often is in database) is, "it depends"
How big is your database? What type of database is it? (OLTP or OLAP).
How much data do you have? How bad is the fragmentation? DBCC SHOWCONTIG
will help you there. Read up on it in the BOL (Books Online).
What type of storage mechanism are you using for your data files? RAID 0,
RAID 5?
Some OLTP systems actually benefit from "some" fragmentation. OLAP systems
do not.
How busy is your server?
General tips that I like to follow:
1. Build your database as big as you think it needs to be when you first
create it. I'm not a big fan of autogrowth because of the fragmentation
issue with the .mdf files. Just a personal preference.
2. Rebuild your indexes on a regular schedule. Use DBCC SHOWCONTIG to
check fragmentation levels within tables and indexes regularly. When you
have slow times go ahead and defrag.
3. You can also run the SQL Profiler to check when queries are running more
slowly and performance begins to drag in other areas. This may indicate
some action needed as far as re-indexing is concerned.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
news:%23tapzEXeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Was wondering what others do about defragging their SQL Server?
> Is there a schedule, do you do it when ever, or ?
> Do you have to take the server offline while in defrag? What is the
> performance hit if you don't?
> How much do you really gain with the defrag?
> Thanks,
> Scott<-
>
|||This whitepaper covers most of your questions in depth
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
news:%23tapzEXeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Was wondering what others do about defragging their SQL Server?
> Is there a schedule, do you do it when ever, or ?
> Do you have to take the server offline while in defrag? What is the
> performance hit if you don't?
> How much do you really gain with the defrag?
> Thanks,
> Scott<-
>
|||Thank you for your reply.
The main DB in question is 6 gb and growing. I believe its an OLTP, We
use RAID 0,
Its used by our Webserver and Internal Applications, so there are maybe 40+
users hitting it 8-5 every day. Though its more if a hit here and there.
They are not constantly getting data from it.
I'll Look into the others...
Thanks again for your reply.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:u70JPVXeEHA.3732@.TK2MSFTNGP11.phx.gbl...
> Great questions Scott.
> The answer (as it so often is in database) is, "it depends"
> How big is your database? What type of database is it? (OLTP or OLAP).
> How much data do you have? How bad is the fragmentation? DBCC
SHOWCONTIG
> will help you there. Read up on it in the BOL (Books Online).
> What type of storage mechanism are you using for your data files? RAID 0,
> RAID 5?
> Some OLTP systems actually benefit from "some" fragmentation. OLAP
systems
> do not.
> How busy is your server?
> General tips that I like to follow:
> 1. Build your database as big as you think it needs to be when you first
> create it. I'm not a big fan of autogrowth because of the fragmentation
> issue with the .mdf files. Just a personal preference.
> 2. Rebuild your indexes on a regular schedule. Use DBCC SHOWCONTIG to
> check fragmentation levels within tables and indexes regularly. When you
> have slow times go ahead and defrag.
> 3. You can also run the SQL Profiler to check when queries are running
more
> slowly and performance begins to drag in other areas. This may indicate
> some action needed as far as re-indexing is concerned.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
> "Scott Townsend" <scott-i@..-N0-SPAMplease.enm.com> wrote in message
> news:%23tapzEXeEHA.3792@.TK2MSFTNGP09.phx.gbl...
>