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

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  

 

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