SQL 2008 server CPU gets 100% if it goes above 300 sql connections

SQL 2008 server CPU gets 100% if it goes above 300 sql connections.

Usually we running about 180-200 SQL connections without issues at 20% CPU.    Occasionally it reaches 300-500 sql connections and CPU just pegs to 100%.
So far looks like legit traffic, all legit connections on sql from our Webservers.

Any suggestions what to look at on SQL?    It should definitely handle that many connections, its not really that many of them, so if with 200 connections running at 20% with 400 should be running around 40% im thinking.

Any suggestions greatly appreciated,
itmtiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
How many Cores/Memory?
Are you not using pooled connections?
0
itmtiAuthor Commented:
Dual Proc with 4 cores, so total 8 cores.

for pooled connections, not even sure where that would be set.
0
itmtiAuthor Commented:
Again, 300-400 or 500 sql connections are not that much, at least we think so.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
for pooled connections, not even sure where that would be set.
It is set by default when using a .NET application, but it can be disabled.

How much memory do you have?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the server configuration? Specially for architechture (32bit or 64bit) and memory?
0
itmtiAuthor Commented:
64-bit
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you have a MSSQL 2008 on a 8 core server, 64bit architecture. The OS is Windows 2008R2?
And you didn't tell us nothing about the memory. How much RAM the server has and how much is configured for SQL Server (min and max memories)?
0
itmtiAuthor Commented:
Windows 2008R2
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) .
Total 64GB memory (sql configured to use up to 54GB)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. So you let 10GB free for OS.
That's a SQL Server dedicated box? No SSAS, SSIS or SSRS installed in the same box?
And there's only one MSSQL instance, right?

What DBCC MEMORYSTATUS returns?
0
itmtiAuthor Commented:
Yes SQL dedicated box.
Single MSSQL Instance.

DBCC Memory lots of output, not sure what you want to see, but here are few returns:

VM Reserved      73157284
VM Committed      56330936
Locked Pages Allocated      0
Reserved Memory      1024
Reserved Memory In Use      0
      
VM Reserved      73153636
VM Committed      56327400
Locked Pages Allocated      0
MultiPage Allocator      379880
SinglePage Allocator      2362688
      
VM Reserved      2560
VM Committed      2504
Locked Pages Allocated      0
MultiPage Allocator      2416
SinglePage Allocator      2362688
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry for not beig more accurate.
Was thinking more in Query Memory Objects sections.
Or if you want, run the command again and send the result to a text file and post the file here.
0
itmtiAuthor Commented:
Having hard time figuring out how to save all to text, seems to save only in sections.

Query Memory Objects sections
Grants      1
Waiting      0
Available      4676708
Current Max      5120905
Future Max      5120905
Physical Max      5120905
Next Request      0
Waiting For      0
Cost      0
Timeout      0
Wait Time      0
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
JHaving hard time figuring out how to save all to text, seems to save only in sections.
Go to Query menu, then Results To and finally chose Results To File. When you run the command it will request you to give a filename and location.

From your post the results shows no bottlenecks in memory but that should only happen when you reach those 300 connections. Do you know when that will be? You should run again this command when you see that CPU is already around 100%.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itmtiAuthor Commented:
Thanks for help,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.