0

We have a stored procedure which takes around 3-4 secs to execute as it has many joins and unions. But if we try to execute that stored procedure using SQL Stress with 100 threads, it gives many time out exceptions. The message is..

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached .

This is on a production server.

If we execute the same thing using SQL Stress in our local environment it does not give any exception.

It might be an SQL Server Configuration issue. It shows 5031 MB used in the task manager max and does not go beyond that, however the System memory is 64 GB.

What steps should I take to investigate further?

  • I suggest you focus on CPU rather than memory. How many cores? If each instance of a single-threaded query takes 3-4 seconds of CPU time, then over 5 minutes of processor time will be needed to execute the proc 100 times concurrently. Divide that time by the number of available cores for the best-case scenario. – Dan Guzman Jan 7 '19 at 11:11
  • its 6 cores and 12 logical processors. at the time of execution it takes 100% of cpu usage till the time it completes.. while locally with the same configuration it takes 100% cpu for some 10-15 secs and then dropped under 10-15%. – Rishi Jan 7 '19 at 12:14
1

The timeout is with the connection pool, which is a collection of connections maintained by the client. All of the connections in the pool are in use, and it is timing out getting a connection object from the connection pool, not timing out trying to connect to the SQL Server. For example, you may have 30 connections in the connection pool, and a 5 second timeout for obtaining a connection from the pool. A connection pool timeout will occur if all 30 connections in the pool are busy and none are freed up within 5 seconds of a request for a connection pool object.

So you can increase the number of connections in the pool, increase the connection pool timeout, or work to see if the stored procedure execution time can be improved. It is likely that all of the connections are busy due to blocking, and if that's the case, increasing the connections in the pool and the timeout won't increase the number of times the stored procedure can execute per minute and you'll need to find out how to make the stored procedure execute faster. So a good first step would be to look at the execution plans and missing indexes for all of the queries in the stored procedure.

Perhaps obvious, but since you state it works better on the "local" server, what are the configuration differences between the two servers? If read committed snapshot is enabled on the local server but not the other, that one config change could have a huge impact.

| improve this answer | |
  • While it's possible that all the pooled connections are actually busy for the duration of the timeout, it's much more likely that the application is failing to Close/Dispose connections, and they are lingering on the heap not closed. – David Browne - Microsoft Jan 8 '19 at 15:18
0

Check your memory settings on the production SQL Server. It may not be an issue or it may be limiting you. Guidance I use from a sql tuning expert is this:

Reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

Using this formula, assuming it is the only instance and the server is dedicated to running SQL Server, you would consider setting sql server to 54GB max memory.

This query is taking 3-4 seconds to complete. It may be going multi core. You can tell, if viewing the execution plan, you see yellow arrows on some of the steps and then a step merging the parallelization. If it is going parallel, then it is actually using 6 cores, you may be 18+ seconds of CPU time. Parallelization can mask issues like this until you get load.

As a commentor said, attempting to run this 100 times concurrently is not going to work and you will run into problems with your application timing out and possibly with sql server becoming overwhelmed and not responding. I've had queries like this cause problems just being run a couple of times per second, causing load on the server and internal blocking in the database.

Put this at the beginning of you query to see execution times and physical IO to determine what might be the issue. It will display CPU time used and IO numbers. Reviewing those will let you know if it is an IO issue or CPU issue.

SET STATISTICS IO ON

SET STATISTICS TIME ON

| improve this answer | |
  • Whenever i restart the service (SQL Service) It works fine then when it goes to 7-8 GB it stuck over there and then the performance issues can be observed. I have a concern that why its not going beyond that 7-8 GB when the maximum memory specified for the SQL is 54 GB. – Rishi Apr 23 '19 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.