March 19, 2013 at 4:40 am
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?
March 19, 2013 at 7:13 am
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...
March 19, 2013 at 10:00 am
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
March 20, 2013 at 2:00 pm
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 performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply