March 28, 2008 at 12:15 pm
Last week our main development box (sql 2005) was so heavily utitlized (CPU near 95% solid) that when develoeprs tried to connect to it they would time out. I tracked it down to our Data Warehouse guys -- they were running queries (JOINS, rebuiliding INDEXes, etc.) of tables with 50 million rows and sucking up all CPU resources. I could not find any blocking (sp_who2) and wonder if the demands of their jobs are really that heavy (maybe) or something else is going on. How would you approach this "issue"?
TIA,
Barkingdog
P.S. Their job usually runs on early Sunday morning -- when nothing else is running on that box -- and takes about 12 hous to complete.
March 28, 2008 at 12:55 pm
A good thing to have would be a performance baseline taken under 'normal' conditions to compare performance against when these large processes are running. This would be i/o, cpu ,disk stats fom system monitor\perfmon.
A quick way to get the connections using most memory, i/o, cpu is
select * from master..sysprocesses order by cpu desc
or whichever resource type you want to sort by. Ignore system processes (spid < 51)
As this is 2005 I bet there are some useful standard reports and DMVs, but I am not too au fait with them yet.
---------------------------------------------------------------------
March 28, 2008 at 1:29 pm
I would say that CPU usage of 95% and connections timing out is not reasonable!! It sounds like you are sharing your development box with multiple development groups? How does it run when the DW guys are not pounding it with index rebuild and massive joins? I would suggest working with the managers from each group to either 1> come up with some groundrules as to which types of severely resource intensive operations you can perform during normal business hours or 2> negotiage for additional hardware so that your development groups can have a dedicated environment and you are not competing for resources and thereby preventing others from being able to work.
March 28, 2008 at 2:58 pm
Barkingdog (3/28/2008)
Last week our main development box (sql 2005) was so heavily utitlized (CPU near 95% solid) that when develoeprs tried to connect to it they would time out. I tracked it down to our Data Warehouse guys -- they were running queries (JOINS, rebuiliding INDEXes, etc.) of tables with 50 million rows and sucking up all CPU resources. I could not find any blocking (sp_who2) and wonder if the demands of their jobs are really that heavy (maybe) or something else is going on.
Resource contention (such as CPU or diskIO) can cause serious performance issues, but will not normally show as "BLOCKING", because it has nothing to do with locks.
How would you approach this "issue"?
I would not run heavy OLAP on the same box as a critical OLTP system. It is somewhat contrary to both of their goals. Failing that, I would would get more processors/cores and then limit how many cpu cores it could use at once.
P.S. Their job usually runs on early Sunday morning -- when nothing else is running on that box -- and takes about 12 hous to complete.
Yes, a large intensive SQL job is not a good thing to share with a workload of on-line interactive users. SQL server has no way to allow you to prioritize the sessions for preferential CPU usage and so the big job will tend to crowd out the little sessions (performance-wise).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply