InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 20 Nov 2009 23:45:14 GMT20http://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxAre 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?Wed, 08 Jul 2009 08:07:31 GMTpeterhehttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspx>>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).Wed, 08 Jul 2009 06:33:19 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxFirstly 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. :-DI 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.Tue, 07 Jul 2009 08:34:50 GMTFNShttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxFor 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 [u][url=http://msdn.microsoft.com/en-us/library/aa337559.aspx]Memory Used by SQL Server Objects Specifications[/url][/u]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 [u][url=http://msdn.microsoft.com/en-us/library/ms178072.aspx]Monitoring CPU Usage[/url][/u] 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 [u][url=http://blogs.msdn.com/sqltips/archive/2005/10/05/Top-N-costly-query-plans.aspx]here[/url][/u].Finally, probably the best all-round performance guide for 2005 is [u][url=http://technet.microsoft.com/en-us/library/cc966540.aspx]on TechNet[/url][/u]. You could do a lot worse than follow the advice given there when troubleshooting your current problem.Fix the CPU issue first!PaulTue, 07 Jul 2009 02:45:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxIn 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..CEWIIMon, 06 Jul 2009 21:11:05 GMTElliott Whttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxIt 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.Mon, 06 Jul 2009 10:42:09 GMTFNShttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxA 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.Sat, 04 Jul 2009 19:03:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxI 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..CEWIISat, 04 Jul 2009 11:46:13 GMTElliott Whttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxYes 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.Fri, 03 Jul 2009 09:41:00 GMTFNShttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxAnd 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.CEWIIThu, 02 Jul 2009 12:58:27 GMTElliott Whttp://www.sqlservercentral.com/Forums/Topic746441-360-1.aspxIf 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?Thu, 02 Jul 2009 10:25:30 GMTFNS