Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Crash Dump Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 4:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 3, 2014 4:03 AM
Points: 446, Visits: 660
Hi All,

I have strange problem with my live cluster environment.
I have a dev environment in which a procedure completes in approx 4mins.

In my live environment, the same procedure if executed it runs for
1st time = 18 sec
2nd time = 18-20 sec
3rd time it hangs the servers. At this point, I cannot open new window from management studio. I cannot run any query in any other window in that server.
This keeps on for several hours or more than 1 day and then server crashes.

We have disabled the hypthreading and have made the default max worker threads.

My SQL Version:-0
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1
<X64> (Build 7601: Service Pack 1)

Thanks in advance.
Harsha
Post #1474053
Posted Tuesday, July 16, 2013 6:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:50 AM
Points: 1,326, Visits: 1,157
Hi,

Sorry but we would need more information on what the stored procedure is doing. Does it accept parameters and if so, do they vary?

Regards,

Andrew
Post #1474081
Posted Tuesday, July 16, 2013 7:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 3, 2014 4:03 AM
Points: 446, Visits: 660
Hi Andrew,

Thank you for the response.

The procedure accepts parameters. It generates a big dynamic query for 11 teams and then inserts into the 11 tables.

Currently I have resolved this now.

I had changed the affinity mask. It was 0 as default. After changing, this worked without any problems.

http://stackoverflow.com/questions/8243297/sql-server-not-using-all-cpu-cores-threads-on-server-hardware

Thanks,
Harsha
Post #1474118
Posted Tuesday, July 16, 2013 7:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:50 AM
Points: 1,326, Visits: 1,157
Glad it's sorted, what did you change the value to?
Post #1474125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse