Solved

DB memory and Max Pool Size

Posted on 2014-12-19
6
169 Views
Last Modified: 2014-12-22
How can I check if my DB has enough memory to handle the extra connections for a Max pool size of 200?

That's the short question. Here's the explanation as to why:

I have a sql server 2012 database. I am getting the error:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

All of the connections are closed immediately, I use con.close or Using Con.

I checked and there were 220 people using the site. And I ran an sp_who after the problem was reported (and had gone away) and there were only 54 items. Not exactly sure how running that tells me anything, but the forum post said to run it.

So I'm thinking maybe I need to up the Max Pool Size=200; in my connection string. However, it says to make sure your DB has enough memory to handle the extra connections.

How would I know that? How can I check if my DB has enough memory to handle the extra connections?

thanks!
0
Comment
Question by:BobCSD
  • 3
  • 2
6 Comments
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 40510270
If your objective is to guarantee SQL Server will not overload the CPU an memory buffer pool, what you should do is seriously consider using MARS instead of expanding memory randomly.  

This is the right way to guarantee resources get consumed economically.  Simple and efficient.  If that is possible that you won't have to add any additional memory and it will work better and faster.  For more info on MARS please read the following article...

http://msdn.microsoft.com/fr-fr/library/ms131686.aspx
http://technet.microsoft.com/en-us/library/ms345109(v=sql.90).aspx

Hope this helps...
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40512879
Why are you pointing to a SQL Server issue?
Can you provide more information?
Where are you getting the error? What kind of operation are you trying to perform? Which kind of object are you using to connect to the database?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40512972
<<Why are you pointing to a SQL Server issue?>>
Exactly.  

It is reasonable to assume this could be a client issue and this is why I suggested using MARS (if possible).  I found this  simple change highly effective in most cases of connections pools related issues.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:BobCSD
ID: 40513417
My original question was:
How can I check if my DB has enough memory to handle the extra connections?

I thought perhaps someone would tell me how I can know if going to 200 pool size, what kind of memory is required.

I have 15 GB memory on this server, so I'm just going to assume that will be enough, since no one seems to know. I have to get this done now.

thanks for your suggestions, I'm closing this out.
0
 
LVL 1

Author Closing Comment

by:BobCSD
ID: 40513425
Thanks. I have no intention of using MARS at this point, but maybe down the road it will be useful. I looked at the page and it will take some major changes to implement and that wasn't my original question.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40513514
<<I thought perhaps someone would tell me how I can know if going to 200 pool size, what kind of memory is required.>>
Your question is based on the assumption that the resources consumed on SQL Server depend only on the number of connections : this assumption is false.  

The number of connections plays one part but what plays on the SQL Side is the size of each individual connection has to mobilize in SQL Server memory on average.  You may have 10 connections requiring a cache of 45GB and 2000 connections requiring 50MB.  So there is no way to answer this straight away.  The only way to bring up an answer your situation is to either evaluate the average of cache that has to be maintained to sustain your client pool to evaluate your server pool OR to proactively reduce the footprint and hope for the best.  Which is why I suggested MARS as the simplest way to get your mind to ease.  If you can not do that then you will have to evaluate how much data should be requested each sec on average from your client then evaluate how much RAM is required on the data cache to sustain all the data required.

Another way to say this is that the client pool size does say anything about the server pool only when you know how much each connection requires on average.  Sorry I could not tell you more but there is not much more to say given the information you gave us.

Good luck for the rest of the sizing and hope for the best.  Let us know if we can help further on this.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now