February 5, 2020 at 3:29 am
Hi guys, need some advice on what sort of troubleshooting can I do in event of the DB server CPU went up to 100% and causes all the processing got slowed down. Upon checking I found that there's about 4000+ idle sessions on the DB when on normal days the max it reaches is only about 800+ sessions.
To resolve the problem fast I went and reboot the DB instance with the option of forcefully terminate all the connection. Once that's done everything returns to normal, although CPU remain high no users complain about it.
How do I troubleshoot on why there's a sudden increase of sessions to the DB?
February 5, 2020 at 9:15 am
I would be looking for blocking chains first. That's the most likely scenario. A single query is blocking all the other queries which are waiting on it to complete. There are a bunch of ways to get this done, but to get started, look at this article. After that, it's a good idea to know how to see which query is consuming the most resources, which are called most frequently, etc. This means learning how to use the Dynamic Management Views (DMVs), Extended Events, and Query Store. A shortcut for the DMVs is to get a copy of sp_whoisactive (open source). You should still learn how to use the DMVs, but sp_whoisactive will help ramp you up quickly. If you're on 2012, you won't have Query Store (it's 2016+). You will have Extended Events. They're for capturing detailed behavior of your queries. There's a Stairways series up above to help get started with them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2020 at 12:44 pm
HI,
Idle sessions will never consume your CPU resource. To start investigating, you can use sys.dm_exec_request OR sys.sysprocesses DMVs to check for sessions that are actively running at that instance. These 2 DMVs can give you information related to cpu worker time,session and the request elapsed time.
Further, you can also check at the locks which are placed on a particular database with help of sys.dm_tran_locks DMV.
These information will be more than enough to figure out the troublesome session and its request.
February 5, 2020 at 2:09 pm
Check to see if the connections being made are using M.A.R.S (Multiple Active Result Sets). Connections are supposed to default to this being off but several of us have found that things like Entity Framework explicitly turn M.A.R.S. on.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply