July 2, 2009 at 10:25 am
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?
July 2, 2009 at 12:58 pm
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
July 3, 2009 at 9:41 am
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.
July 4, 2009 at 11:46 am
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
July 4, 2009 at 7:03 pm
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
Change is inevitable... Change for the better is not.
July 6, 2009 at 10:42 am
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.
July 6, 2009 at 9:11 pm
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
July 7, 2009 at 2:45 am
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
SQLkiwi blog
@SQL_Kiwi
July 7, 2009 at 8:34 am
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. 😀
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. :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.
July 8, 2009 at 6:33 am
>>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 on googles mail service
July 8, 2009 at 8:07 am
Are you sure it is the % Privileged Time >90%? That means CPU is busy in kennel mode, e.g. using operating system services, processing IOs, Paging Memories to disk, etc.
What is the % User time?
January 28, 2013 at 2:15 am
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/942fe3d4-141a-486a-afdf-53f079d3a4d3
This is an issue that is new in SQL Server 2005, and is only exposed under specific conditions – namely,
the use of the x64 version of SQL Server 2005 and the execution of large ad hoc query batches. Unfortunately, the memory that is used to store the execution plans for those ad hoc queries is not trimmed aggressively enough in the x64 version, and as a result we see more and more connections having to wait for memory allocations before they can continue. If any connection is suspended for more than 30 seconds, the application terminates the connection by reporting that the command currently executing has timed out.
This issue has been previously identified and is fixed in the upcoming service pack release (SP2) for SQL Server 2005, the Community Technology Preview (CTP) release of which is available today, and is confirmed to fix the issue in your environment.
Possible Solutions:
1. Sp2
2. create a job that manually frees the memory that is being consumed by query plans through the use of the
DBCC FREEPROCCACHE command. The documentation for this command states that “freeing the procedure cache causes,
for example, an ad hoc SQL statement to be recompiled instead of reused from the cache.”
(http://msdn2.microsoft.com/en-us/library/ms174283.aspx)
We have not tried out the second solution so far.
3.Change the Max Degree of Parallelism argument at the server/instance level, to something less than # or processors.
January 28, 2013 at 2:16 am
Please note: 4 year old thread.
Also, ad-hoc queries and plan cache has nothing to do with sleeping connections. Sleeping connections are ones waiting for input from the application, they are doing nothing.
Max degree of parallelism has nothing to do with sleeping connections, nor with plan memory
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply