Choosing and Configuring Hardware - Professional SQL Server 2005 Performance Tuning - page 162

of running out of space in a file and having to manually add more space. However, it should only be
there as a last resort as it could create fragmented files on disk, which will impact performance.

I attended a CritSit once where a customer was ''autogrowing'' their database in 10 MB increments
throughout the day and then archiving data at night and shrinking the file again. Performance was dire
due to the constant autogrow events and the heavily fragmented database files.

If you don't have dedicated drives for the databases and you're starting from scratch the best method is:

1.

Install the OS.

2.

Defragment the disk.

3.

Install any applications (SQL Server).

4.

Defragment the disk.

5.

Create data and log files at max size.

6.

Stop SQL Server, check for fragmentation, and defrag if necessary.

7.

Disable autogrow or at least make the increment large so it happens rarely.

8.

Routinely defragment the disk to preserve contiguous free space in the event you ever need
to add more SQL Database files.

In most cases the operating system's disk defragmenter does a great job and is all you need, but there
are various third-party tools that can give you more control, speed, and centralized management for
coordinating defragmentation of multiple servers.

If you have a large database on a dedicated drive and you're concerned about file fragmentation, copying
it off the drive and then back again will remove any fragmentation. This requires enough free disk space
of course to copy the database somewhere, so if that isn't available then stop SQL Server (to close the
database files) and use the OS defragmenter to fix the problem.

Best Practice
Plan your database growth rate and size the files appropriately so you can switch off
autogrow.

CPU
SQL Server 2005 operates in a very different environment than previous versions of SQL Server. When
SQL Server 2000 was launched, a large server used for SQL Server might have two or maybe even four
processors. Today SQL Server 2005 is commonly run on 8 or 16 processors and is able to run on the largest
servers with 64 processors, and up to 2 TB of RAM running Windows Server 2003 Data Center Edition
with SP1. Today there is a bewildering array of processor options to consider when thinking about a new
system. SQL Server 2005 can run on all the mainstream processors available today.

32-bit processors: x 86

❑

32-bit with 64-bit extension processors: x 64

❑

64-bit processors: IA64

❑