Hi Friends,
In many companies developers won’t be given administrator privilege in OS on their test or development machines. When they execute a query and consider its consuming high CPU usage, since they won’t have admin privilege they are not able to confirm that the CPU is consumed by sqlservice or not. I’ve written a T-SQL Code to check the CPU usage consumed by Sqlservice.
Check out the below script.
Code:
DECLARE @CPU_BUSY int, @IDLE int
SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE WAITFOR DELAY ’000:00:01′
SELECT (@@CPU_BUSY – @CPU_BUSY)/((@@IDLE – @IDLE + @@CPU_BUSY – @CPU_BUSY) *1.00) *100 AS ‘CPU Utilization by sqlsrvr.exe’
Output:
CPU Utilization by sqlsrvr.exe
—————————————
0.850000000000000
Hope this small script will help you to check out sqlservice usage via T-SQL Statement!
can u pls get me the code for % cpu utilization of all processes on server
It covers all the CPU utilization
if there is 2 instance running (2 sqlservice.exe) does it cover cpu utilization for both, or only for the instance which is connected.
@Blackprince — It will provide usage only for that instance where you are executing this script.
vidhya,
great to see such a simple script to capture cpu utilization of sql engine.
do you think @@cpu_busy gives always accurate output?
Is there any evidence that cpu utilization by this query matches with %cpu of sql server instance from windows tool.
please share your experience.
Assume that windows machine is dedicated only for sql server and that is named instance.
@Suneel, yep! I have tested it my prev organization and it worked perfectly so we started using this script.