MSSQL multi user database with Trusted Connections and AD auth - Connection Timeouts
1
vote
0
answers
129
views
at our company we run a MS SQL Server that hosts around 70 databases for various software products that are used by our colleagues.
The server is version 2016, 8 cores, 127 GB Ram, lots of disk space. Mostly we use trustedConnctions (AD/Kerberos) to the databases. Usage of the databases varies widely. Some are only for logging, others are heavily queried for reading and writing.
One of the (third party) software packages, that is used by around 150 users at peak time, connects to 21 databases simultaneously.
From time to we see massive problems in the logs of this software, because it takes eight to 12 seconds to establish each database connection. Sometimes there are even timeouts for the connection.
In the Database logs we can't find any clue what could cause this delays.
The CPU at the server works around 50% most of the time. The disks are not stressed.
Total connections to the server fluctuate between 700 and 900.
We are not particularly fond in using the sql-server-profiler or interpreting the logs.
What could we do to search for the source of the problem?
Which indicators should we monitor?
Could there be other factors, outside of the sql server, that we should check?
I would be grateful for any help!
**Update:**
the suggestions helped a lot.
I read through the links and tried to understand the different wait-types.
On script from sqlskills got me this statisic:
The biggest chunk is allways the Preemtive_OS_AuthenticationOPS.
Internet says:
> PREEMPTIVE_OS_AUTHENTICATIONOPS Description This wait accumulates
> while SQL Server is waiting for an Active Directory query to complete.
That got me thinking...
I changed the test environment of the big software suite from trustedconnection (ActiveDirectory) to SQLServer-User authentication.
The results are phenomenal: Nearly no wait times for the database connections!! Massive speed increase within the software.
Sadly that is not a possible solution for our production environment, but now i will shift my focus to the Kerberos/SQL Server interactions.
Any tips on that?
**A little update** for users that stumble over this question:
The infrastructure colleagues updated a TrendMicro agent on the sql server, that was several versions behind.
From that moment on, things ran nearly smoothly. If that was the underlying cause, or also only a symptom of something else... maybe we will never know....

Asked by SteLoe
(362 rep)
Aug 7, 2024, 12:21 PM
Last activity: Aug 20, 2024, 01:59 PM
Last activity: Aug 20, 2024, 01:59 PM