Something like a starting point:

SET ANSI_NULLS OFF -- this is to allow WHERE value = NULL type of syntax in case @inst_index is NULL
IF ((OBJECT_ID ('dbo.tbl_ServerProperties') IS NOT NULL) AND (OBJECT_ID ('dbo.CounterData') IS NOT NULL) )
BEGIN
	DECLARE @process_id INT = 0, @cpu_count int, @inst_name VARCHAR (64), @inst_index INT

	SELECT @process_id = PropertyValue 
	FROM tbl_ServerProperties sp
	WHERE sp.PropertyName = 'ProcessID'


	SELECT @cpu_count = CASE WHEN PropertyValue = 0 THEN 1 ELSE PropertyValue END
	FROM tbl_ServerProperties sp
	WHERE sp.PropertyName = 'cpu_count'
   
   
   
   --get processID of SQL assumes that the instance was not restarted during data collection and preserved its PID
	
	SELECT TOP 1 @inst_name = InstanceName, @inst_index = InstanceIndex
	FROM CounterData ctr JOIN CounterDetails cdet
	  ON ctr.CounterID = cdet.CounterID
	WHERE cdet.ObjectName = 'Process' 
	 AND cdet.CounterName LIKE 'ID Process'
	 AND cdet.InstanceName  like 'sqlservr%'
	 AND ctr.CounterValue = @process_id 

	--combine SQL and OS counter data into a data set by using a join 

	SELECT sql_cpu.CounterDateTime AS EventTime, 
	sql_cpu.RecordIndex AS record_id_sql,
	os_cpu.system_idle_cpu, 
	CASE WHEN sql_cpu.sql_cpu_utilization > os_cpu.total_cpu_utilization THEN os_cpu.total_cpu_utilization 
		ELSE sql_cpu.sql_cpu_utilization END AS sql_cpu_utilization, 
	total_cpu_utilization - (CASE WHEN sql_cpu.sql_cpu_utilization > os_cpu.total_cpu_utilization THEN os_cpu.total_cpu_utilization 
		ELSE sql_cpu.sql_cpu_utilization END ) AS nonsql_cpu_utilization 
	FROM
	  (
	  -- get SQL CPU for the imported instance
	  SELECT ctr.CounterDateTime, ctr.RecordIndex, CONVERT(INT, (floor(ctr.CounterValue )/ (100 * @cpu_count)) * 100) as sql_cpu_utilization, InstanceName, InstanceIndex
	  FROM CounterData ctr JOIN 
	  	CounterDetails det
	  ON ctr.CounterID = det.CounterID
	  WHERE det.ObjectName = 'Process' 
	  	AND det.CounterName LIKE '[%] Processor Time'
	  	AND det.InstanceName = @inst_name
	  	AND det.InstanceIndex  = @inst_index ) as sql_cpu

	INNER JOIN 

	  (SELECT ctr.CounterDateTime, ctr.RecordIndex, floor(ctr.CounterValue) as total_cpu_utilization, 100 - floor(ctr.CounterValue) as system_idle_cpu  
	  FROM CounterData ctr JOIN 
	    CounterDetails det
	  ON ctr.CounterID = det.CounterID
	  WHERE det.ObjectName = 'Processor Information' 
	    AND det.CounterName LIKE '[%] Processor Time'
		AND InstanceName = '_Total') as os_cpu

	  ON 
	  sql_cpu.RecordIndex = os_cpu.RecordIndex

END

Modify Bottleneck Analysis report to use Perfmon counters instead of ring buffer DMV #193

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions