| |
 |
|
SQL Server Tips by Robin Schumacher
|
Server-Level Space
Considerations
In terms of avoiding space deficits at the physical disk layer, the
best preventative measure is to buy liberal amounts of disk space in
the beginning. From an SQL Server standpoint, server free space
deficits can be prevented by placing database and log files on
lesser utilized drives. Drives that contain the operating system
software and any swap files should be avoided.
From a performance standpoint, disks or arrays that exceed 80% of
their capacity should be avoided. The New Technology File System (NTFS)
needs room for its various house-keeping tasks to work, and when
disks exceed 80% capacity, NTFS become less efficient, with the end
result being impaired I/O.
The disk can be thought of in zones: inner, mid and outer. The bits
in the inner zone move past the head much slower than the bits in
the outer zone. Disks are engineered to write bits to the inner zone
last, because I/O to that zone is up to four times slower. Thus,
filling a disk beyond 80% capacity can result in slower performance
as the system does I/O in that inner zone.
If it is noticed that there is over-allocated space for some of the
databases, transaction logs, or files, the entire databases or
individual files can be reduced in size with the DBCC SHRINKDATABASE
or DBCC SHRINKFILE commands. There are various restrictions as to
what the SQL Server can and cannot do with respect to shrinking
databases and files, so the Microsoft online books should be
consulted for a thorough presentation of the subject.
Before leaving the topic of general space monitoring, there is one
oddity that needs to be highlighted regarding SQL Server space
reporting. SQL Server does not consistently maintain space
information in its internal tables and occasionally, both external
storage-related queries and SQL Server’s own space procedures will
report invalid or negative values for space. If this occurs for any
of the databases, the DBCC UPDATEUSAGE command can be run against
the problem databases and the inaccuracies should disappear.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
