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

Collecting Statistics

As the DBA begins to work through the process of building a SQL Server capacity planning system, the question may arise, “What exactly should be collected?” The answer to this question can vary widely depending on what one wants to track, analyze, and forecast.

As a general rule of thumb, the DBA should collect availability statistics, both global and object level storage-related measures and global, session and sometimes SQL level system usage/performance metrics.

On the availability front, the DBA is basically looking for heartbeats in the following areas:

  • Up/down status of database server machine.

  • Up/down status of SQL Server instance.

  • Up/down status of SQL Server agent, if used for job scheduling, etc.

  • Up/down status of miscellaneous and ancillary SQL Server components.
    From a storage and object structure standpoint, some of the metrics that should be collected or tracked include the following:

  • Database server-drive used and free space.

  • Database total, used, free, and used/free percentages.

  • Transaction log total, used, free, and used/free percentages.

  • Database total table and index reserved space.

  • Full database backup space used.

  • Filegroup total, used, free, and used/free percentages.

  • Total table and index reserved space for filegroups.

  • File total size.

  • Table and index reserved space.

  • Table row counts.

  • Table extent fragmentation (SQL 7 and 2000).

  • Table logical fragmentation (SQL 7 and 2000).

  • Table forwarded records.

  • Table scan density (SQL 7 and 2000).

  • Table average page density.

  • Table extent count vs. extent switches (SQL 7 and 2000).

  • Index extent fragmentation (SQL 7 and 2000).

  • Index logical fragmentation (SQL 7 and 2000).

  • Index scan density (SQL 7 and 2000).

  • Average index fragmentation (SQL Server 2005).

When looking at what to collect from a performance standpoint, there are obviously several metrics that can be obtained from the master.dbo.sysperfinfo table as well as other sources. Table 8.1 lists some of the more important statistics that assist with trend analysis in their respective areas


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