SQL Server Performance Forum – Threads Archive
SQL Server 2005 Hangs — 100% utiilization
I have a brand new IBM eSeries 306 server. Its a single processor (2ghz) system with 2 gb of RAM. I am the only user on the box and it hosts an app that I run for a handful of clients. This is a *fairly* controlled enviroment. From time to time, SQL 2005 server seems to run out of control. Out of control means 100% CPU utilization with the SQL Server app eating up somewhere between 850 mb and 1.1 gbyte of memory. The app hangs and clients complain. What’s weird is often does this as a result of a simple, nothing special, any-other-time-runs-in 10 secs query. I’ve also seen it happen when *no one* is on the box at all! My production machine is a Dell box running SQL Server 2000 with half the RAM and a slower processor…and I never have this problem. I’m more of a developer than a DBA so its possible that I made some weenie/newbie mistake during installation by choosing all of the defaults Any body got any ideas?Thanks! I really appreciate it. -dz tngresearch.com
10 sec is not a simple query
i would examine the execution plan for this query carefully, and consider whether it needs to be executed serially, ie, only one may be executed at any given time
quote:Originally posted by joechang
10 sec is not a simple query
i would examine the execution plan for this query carefully, and consider whether it needs to be executed serially, ie, only one may be executed at any given time
i would examine the execution plan for this query carefully, and consider whether it needs to be executed serially, ie, only one may be executed at any given time
But why would a ten sec query sometimes take ten seconds and other times five-10 minutes and then time out? Especially if there are no other jobs running? And why, under nearly identical circumstances (same query, same app, same data) would it run consistently at 10 secs on another box that has less memory and a slower processor? Is there something I should be doing to this new SQL Server 2005 installation to keep SQL Server from running away? tngresearch.com
It probably depends on the queries and what execution plans are used… Joe’s point was that a 10sec query warrants investigation since 10sec is at least 100fold more than expected for a ‘standard’ query So are you able to post the query, the indexes on the tables mentioned in this query and the differences in hardware between the servers? are either of them hyperthreaded? e.g. less memory could explain a difference if the query’s data fits in the cache of one, but causing a sub-optimal plan, where as it doesn’t fit into the other, so the sql optimiser knows that it needs to go back to disk to get the pages which may affect its choice of plan Cheers
Twan
quote:Originally posted by Twan
Joe’s point was that a 10sec query warrants investigation since 10sec is at least 100fold more than expected for a ‘standard’ query
Joe’s point was that a 10sec query warrants investigation since 10sec is at least 100fold more than expected for a ‘standard’ query
You guys are nailing me on semantics…and I am guilty as charged! No, its not a simple query. Its actually a rather lengthy and complicated sp which does some pretty heavy lifting. I was using "query" generically. And that was misleading. Sorry ’bout that! The point I was trying to make is that I have an apples-to-apples comparision where the only differences are the hardware the the version of SQL Server that I am running (2000 vs 2005).
quote:So are you able to post the query, the indexes on the tables mentioned in this query and the differences in hardware between the servers? are either of them hyperthreaded?
No hyperthreading to the best of my knowledge.
quote:
e.g. less memory could explain a difference if the query’s data fits in the cache of one, but causing a sub-optimal plan, where as it doesn’t fit into the other, so the sql optimiser knows that it needs to go back to disk to get the pages which may affect its choice of plan
e.g. less memory could explain a difference if the query’s data fits in the cache of one, but causing a sub-optimal plan, where as it doesn’t fit into the other, so the sql optimiser knows that it needs to go back to disk to get the pages which may affect its choice of plan
Hmmm. That’s an interesting theory. But it still leaves me confused! If this was the case, wouldn’t it always run much slower on the older, production box? And why does the box "run away" at times where no one is running anything. It seems like some of its route idle-cycle time processing sends the IBM server into this "hyperventilating" state, too! Joe & Twan thanks for your help. And your patience, too! ps: are there any performance measures I can capture to help you help me decipher what’s going on? I’m referring to the stuff that’s built into Windows Server 2003 found under "Administrative Tools" and "Performance."
tngresearch.com
Hi ya, if possible getting the execution plan of when things go bad would be ideal. My guess is that sql changes from something like a nested loop to a hash or merge join which can be detrimental. sql2k and sql2k3 is not really apples and apples, so there may well be different things which trip up the optimiser…? I’d say that the best bet is to look at optimising the query, and thinking about which parameter/variable values might cause a substantially different execution plan, or cause the number of rows from a single table to be vastly different Cheers
Twan you could look at perfmon counters, but I’d guess that there not going to show anything that you don’t already know…? i.e. lots of cpu usage, increased memory usage and reduction in page life expectancy
A shot in dark, do you have any anti-virus or anti-spyware tools on this server?
Or any other software that is consuming server resources? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Someone wrote to me with this bright idea:
quote:A shot in dark, do you have any anti-virus or anti-spyware tools on
this server? Or any other software that is consuming server resources?
this server? Or any other software that is consuming server resources?
Good thought. Unfortunately, this is a pretty bare bones machine and neither of these tools are installed. But I appreciate your thinking outside the box about my poorly performing box! I have been getting some Microsoft *help* on this. Although, so far, they hasn’t been alot of help. Maybe you guys (and gals) and help me make some sense out of what they have been having me look at. I can make the system *hang* (MS has been calling it a "CPU Spike" b/c the cpu utilization goes to 100% and stays there for 20-30 mins) by running the same query simultaneously from 2-3 concurrent sessions. Microsoft’s inital remedy didn’t make alot of sense — at least to me! But maybe I just don’t get it –let me know what you think. they asked me to update statistics and then manually reindex each table in the DB. I suppose this can’t hurt. But don’t these tasks get done automatically? And if the stats were screwed up, wouldn’t the query ALWAYS run slowly…not just when there were concurrent jobs? How would this cause a spike?
Does this sound like some kind of resource conflict to you guys? The last ping I got from them was something about conflicts in the tempdb. But as a sql server programmaer, I’ve never had any reason to play with tempdb. Are they on to something, or just helping me chase my tail? Thanks for any ideas you have. -dz tngresearch.com
quote
oes this sound like some kind of resource conflict to you guys? The last ping I got from them was something about conflicts in the tempdb. But as a sql server programmaer, I’ve never had any reason to play with tempdb. Are they on to something, or just helping me chase my tail?
Are there select … into queries?
oes this sound like some kind of resource conflict to you guys? The last ping I got from them was something about conflicts in the tempdb. But as a sql server programmaer, I’ve never had any reason to play with tempdb. Are they on to something, or just helping me chase my tail?"they asked me to update statistics and then manually reindex each table in the DB. I suppose this can’t hurt. But don’t these tasks get done automatically? And if the stats were screwed up, wouldn’t the query ALWAYS run slowly…not just when there were concurrent jobs? How would this cause a spike?" Automatic update statistics is for the only 10% of all table.
So, full update statistics could be a good idea. Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
]]>