Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter