CPU troubleshooting with Perfom counters
-
I have put 2 PERFMON counters to troubleshoot high CPU utilization due to SQL server 2008 on a windows host .
I see % processor time (red )is around 100 % constantly whereas %privileged time (green) varies and sometimes goes 100 % as well . Can I conclude something from this ?
-
Post more of perfmon please, including scale and in/max/average
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
-
many a times %Privilieged time is more than 30% and as per an article from Pinal Dave - https://logicalread.com/troubleshoot-high-cpu-sql-server-pd01/#.WVat4y_rtiA
"This can be seen via Performance Monitor by monitoring the “% Privileged Time” and “% User Time” counters under the “Process” node. If “% Privileged Time” value is more than 30%, it’s generally caused by faulty system drivers or antivirus software. In such situations, make sure the BIOS and filter drivers are up-to-date, and then try disabling the antivirus software temporarily to see the change."
Can i conclude something like this ? -
How many CPUs does that server have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
4 processors 2.6 ghz ... 16gb ram ... its a mini system hosting only one db 🙂
-
-
One more try.
Can you post the perfmon showing the max, min and avg of the privileged time?
Also, what process is that?CPU usage is definitely too high, but impossible to debug why with no information.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Query :
WITH DBIO AS
(SELECT DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read +IVFS.num_of_bytes_written)AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
INNER JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.[type])
SELECT db, file_type,
CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
CAST(100. * io_stall / SUM(io_stall) OVER()
AS DECIMAL(10, 2)) AS io_stall_pct,
ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
FROM DBIO
Output :
our database tempdb IO_stall_pct is more than 80 % for my instance ...
what all to check as I don't see blockings , tempdb data and log file usage issue ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply