Re: Best Practice for SBS 2008

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hold up. SQL is a different beast. The one thing both of your cited examples have in common is SQL. Defragging a SQL database is *not* the same as a file-level defrag. The performance gains don't come from how quickly files are found or pulled from the drive, but by how much *raw data* is pulled form the drive.

SQL breaks records into pages. When you run a query, even if that query returns one record, the entire page that contains that record is pulled into memory. So if you have queries that pull 10 or 15 records (or 200 or 500 or 10,000) then multiple pages are read. If you index properly and occasionally defrag the database (not a defrag in a traditional sense) then you are actually reducing the number of PAGES that have to be read. That can be the difference of GIGABYTES and will impact memory usage, page file usage, and many many other aspects.

Again, a file defrag is arranging the file in contiguous blocks. Which in RAID-5 doesn't really exist.

A SQL defrag (or Jet for Exchange, or even old school dBase....database defragging in general) involves re-arranging the records to best match the index to reduce the number of pages loaded for a given query. The individual page files could still be *heavily* fragmented, but it is only having to load one page for a query instead of 10, so the performance increase is an order of magnitude faster.

--

And it should be noted that a SQL database does not need to be on a separate partition to be cleaned up. You can take the exchange IS offline and defrag it even if it is on C. Same with a SQL database. Or MySQL. Or whatever. Database defrag != file defrag.

:)

-Cliff


"Leythos" <spam999free@xxxxxxxxxx> wrote in message news:MPG.24457999366cb85e989aed@xxxxxxxxxxxxxxxxxxxxxxx
In article <#HSXce7tJHA.2148@xxxxxxxxxxxxxxxxxxxx>, cgaliher@xxxxxxxxx
says...
But RAID-5? or RAID-10? Or even RAID-0? Not so much. This is both
real-world experience and hardcore documented benchmarks (Tom's Hardware
even did a write-up several years back...a myth-busters style test.)
So.....


Seen many claimed tests, but the problem is that test generate test
situations that don't mimic real-world data use/fragmentation.

I can honestly say that with a 12xDrive RAID-5 solution, after 1 year of
use, many gig of changes per day, massive SQL databases, that a defrag
returned user performance back to "like new" on the server and for users
accessing both the databases and files.

I'm not saying that you need to run defrag daily or weekely, you need to
base it on your system use and layout.

According to most benchmark testing, if you believe it, a 12 Drive RAID-
5 solution would not benefit from a Defrag, but it was very noticeable -
Profile Loads, My Documents Access, SQL transactions taking 40% less
time to execute, etc....

The same is true on a 6 x Drive solution for a document imaging app,
8,000 new documents per day, spread over a year with no maintenance
(meaning no defrag), SQL database showing 58,000+ fragments of the MDF
File, all of the PDF images were fragmented... Access by 50 users was
timing out.... Document people were unable to resolve the issue, still
timing out. I took the server down, defragged it with SQL off-line, so
that SQL MDF/LDF files would also defrag, increased the SQL files to
expected next years size (so they would not fragment again) and
restarted services (not the server) and everything was working as fast
as the day it was installed.... Only change was a DEFRAG...



--
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@xxxxxxxxxx (remove 999 for proper email address)

.

Relevant Pages

  • Re: Best Practice for SBS 2008
    ... real-world experience and hardcore documented benchmarks (Tom's Hardware ... I'm not saying that you need to run defrag daily or weekely, ... Profile Loads, My Documents Access, SQL transactions taking 40% less ...
    (microsoft.public.windows.server.sbs)
  • Re: Best Practice for SBS 2008
    ... SQL is a different beast. ... Actually, there are two types of defrag when it comes to SQL, one is ... There are performance gains in defragging the SQL data files as well as ...
    (microsoft.public.windows.server.sbs)
  • Re: Server freeze while defragging
    ... MCSE, MCTS: SQL 2005, Project+ ... Are you trying to do this on the active node?? ... In a cluster situation, the c: ... really benefit from a defrag, at least not enough to bother. ...
    (microsoft.public.windows.server.general)
  • Re: Defrag - is it safe to use?
    ... On SBS2003 is it safe to defrag using the builtin Tools utility? ... You can't really defrag files that are locked/in use - such as SQL ... SQL instance or detach the database files. ... Trust yourself. ...
    (microsoft.public.windows.server.sbs)
  • Re: Relational database & OO
    ... And when was the last time you saw an RDB that stored a telephone ... The modern relational theory permits to store values of user-defined type: ... Neither do the relational model or SQL. ... Your comprehension of the SQL database performance is not good. ...
    (comp.object)