You failed to give us ALL the code in your sproc, most especially those things that drive the cursor(s). First thing I would say is put the output of...

I'm going to STRONGLY disagree with any notion anyone might have that putting this lookup stuff into a table (of any flavor) and joining to it in in a query...

That sounds like the way I would do it.

1) In my experience as a performance tuning consultant at lots of different clients of varying sizes, 95%+ of performance problems can be found with profiler with it's existing set...

1) Why can't you use Profiler? Aggregate profiler analysis is my single-most important tool as a performance tuning consultant!!!

2) You can capture workload with extended events.

3) sp_whoisactive is great...

tcronin 95651 (3/4/2016)

---

THis is a data warehouse with monster ETL's I have seen great things about the Fusion product. The fact is it is cheaper than adding the SSD...

Adam posted a 30-day blog post series on SQLBlog.com about all the ins-and-outs of sp_whoisactive. Read and learn.

I will say again that this is a perfect scenario for a SEQUENCE. It offers everything you are asking for and is very easy to use. Look it up...

The few clients I have had use this feature went the simple route. I don't know a best practice here.

No. It means precisely what it says. Nothing more and nothing less. Any data going into/out of tempdb, regardless of the source of that data, gets the encryption overhead. There...

+1000000

I'm with Jeff as usual on questioning the design decision/need here. But what you ask for seems tailor-made for SEQUENCEs, which are available in SQL Server 2012+.

It is confirmed here: https://msdn.microsoft.com/en-us/library/mt614935.aspx

I love putting a heavily used tempdb on local SSD, especially if it is like FusionIO and bolted into a PCI slot.

Just to reinforce how bad this logic flaw is, I had a client GO OUT OF BUSINESS because they did this - and I TOLD THEM IT WAS GOING TO...