 |
|
SQL Server Tips by Burleson |
Examining CPU Activity
In terms of CPU activity, a query can be issued and the system
statistical function @@CPU_BUSY can be selected. It shows time in
milliseconds that the CPU has spent working since SQL Server was
started. This is a steadily increasing number and is best viewed via
a monitor or procedure that can compute differences between sampled
intervals, along with a comparison of @@IDLE. This function is a
millisecond measurement of how idle SQL Server has been since
startup.
Some sources indicate that @@CPU_BUSY has to be multiplied by @@TIMETICKS
to get the actual time in milliseconds. This claim does not appear
to be valid. Testing by some savvy SQL Server DBAs has determined
that @@CPU_BUSY has to be multiplied by @@TIMETICKS and then divided
by the number of CPUs on the server to get the actual time. To
validate this on the box, the following test_time T-SQL code can be
run:
* test_time.sql
-- Script is available in the Online Code Depot
The CPU count number of four in the above T-SQL code would have to
be replaced with the actual number of processors the machine has in
order to get the proper numbers out on the box.
The easiest approach to obtaining current CPU activity is to simply
use the Microsoft Task Manager and locate SQL Server executable.
Task Manager can give real time and historical peaks/dips that have
occurred since the monitor has been running.
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 |
