Here's the T-SQL statement that has the big delta in execution time depending on the user.

The connection type for all users on our CRM is Windows integrated auth (SPPI).

Any feedback...

The first thing I notice is that the other user seems to hover around 668117 reads for his query, whereas I typically am between 80 and 120.

His CPU time is...

Is this question relevant to SQL 2012 or also SQL 2008 R2 and SQL 2005 as well? I couldn't find the related version info on BOL.

Just kidding folks. And...

Good question. I got the mirroring one wrong. Somewhere I thought I'd learned that SQL2012 didn't support mirroring for contained databases. Good learning experience, thanks.

Thanks for the QotD, it's a good refresher on the behavior differences between DATETIME and DATE datatypes.

Cheers,

Andre Ranieri

Thanks for the question! I usually start my day off with the SSS-QOTD to keep my knowledge fresh.

Nice, easy question for a change. First time in a month I didn't have to research BOL before choosing my answer.

+1 here. I chose "10" as the answer.

The jar either has 10 jelly beans in it, or 12.

As a point of intellectual curiosity, when I run this against my sandbox database (SQL 2008 R2) I get an error that the database needs to be in single user...

I got this one wrong (or right) because my answer assumes that DBCC CheckDB requires a database name, and in parentheses. At any rate, I didn't get the point...

Thanks - I was wondering about that.

BTW Lynn - I read your blogs religiously. Loved the one about deleting millions of rows in a batch without...

Parameter sniffing might have just been what caused a 30X increase in duration for this statement.

Again, out of intellectual curiosity, I changed my code to declare a second datetime parameter...

Possibly; running Chris' T-SQL statement with dynamic parameters shows 1686 reads in Profiler, compared to 388160 reads when the parameters are passed in from the exec sp_ExecuteSQL statement.

Profiler shows the...

Holy smokes - your statement ran in 4-8 ms, compared to the original 'fast' one taking 73-85 ms

I'm still wondering why the exact same statement would have different execution duration...

Done - I uploaded a ZIP file.

Here's another weird thing and perhaps a clue: if I modify the sp_executesql statement to explicitly declare the parameters as shown in the snippet...