Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql not finishing or stuck


sql not finishing or stuck

Author
Message
Sailor
Sailor
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 837
I'm on SQL 2008 10.0.4371 with 16 cpus and 24 gigs of RAM. Under some condition, I start getting increasing CPU loads (until about 90% utilization on all 16 cpus) and a website that stops responding that never disappear until I restart SQL Service. During this time, the only thing that seems to jump out is 4 million reads per second. I tried running sp_whoisactive, but nothing seems to be jumping out. Other than everything is waiting on SQL to send the results. The app servers are WebSphere so we're using SQL JDBC along with (eeek) cursors. There are a couple queries that run slow and missing indexes, which I've submitted to IBM.

I have four temp dbs (100 meg and three 200 meg--which I want to set all to 100) one on D: the rest on E:. Of course yesterday I noticed the c, d and e drives are all on the same lun. So, I'm think maybe going down to 1 or 2 tempdb instead. The e drive tempdbs have a read stall of 28 seconds while the d is around 8 or less. Also, the other thing is I'm thinking of turning on ad hoc optimization as with the cursors there's no stored procs (ugh). I did notice that SQL was only using 4 gig of ram, and have fixed that.

Is there a switch I can turn on to end a command if it does not finish in a certain amount of time? Is that the governor?



Sailor
Sailor
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 837
I think I found part of the problem...which is that the index defrag routine requires page level locking, while the app requires row lock (page lock off). I'm not sure what effect having both page lock on AND row lock on causes, but we will see...



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
Some itesm that jumped out at me readnig your post.

I have four temp dbs (100 meg and three 200 meg--which I want to set all to 100) one on D: the rest on E:. Of course yesterday I noticed the c, d and e drives are all on the same lun. So, I'm think maybe going down to 1 or 2 tempdb instead.

Having multiple tempdb files is not always about spreading the I/O across multiple disk subsystems, it's also about keeping allocation contention in check. I would definitely resize the data files so they are all teh same size, but with 16 CPUs I would leave the 4 files in place even if they are actually on the same disk subsystem.

Also, the other thing is I'm thinking of turning on ad hoc optimization as with the cursors there's no stored procs (ugh).

Just because there are no stored procs does not mean that all queries are ad hoc. Prepared queries are parameterized just like stored procedures, i.e. you can think of "preparing a query" as "compiling a temporary stored procedure." I am not advocating the use of cursors, at all, server-side or client-side, but not all cursor-code suffers from being ad hoc.

Is there a switch I can turn on to end a command if it does not finish in a certain amount of time? Is that the governor?

I am no RG expert, but not that I know of, not based strictly on time. Once a query starts executing the engine lets it run as long as it needs. Maybe others will chime in.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
CapnHector
CapnHector
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 1789
opc.three (3/19/2013)
Some itesm that jumped out at me readnig your post.

I have four temp dbs (100 meg and three 200 meg--which I want to set all to 100) one on D: the rest on E:. Of course yesterday I noticed the c, d and e drives are all on the same lun. So, I'm think maybe going down to 1 or 2 tempdb instead.

Having multiple tempdb files is not always about spreading the I/O across multiple disk subsystems, it's also about keeping allocation contention in check. I would definitely resize the data files so they are all teh same size, but with 16 CPUs I would leave the 4 files in place even if they are actually on the same disk subsystem.

Also, the other thing is I'm thinking of turning on ad hoc optimization as with the cursors there's no stored procs (ugh).

Just because there are no stored procs does not mean that all queries are ad hoc. Prepared queries are parameterized just like stored procedures, i.e. you can think of "preparing a query" as "compiling a temporary stored procedure." I am not advocating the use of cursors, at all, server-side or client-side, but not all cursor-code suffers from being ad hoc.

Is there a switch I can turn on to end a command if it does not finish in a certain amount of time? Is that the governor?

I am no RG expert, but not that I know of, not based strictly on time. Once a query starts executing the engine lets it run as long as it needs. Maybe others will chime in.


setting a query timeout on the client side MAY work depending on the situation and code but as far as i know opc.three is right in the engine, once the code is sent to execute only completion or killing it will stop the processing (of course there is also the roll back if it was a data modification instead of a straight select).


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search