Close Menu

SQL-SERVER

 

Find CPU Usage in the last Hour


In the previous script's we have seen how to Find Backup History and restore history on a Sql-Server.This script is much related to perfomance monitring.there are many methods to findout CPU Usage in the previous point of time.however this script helps us in finding out Using TSQL This script uses various internal tables of the sql server instance and joining DMV's for the desired results.

we can use this script to identify if there is a query being run since an hour or any pirticular time comparing it with the amount of CPU usage. The obtained results help us in identifying perfomance bottleneck if at all they are related with the CPU usage. if there is lesser cpu usage while the query being run or the process being executed the we can conlude that something is slowing the query or the process whic is being executed on the sql-server

The image below is run on my personal computer and hence its howing very less numbers for the CPU usage of the sql server. however when you run the same over productionments/dev/testing environ we get different results.

Before You Proceed We recommend you to read our Disclaimer below

Please test the script on your testing or Dev (development) environment's which are expendable before executing or implementing these scripts over production or realtime environment. Also get the Aprovalfrom your DBA before executing these scripts

The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.

In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.

Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.

Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.




 DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);                                                            
SELECT TOP(60) SQLProcessUtilization AS [SQL Server Process CPU Utilization],                                                                  

                SystemIdle AS [System Idle Process],                                                               

               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],                                                                  
 
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]                                                                  
 
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 '%%') AS x                                           

         ) AS y                                                         

you can download the Script here here