July 16, 2024 at 4:10 pm
Hello everyone,
I've got a question when it comes to best practices about application sessions in SQL Server.
When your company developers write code and create applications to connect to SQL Server, and these sessions despite being connected to the SQL Instance for a few days ("connect time" from sys.dm_exec_sessions) is (2024-07-01 01:00:00) but the (last_request_start_time from sys.dm_exec_connections) is the same date (2024-07-01 08:00:00) and today is (2024-07-16 12:00:00), do these sessions take the CPU resources (CPU threads) and affect your systems availability?
So lets say, you have thousands of sessions running but a lot of them are absolutely doing nothing, do these sessions take resources that could be used for other sessions? do these sessions affect your SQL server stability?
what do you think of these sessions? do you kill them?
what are your best practices when it comes to sessions in SQL Server.
Best regards and thank you.
July 16, 2024 at 5:35 pm
No. If they're sleeping, and don't have any active tasks, they are not using up CPU. They are taking a small amount of memory.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 18, 2024 at 1:16 pm
Unless you have some kind of direct evidence that anything is causing a problem, leave it alone. Don't kill sessions without knowledge of there being an issue.
"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
July 18, 2024 at 9:29 pm
To play devil's advocate, all of the applications I develop I design with a "quick drop" to SQL. What I mean by quick drop is that the application opens a connection to SQL, does what is needed, then closes the connection. Not all applications do this mind you; some it makes more sense to have open for a short period of time because the user will do multiple actions before going idle. Basically, the application will try to guess how often it needs to open a connection and will close it as soon as it thinks it is done. MOST of the applications do a data pull, then can close the connection until the user needs a refresh of that data, a drill down into the data, or a change to the data. BUT the time between queries is often 10+ seconds so I close the connection between queries in most cases. There are exceptions where multiple queries will be called one right after another and those will keep the session active until everything is done, but I still try to close the connection as quickly as makes sense.
BUT to add to everyone saying "don't kill it" - that's good advice. There is RARELY a need to kill the sessions UNLESS the application doesn't handle it well (app crashes and leaves sessions open for example). There is ALMOST always issues with killing a session you don't know anything about - it COULD be an open transaction and you could trigger a rollback that could take FOREVER to complete for example.
IF the sessions are sleeping for long periods of time, I'd recommend reaching out to the app developer (if possible) to determine if it is expected or not. App crashes during a SQL query can cause stuck sessions, but even these I tend to leave alone UNLESS they are causing a problem (blocking). Never know the impact of killing it and the app may not handle it well if the session is terminated while a user is using the app (even in the background or just having it running for days without touching it).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 19, 2024 at 4:44 pm
I tend to follow's Brian's methodology ^^
July 23, 2024 at 7:42 pm
Thanks guys for taking your time to read this and your replies!
Got my doubts cleared out.
Have any of your SQL Instances run out of threads and "crashed" temporarily but then it came back online as if nothing happened?
Now, I'm assuming it happened due to not having enough threads cause that's what Microsoft support said, and even they thought this was weird due the lack of logs.
They couldn't pinpoint directly the issue specifically because the lack of logs.
Nothing on the SQL Server logs and you can only notice the issue because your transactions tables have breach of time in your transaction date time columns, like it wasn't inserting.
Nobody could connect to the instance, Tried accessing through the SQL DAC and only queried the sessions tables and there were the normal amount 3k-4k but then SSMS Query windows crashed, SSMS died and couldn't connect back because that session was still connected and there could only be one session, but then everything went back to normal.
July 25, 2024 at 8:11 pm
I haven't seen a server come back because of thread exhaustion, but that may have been the business (or my own) lack of patience to wait and more willing to reboot quickly when seeing the error.
July 25, 2024 at 10:04 pm
I wonder if it is impatience on Steve Jones's part OR a configuration thing? I can't remember if the SQL services are set to auto-restart on unexpected stop or not, but I had thought that by default if SQL crashed, it was down until someone starts it up again (person or automated tools/failover software).
It is really weird to have a "lack of logs" though. You should have access to the SQL logs and Windows logs, no? One of those should have some details into what went sideways...
When you say "SSMS died", were you running SSMS on the physical server or on your local machine? I am wondering if the server was being starved for resources (max memory on the instances set wrong (default) and then having someone actually RDP into the server and start stuff up and eat up the last little bits of memory the server had left). Mind you, the windows logs should tell you why SSMS crashed...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply