CPU Utilisation

 --CPU Utilisation

 

       SET NOCOUNT ON

       DECLARE @ts_now bigint

       DECLARE @enable Varchar (60);

       DECLARE @AvgCPUUtilization DECIMAL(10,2)

 

       SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info

 

       -- load the CPU utilization in the past 10 minutes into the temp table, you can load them into a permanent table

       SELECT TOP(10) SQLProcessUtilization AS [SQLServerProcessCPUUtilization]

       ,SystemIdle AS [SystemIdleProcess]

       ,100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPU Utilization]

       ,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime]

       INTO #CPUUtilization

       FROM (

                     SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,

                            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')

                           AS [SystemIdle],

                            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',

                           'int')

                           AS [SQLProcessUtilization], [timestamp]

                     FROM (

                           SELECT [timestamp], CONVERT(xml, record) AS [record]

                           FROM sys.dm_os_ring_buffers

                           WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

                           AND record LIKE '%<SystemHealth>%') AS x

                     ) AS y

       ORDER BY record_id DESC

       --select * from #CPUUtilization

      

 

 

--> STEP 2 :

 

       DECLARE @xp_msver TABLE

       (

              [idx] [int] NULL

              ,[c_name] [varchar](100) NULL

              ,[int_val] [float] NULL

              ,[c_val] [varchar](128) NULL

       )

 

       INSERT INTO @xp_msver

       EXEC ('[master]..[xp_msver]');;

 

       WITH [ProcessorInfo] AS

       (

              SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]

                     ,CASE

                           WHEN hyperthread_ratio = cpu_count

                                  THEN cpu_count

                           ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))

                           END AS [number_of_cores_per_cpu]

                     ,CASE

                           WHEN hyperthread_ratio = cpu_count

                                  THEN cpu_count

                           ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))

                           END AS [total_number_of_cores]

                     ,[cpu_count] AS [number_of_virtual_cpus]

                     ,(

                           SELECT [c_val]

                           FROM @xp_msver

                           WHERE [c_name] = 'Platform'

                           ) AS [cpu_category]

              FROM [sys].[dm_os_sys_info]

       ),

       [ProcessorInfo1] as

       (

              SELECT @@SERVERNAME servername,[number_of_physical_cpus]

                     ,[number_of_cores_per_cpu]

                     ,[total_number_of_cores]

                     ,[number_of_virtual_cpus]

                     ,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category]

              FROM [ProcessorInfo]

       ),

       CPUUtilization as

       (

              select @@SERVERNAME [ServerName],avg(SQLServerProcessCPUUtilization)[AVG_Utilization],

              case

                     when avg(SQLServerProcessCPUUtilization) > = 80 then 'CPU Utilisation is High'

                     else 'CPU Utilisation looks Good'

              end[Status]

              from #CPUUtilization

       )

 

       select A.servername, number_of_physical_cpus,   number_of_cores_per_cpu,       total_number_of_cores,     number_of_virtual_cpus,       cpu_category,B.[AVG_Utilization],B.[Status]

       from [ProcessorInfo1] a

       left join CPUUtilization b on a.servername = b.ServerName

 

--> STEP 3 :  DROP TABLE

 

       DROP TABLE #CPUUtilization