Lets see real code please, not pseudo code.

Try the awesome CASE keyword:

CASE WHEN rankord IS NULL THEN LAG(., -1) ELSE rankord END and group on that to get the actual sequence of ranking? No idea if this...

Since it is repeatably the first-run-of-the-day perf problem then fine AND it is dynamic SQL it REALLY sounds like a disk-IO-slowness issue. As someone said, track the IOs yourself early...

15K HDDs doing sequential large-block writes can often push 125MB/sec. A few disks of that and you have some very substantial write capabilities for tlog flushes. Note the HUGE caveat...

GilaMonster (3/18/2016)

---

Statistics mostly, table variables don't have them. Lots of rows in a table variable can result in really bad row count estimates by the...

That's really good news for you!! Hope you can keep the train on the tracks and on schedule until the spiffy new hardware is online!

A) I actually use physical cores on NUMA for my MAXDOP number.

B) Someone needs to convince your company that their physical resources/configuration (virtualized here?) is unacceptable for optimal performance. I...

1) There are often times when using a temp table (NOT table variable!!) can make a process be WAY more efficient due to widely varying amounts of rows or key...

But generally is it better to have data files on a slower drive and log files on SSD (I assume it depends)? Might be worth mentioning that there is asynchronous...

You haven't given us the most important factor here: what EXACTLY constitutes your X drive, and what else is on it? If it is a single or few disks, 8...

JarJar (3/16/2016)

---

thanks for the info, kevin.

do you have a link that you can recommend for sql san configuration optimization? i am not a san admin so it would...

Dang it! I keep forgetting to recommend that book to clients and posters with similar questions!! :blink:

I'm still waiting for the performance review of the new 2016 string splitter function I asked for long ago before all this mish-mash about NULL sidetracked the topic. 😀

JarJar (3/16/2016)

---

In the past, an i/o performance enhancing option was/is to split up your database into multiple datafiles and put each datafile on it's own dedicated disk array. The...

You are on SQL Server 2012. What you ask seems tailor made for the windowing function enhancement that came with that version. LAG in this case I would think.