Welcome to the SSC.com forums!

Have you rebuilt any/all indexes, especially the clustered index if there is one? And if there isn't, why isn't there? 🙂

Maddave (3/5/2014)

---

Thanks for the reply. Only one database (bar master and msdb) are using snapshot isolation. The vesioning would be kept in the version store of TempDB though, wouldn't...

Ickier (3/4/2014)

---

Thanks for all the help everyone, I realize there were and are limitations to how much you can help with the information being necessarily masked.

An interesting note, thanks to...

Ickier (3/3/2014)

---

CPU seemed fine I thought, but I'll take a closer look at that.

It's not spilling to tempdb (unless it can spill without showing up in the plan -- please...

I don't think you actually attached the plan. Oh, it is up on OP...

OK, so no UDFs it seems. No blocking. Are plans the SAME just with different reads?...

I don't see any queries, nor table structures (with indexing), nor actual query plans. How are we supposed to help you here?

You didn't mention it explicitly, so...

peter.cox (2/27/2014)

---

In case anyone's still interested (!) I've pretty much worked out what the issue was; after fully dissecting the code (as I say - there's a lot; 65 steps,...

SQLRNNR (2/27/2014)

---

you could run an extended event session to capture that since it looks like you are on 2012 and sql 2012 is more equipped and more friendly than previous...

UDFs can be one of the worst things you can do in SQL Server. Read my Death by UDF chapter in the SQL Server MVP Deep Dives 2 book...

+100!! sp_whoisactive @help = 1

Adam Machanic has a 30-day blog post series on sqlblog.com too.

You didn't give us much to go on, but I am betting on parameter sniffing. That you CAN find in a Binoogle search.

Try OPTION (RECOMIPLE) in your statement that...

I recommend a web search for: Itzik Ben-Gan Gaps Islands. There are some amazingly-efficient solutions for this now.

My advice is...

+1 for Books Online. MUCH more efficient (for all concerned) method of quickly learning about SQL Server and TSQL!!

When I am tuning a "critical" piece of code for a client I just capture profiler trace of the code for a period of time. I will use that...