IIS Worker (30%) and SQL Server (60%) High CPU usage.
Currently hosting a website on iis (.net core 3.1). I have no clue as to why usage for both the iis worker and sql server is so high. I have a firewall setup and both network traffic from firewall and iis logs do not suggest ddos, as there seems to only be at least 8-10 simultaneous connections from both the same and distinct ip under 2-3 secs with 15mbps bandwidth but the size of the page is miniscule, under 10 kb so it shouldn't matter (i think?). EFCore is mainly being utilized for most app-db interactions and also a bit of ado.net for a few legacy stuff and i made sure the the connections are being disposed of properly with ado.net connection utilizing
using statements and im pretty sure efcore manages the connection on its own. I also make sure to apply all queryables to my efcore get queries before enumerating them so im not retrieving the entire table, and the queries aren't that complex either with just simple date comparisons and pk look ups. Though, I indexed the concerned columns manually via sql server instead of efcore migrations but i think that shouldn't have much of a difference with regards to performance. I also tried the scripts found in https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues but results indicate that my queries only take 3% of cpu usage, so something else is causing it. I've checked and no other jobs are running, there is one other database that has replication setup but it's setup in a "replicate on command" way (i forgot the proper term) so it shouldn't affect at all. CPU is an i7-7800 3.2 ghz and ram is 40gb.Troubleshoot high-CPU-usage issues in SQL Server - SQL Server
This article provides a procedure to help you fix high-CPU-usage issues on a server that is running SQL Server.
6 Replies
Do you have any common orm issues, select n+1 etc?
Orms can send massive queries as well; I've seen well intentioned queries that are a quarter megabyte in size.
Many folks over select as well, asking for vastly more data than they need, or pulling vast amounts of data that should just be aggregation on sql.
Sometimes you end up with queries that are completely disgusting with massive
cross applies across many thousands of rows.i don't see how select n+1 could manifest in the type of queries that are currently being run, most if not all are pk lookups and are not nested lookups, and largest result set so far is over 18 rows for a single query. Is this visible via the script found in this link https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-high-cpu-usage-issues ? the main query that's popping off in the diagnostic script's result set is this though,
SELECT CASE WHEN EXISTS( SELECT 1 FROM dbo.Table WHERE Id = @Id ) THEN CAST(1 as bit) ELSE CAST(0 as bit) END . and i can only surmise that it's the result of an orm operation because of how the command text was written. Problem is i don't really have a query that explicitly does this, so i can't tell which endpoint exactly is firing it.Troubleshoot high-CPU-usage issues in SQL Server - SQL Server
This article provides a procedure to help you fix high-CPU-usage issues on a server that is running SQL Server.
Ef emits queries to logs
Overview of logging and interception - EF Core
Overview of logging, events, interceptors, and diagnostics for EF Core
And a trace on the query ( performance expensive ) can help you trace the source
I'm sorta surprised efcore emits
select 1 toothank you so much for this lol, i think i found what was spiking usage. i just kept spamming f12 till i got to a piece of code that just kept generating an 8 character unique random string but the code's been running long enough with enough users that most of the strings have been used so it just started looping till it found one of the few strings that havent been accomodated yet. just increased the string length to 12 and the cpu usage went from 98% peak to 5% lmao.