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 not finishing or stuck Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 4:40 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 247, Visits: 799
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?



Post #1432563
Posted Tuesday, March 19, 2013 7:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 247, Visits: 799
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...


Post #1432631
Posted Tuesday, March 19, 2013 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1432755
Posted Wednesday, March 20, 2013 2:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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

Jeremy Oursler
Post #1433466
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse