Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Can lots of User Connections cause high CPU use Expand / Collapse
Author
Message
Posted Thursday, July 2, 2009 10:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 7:51 AM
Points: 105, Visits: 581
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?
Post #746441
Posted Thursday, July 2, 2009 12:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #746567
Posted Friday, July 3, 2009 9:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 7:51 AM
Points: 105, Visits: 581
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.
Post #747042
Posted Saturday, July 4, 2009 11:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #747239
Posted Saturday, July 4, 2009 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #747285
Posted Monday, July 6, 2009 10:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 7:51 AM
Points: 105, Visits: 581
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.
Post #747884
Posted Monday, July 6, 2009 9:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #748257
Posted Tuesday, July 7, 2009 2:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 11,194, Visits: 11,142
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #748372
Posted Tuesday, July 7, 2009 8:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2011 7:51 AM
Points: 105, Visits: 581
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!) 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.
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.

The runnable_tasks_count is always well into double figures and the Processor: % Privileged Time is firmly planted into the 90%+ mark. 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.
Post #748598
Posted Wednesday, July 8, 2009 6:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 4,350, Visits: 6,162
>>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.

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 at GMail
Post #749196
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse