Sysprocesses CPU and CPU Percentage - utilization

I am not sure how many of you have faced this situation? I have faced it many many times. Somebody asks you to provide currently running expensive queries or processes. You quickly look up to sys.dm_exec_requests or sys.processes and export the result in a excel and send it to requestor.Mostly you would have included the columns like CPU,Memory,IO to indicate the utilization. Immediately the question people shoot at you is "Can you convert the CPU column to CPU %?. How much CPU% my query consumed?"

Eh..I have always found it bit hard to make people understand it doesnt indicate CPU%. So what does it indicate?

CPU_time column on Sys.Exec_requests and Sys.processes indicates amount of time in milliseconds the process was utilizing the CPU. CPU time is similar to "man hours" calculation used in Corporate world. If two men work for 1 hour each then the man hours spent is 2 hours. Similarly CPU time is amount of time all the processors spent on executing your query.

So does that show Query running time? No. It is "related" to query running time. Why it doesnt reflect exact running time? Because of the following factors.

* A query can be waiting for some other resource as well. The wait, if it doesnt involve any CPU processing then the CPU_time will be zero though the query may be running for long time. For example, if the query is waiting for a lock to be released, it doesnt consume any CPU_time. Other such examples can be latch wait,network wait etc.

* CPU_time is the time spend by all processes just like "Man hours". If the query uses parallel processing or consumes multiple processors, the CPU time returned by sys.processes/exec_requests will be much higher than actual running time of the query. For ex: if the query runs on both the processors p1 and p2, then CPU_time is equal to the sum of time consumed on all p1 and p2. So we can say CPU_time relates to actual execution time but not exactly the same.

So what can we infer from the CPU_time field? We understand that the queries which had the highest CPU_time have made the processor work harder and are likely to have caused a stress on the server.

Calling Function / UDF using linked server

If one needs to call a UDF or System function ( ex: Serverproperty, Getdate(), etc ) on a remote server, then one cannot use the usual 4 part linked server type query.ie., Assume you want to find the Servername, instance name or the current time on a remote sql server then, querying [linked_server].master.dbo.getdate() or [linked_server].master.dbo.Servername('Servername') doesn't work.  
      In such a case OPENQUERY function comes to our rescue. How does that help? Assume we want to get the Servername,Instance name, Version, Service Pack levels of a remote server. Then use the following query

SELECT *

FROM   Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
') 




As you can see, the first parameter is the linked server name. The second paramater is the actual query we want to execute.Within the quotation we can run any query and obtain the results. Openquery sends the query to the remote server, executes it in the remote server and presents the result for processing on the local server. Note that this allows one to perform joins / filters with the local table after the results have been returned by the remote server using OpenQuery function. Ex :


SELECT *
FROM   Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
')
WHERE  remote_server_edition = Serverproperty('Edition') 


The above query prints the result if and only if the local and remote server's SQL Editions were the same. On the queries above we have used additional quotations on serverproperty function as we have to specify quotation with in a quotation.

Also,one can also store the result into a temp/physical table or in a variable. Sample query for storing in temp table

INSERT INTO #temp
SELECT *
FROM   Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
') 


Sample query for storing the remote server's date in a variable

DECLARE @dt DATETIME

SELECT @dt = dt
FROM   Openquery([10.13.221.55], 'Select getdate() as dt ')

PRINT @dt 

So,Openquery provides easy way to access functions esp the system ones like getdate(),db_name(),Serverproperty,DMFs which can't be accessed directly using linked servers.