| |
 |
|
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 |
