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.