sql 2008 cluster hang on a heavy load

[billo102]

forgive me if I'm in the wrong forum here, and i'll move if thats the case, but here is my issue :

We have a sql server 2008 active/active cluster running on windows 2008R2 O/S. 14GB RAM, 4xCPU. we have set a ceiling of 12GB for sql server. We're running an agent job which loads 3 million records to a database. during this load the job fails and the cluster seems to attempt to fail over to the other node but unsuccessfully i.e., the cluster address is no longer accessible. we have to manually fail the cluster node back.

during the load on viewing task manager we can see that memory usage hits a max of 12.5GB and CPU at times hits 100% on all 4 CPU, but for the most part fluctuates at an average of about 60%.

I suppose my question is, will a cluster try to fail over if memory or CPU are taking a heavy hit? or am i barking up the wrong tree?
also any ideas why it wouldn't fully fail over? we've crawled through logs, of which there are a lot, and can't find anything useful. we've also tried recreating the issue but it ran successfully at a later time. Also 3 million rows doesn't seem like a lot but in terms of resources should 14GB RAM and 4xCPU not be sufficient?
Many thanks

 

[mrdenny]

The Cluster will fail the SQL Server over if the SQL Server doesn't respond fast enough which can happen when the CPU runs to 100%. Edit the SQL Server resource and change the detection settings to be a little more liberal.

How are the 3 Million rows being loaded? A data load shouldn't run the CPU up very high unless there is something else going on.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[fredericofonseca]

Hi Denny,

In the load in question, the data is being moved from one SQL server database/table into another one with a merge statement, around 50 fields, 8-9 table joins, and a poorly index management. 7 or 8 hash tables been built by the SQL as part of all the joins.
1 UDF being used in maybe 12 fields on both the insert and update side of the merge (instead of the input select )

To add to the original post from Billy.
This is being run on a Windows server 2008R2 clustered, installed under a vmware ESXi server. Hard disk controller supplied as LSI Parallel (in my opinion they should change it to LSI SAS), and they had at one point in time an error being reported in one of the HD's supplied under that controller.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mrdenny]

Ouch, first thing to do it fix the indexing problems, and if possible loose the function. Between those two problems that'll probably fix most of your issues.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog