SQL 2016 Perofmance issue

Question

Hi,
We are facing perforance issue with one of SQL 2016 (AlawyasOn with 1 secondary (AsyncMode).
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

We opened a SEV A case with MS and working from past 48 Hrs. Still no relief. MS engineer telling based on Pssdiag it's application issue, but our DEV tem telling no issue from Application side (since they didn't changed anyting).

We don't know what to do really we are struck.

Issue: getting lot of connection from applicaiton (observing lot of wait's and blocking on tables) those are going away. But it intermittent.

Answer 1

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.

That's certainly an error message that points in the direction of the application. The pattern is that the application opens connections, but not does not close them properly. Eventually, there are no connections left in the pool, and connection fails.

Now, you also mentioned blocking, and true, there can be a situation where processes comes in and get blocked, and therefore never disconnect. This will eventually lead to this error above. And in that case, the message as such means nothing.

But there is also an interesting variation of this. The proper pattern of opening a connection is do it inside of a using block. This guarantees that the connection is returned to the pool when the using block is exited. On the other hand, this does not happen if the application simply does:

SqlConnection cn = new SqlConnection(...);
   cn.Open();
   // Do stuff here
   cn.Close();

In this case, the connection remains open until the connection object is garbage-collected. Now, depending on the load, this may be sufficient, but if the load on the application increases over time, it may come to a point where garbage collection does not kick in often enough.

Please note that I am not saying that this is the actual root cause to your problem. Rather it is just a speculation. You have shared close to zero amount of information, and there is no way we can give you a confident answer of what is going on. At a minimum I would need to see the output of beta_lockinfo when you have this condition.

Answer 2

Hi @Vijay Kumar ,

Welcome to Microsoft Q&A!
Please refer to this MS doc: Understand and resolve SQL Server blocking problems
And
https://www.mssqltips.com/sqlservertip/2927/identify-the-cause-of-sql-server-blocking/
https://www.sqlshack.com/monitoring-sql-server-blocking-problems/
Hope these could give you some ideas to find which processes has been blocked.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

If a support engineer has been working with this for 48 hours without finding any resolution, you may realise that the chances the people with no access your system would be able to tell you where the problem is are minimal. Not the least when the amount of information you share is scant.

I am tempted to say that the root cause here is about processes, and then I don't mean processes in SQL Server, but processes in a workplace. This sounds like a clear example of why the DevOps paradigm has become popular and that your site has yet to embrace it. With DevOps, the application team had been actively in operating the system. Now, they are just shrugging their shoulders and saying "we didn't change anything".

And maybe they didn't. (Then again, my experience is that when people say that, they have actually changed something.) However, it is not uncommon that the query plan for a query changes. As more data is added to the system and the profile of the data changes, the statistics change and the optimizer may make new decisions. Sometimes to the better, sometimes to something a lot worse.

And this may have happened here. Although the information is very scant, this very much looks like an application issue to me. Exactly how much is hard to say. It may be a case of a poorly written query and/or poor indexing, where you with some luck got away with it. Or it may be a reasonably well-written query where you have some really bad luck.

But it could also be that usage of the application has changed, and this reveals design mistakes.

If you have not enabled Query Store for this database, do so. Query Store is a great tool to find poorly performing queries. It can also help you to find queries that have regressed in performance, and as a temporary measure you can force an earlier plan when a query certainly starts to perform badly.

To analyse looking you can use my beta_lockinfo. It's freely downloadable at no cost and permits you look at the blocking chains and you can also see the current activity of the processes.

Answer 4

since they didn't changed anyting

Ohhhh.... How much I hate this claim

Should I start with stories about clients which "didn't changed anything" and yet after spending hours or even days in some cases I found that the client made a change which directly led to the issue?

One time for example, the CEO which was also the owner of the company contact me on the weekend to get support since they "didn't changed anything" but the application stop working (again). This person always claim that he is an expert with more than 20 years experience (the question is what was he doing during these 20 years). The bottom-line is that after a few hours I found that he change one data type of a column in one of the tables and this led to the issue directly. It turned-out that he showed the application to a his client and since he wanted to show that understand something that he showed him that he can change the type to a smaller one in order to gain performance :-)

Like any consultant, I have tens of stories about people that "didn't changed anything" but something stopped working well.... This is the dark magic of computers :-)

back to your case...

Issue: getting lot of connection from applicaiton (observing lot of wait's and blocking on tables) those are going away. But it intermittent.

You gave us zero technical information to work with. We cannot read mind or test your server/machine and you only gave us a general story about "I have an issue".

If you want our help, then we need that your IT and DBA come here and provide technical information including relevant errors, monitoring information from the SQL Server about waits and locks and opening of connections tot he server, and monitoring information from the application about number of users, code of the application, and more...

Answer 5

This is the error we observing in application server (IIS) (Logs)

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.