DBCC SQLPERF 参数总结

DBCC SQLPERF 参数总结

DBCC SQLPERF is one of the most important undocumented SQL Server performance tuning pearls that allows you to answer the question: "how well is my system performing". DBCC SQLPERF provides different kinds of statistical data which is gathered by the system at runtime. This data can be used to analyze and evaluate your system and pinpoint possible bottlenecks.

This article tries to provide a complete set of documentation; describing how to use DBCC SQLPERF, what information is provided through the different options, which shape the data is returned in and how to use/interpret the data.

DBCC SQLPERF can be called in two ways. The first way it will return a result set of statistical data, the shape and content of result set depends on the key value provided. The second way resets the statistics values (to zero) so you can start measuring again from that point onwards, until the values get reset they are accumulated since the start of the server.

Returning the gathered statistics/data:
DBCC SQLPERF(<key>) [WITH { [NO_INFOMSGS] , [TABLERESULTS] } ]

Reset the data structures used to gather the statistics/data to zero:
DBCC SQLPERF(<key>, clear) [WITH { [NO_INFOMSGS] } ]

The WITH clause helps you control the output format, there are two WITH clauses that can be applied:

NO_INFOMSGS: which will suppress the "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message

TABLERESULTS: which will cause the output of the DBCC command to be in the form of a result set, so you can re-use it latter. The DBCC SQLPERF options always return a result set, so this option is already implicitly set. I prefer to still make it explicit, which is why you see me use it in my examples.

DBCC SQLPERF can be called with different <key> values; each key value returns content and shape of data. The allowed key values are described in the table below.

Description of the key values:

Key Description
LogSpace DBCC SQLPERF(LOGSPACE) is the only officially documented SQLPERF key value in the SQL Server Books Online; it returns statistics data about the usage of transaction-log space in all databases.
NOTE: Since this option is officially documented it will not be covered in any further detail.
NOTE: This option can not be cleared, since it is not based on statistical data.
 
UMSStats SQL thread management
WaitStats  resources, wait types
IOStats outstanding reads & writes (note: always zeros)
RAStats read ahead activity (note: always returns zeros)
Threads I/O / CPU / memory usage per thread
SpinLockStats statistics on spinlocks
UMSSpinStats statistics on UMS
NetStats ODS statistics
LRUStats LRU-MRU chain statistics (Note: free page scan always zero)

Supported SQL Server versions:

DBCC SQLPERF is only support by SQL Server 7.0 and later versions, before 7.0 the information provided by DBCC SQLPERF is only available through a DBCC extension DLL created by Microsoft Product Support Services, which laid the foundation of DBCC SQLPERF in its current format. In SQL Server 2005, most information that you can only get through DBCC SQLPERF in 2000, can be retrieved via the new dynamic management views.

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/suntt/archive/2006/03/18/628171.aspx