Yeah, Cost Threshold for Parallelism is way too low.  That is likely causing the crazy spikes in response/CPU times.

On the missing index views? They just don't say anything. I'm not talking about all the other DMVs. They're vital. 1 million percent so. But the missing index DMVs?...

I wonder what Jonathan's parallelism settings are, and other SQL settings that might affect the run times.  Some of those results are definitely out of place.

Generally speaking, the missing index recommendations taken from the DMV tables are useless. That's because they are not, and cannot be, linked to a given query. So you just...

These are the results from my machine

***********************************************************************************************************************
===== Scott's Code) =====
DBCC...

I'm curious to see if anyone takes a shot at (2).  Maybe it's TL;DR :smile:.

;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Itinerary ORDER BY CreationDate) AS row_num
    FROM dbo.Test
)
SELECT T.*
FROM dbo.Test T
INNER JOIN...

As I see it, it's not the ROW_NUMBER itself that causes inefficiency (SQL is remarkably efficient at providing ROW_NUMBER), it's the multiple uses of it and thus multiple scans of...

For (4), some/many of you may be wondering, "Why on earth would you ever have a trigger returning an identity value?"

But, yes, there is actually a good reason for doing...

ok, that's not what I thought you meant. I thought you meant flip all the bits.

This works for 0 and 1:

DECLARE @smallint smallint =...

I strongly suspect that the specific proc nor statement is available, since the "standard" dm_* views don't include that data.

If you could post the proc itself, I will look and...

ok, that's not what I thought you meant. I thought you meant flip all the bits.

This works for 0 and 1:

DECLARE @smallint smallint =...

I'm trying to think of a more efficient way, but for now this should at least function correctly:

;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER()...

smallint and char(3) (and I have added the data types to the original post).

ok, that's not what I thought you meant. I thought you meant flip all the bits.

This works for 0 and 1:

DECLARE @smallint smallint = 0
select @smallint...