SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can lots of User Connections cause high CPU use


Can lots of User Connections cause high CPU use

Author
Message
FNS
FNS
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 584
If I use Activity Monitor I see 1400+ connections. Only 10 are "Runnable" the rest are sleeping. Meanwhile the CPU is getting raped.

Is this just connection pooling leaving connections open, lying around so they're ready to go when the next wave of commands come in?
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23716 Visits: 5314
And you are sure that the sql server exe is using all the processor?

Depending on your server configuration and hardware, it is possible for even 1-2 connections to nearly monopolize a server.

Each connection takes mempory, so if your server is memory starved I could see performance issues but it would likely be disk thrashing..

Hope that helps.

CEWII
FNS
FNS
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 584
Yes I'm sure it's SQL taking all the CPU.

It's not the disks, least not what I can see from the metrics. Disk Q and Disk Busy % are all nicely low.

I was wondering whether an increase in users connecting up (as seen in Activity Monitor) can tip a CPU from busy to redlined.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23716 Visits: 5314
I can't substantiate it but I think it is possible.. I'm guessing though that you have a CPU bottleneck for 1 or more processes..

CEWII
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212833 Visits: 41977
A single poorly written stored procedure, view, or UDF can bring a server to it's knees. I'd recommend running SQL Profiler and find out which one it is so you can fix it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
FNS
FNS
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 584
It seems to be that as soon as the number of connected users jumps up beyond a certain level the machine sags to its knees. Having said that, the CPU is always way, way too high.

Is there any official documentation which says amount of RAM per user connections? Or CPU power needed per connection? What I'm trying to establish is whether the below is a lot for a 3Gb dual CPU system or not:

10 runnable (green), 1370 sleeping (red), 15 dormant (blue) connections in activity monitor.

If it is down to poor store procs, ad-hoc queries, cursors and the like then fine, I'll keep digging.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23716 Visits: 5314
In the olden days (sql 6.5) I believe it used 37K/connection, I believe the new number is high 40K/low 50K. Using that number you would be using 55MB for the user connections. I used to have a SQL 2000 box that had a lot of old connections, so I built a script that ran every morning that killed every process with a SPID over 50 that the last batch was over 24 hours. I don't know if this will do anything for you..

But you said 3GB, how much is SQL allowed, MIN/MAX. I'm wondering if you have SQL set too high or low.. With a 3GB machine I would generally save at LEAST 256MB for the OS alone. Others may suggest more.. But thats me.. If you are going to use other tools like SSAS, SSRS, or SSIS you might need need to take SQL down even further for max memory..

CEWII
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35196 Visits: 11359
For SQL2K5, memory usage per connection is roughly (3 * network_packet_size + 94) KB. So for a 4096 byte network packet size (the default), that's around 106KB per connection, more if MARS is used.

See Memory Used by SQL Server Objects Specifications

But, first things first. (It really doesn't sound at all likely that memory is your problem).

If there are anything like ten runnable worker threads over a period of time on that dual-core server, that's almost certainly the place to start. See Monitoring CPU Usage for the counters to monitor. You can also query DMVs like sys.dm_os_schedulers, but a performance graph is probably going to be easier to work with in this case.

Often, very high CPU usage is due to high levels of parallelism caused by a lack a good indexes. Once you have confirmed the CPU bottleneck, follow Jeff's advice and run Profiler looking for high-CPU operations. Normally some good gains can be achieved just by tuning the top 5 or 10 CPU-using objects.

You may also want to consider reducing parallelism - either by increasing the cost for parallelism from the default (and way way too low) value of 5; or by turning off parallelism at the server level and just enabling it for individual queries using the MAXDOP query hint. Of course it may be simply that the server simply requires more horsepower. If you have the option to transfer to a machine with more/faster cores that might be worth considering before doing the tuning. The relative priority depends entirely on your business.

When your CPU problems are sorted, I would encourage you to become familiar with the wealth of information available from the dynamic management views (DMVs). You can find some good examples for costly queries here.

Finally, probably the best all-round performance guide for 2005 is on TechNet. You could do a lot worse than follow the advice given there when troubleshooting your current problem.

Fix the CPU issue first!

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
FNS
FNS
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 584
Firstly a thanks to you all for your suggestions and pointers. :-)

I've been studying the DMV stats and tracking through the excellent MS documents on this topic (trying to jam too much info in my head!) Crazy but I'll go through these links too to make sure I'm not missing anything easy. How I wish there was a sp_configure GO_MUCH_FASTER 1 setting to play with. :-D
I wanted to check I'm not fighting the impossible with the number of user connections. If there was a nice MS table which said for 1000+ connections you need 16Gb of RAM or something then I could just forward that to the boss and my job would be done. Cool

The runnable_tasks_count is always well into double figures and the Processor: % Privileged Time is firmly planted into the 90%+ mark. Ermm I'm surprised I've not melted the CPU cores yet.

Indexes have been and are under review and I'll continue to try and target the highest CPU offenders first but I've gone through them several times so I don't think I'll suddenly be able to hit 10 store procs and watch with amazement as the CPU drops to sub 50%. Nice to think it would though.

Parallelism: Hmmm. Not thought of that: yes it's set to the default. I'll add this to the list of "to-do" in fixing this.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31907 Visits: 8671
>>How I wish there was a sp_configure GO_MUCH_FASTER 1 setting to play with.

This configuration exists - and is known as hiring TheSQLGuru to give your system a performance review. :-D

Seriously though, hiring a tuning professional to get your system whooped into shape (while mentoring you on how to do this in the future) can be an incredibly rewarding exercise both for the company and yourself. There are a kajillion things at play here that a good tuner already knows - things like checking for excessive parallelization and indexing opportunities which you professed to not thinking of. You can hunt and peck on this forum for months and not get one tenth the way to optimizing your system that a consultant could do in a week (or even a few days if the expected few magic bullets are uncovered).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search