Strange side effect of "SET STATISTICS TIME ON" in SQL Server 2008R2?

Question

SET STATISTICS TIME OFF; DECLARE @String AS VARCHAR(10), @I INT = 1 WHILE @I <= 3 BEGIN SET @String = CASE @I WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' END; SELECT @String AS [StatsTimeOff] OPTION(RECOMPILE) SET STATISTICS TIME ON SELECT @String AS [StatsTimeOn] OPTION(RECOMPILE) SET STATISTICS TIME OFF SET @I +=1; END 

Returns

 StatsTimeOff ------------ First StatsTimeOn ----------- First StatsTimeOff ------------ Second StatsTimeOn ----------- First StatsTimeOff ------------ Third StatsTimeOn ----------- First 

Why does the combination of OPTION(RECOMPILE) and SET STATISTICS TIME ON seem to make the regression variable equal to its initial value?

I have never seen SET STATISTICS TIME ON affect an outcome before.
Did I miss something?

Answer 1

This seems like a bug, and I can reproduce it in SQL Server 2008 R2 SP2. I believe your case argument goes a bit off of it, since you can reproduce the problem by simply selecting @I, where I would rewrite it as follows:

 DECLARE @I INT = 1 WHILE @I <= 3 BEGIN SELECT FirstRun = @I OPTION(RECOMPILE) SET STATISTICS TIME ON SELECT SecondRun = @I OPTION(RECOMPILE) SELECT ThirdRun = @I OPTION(RECOMPILE) SET @I +=1 END 

Since I think this is a mistake, I am not going to fix it. However, I would suggest moving all the SSMS settings to the top of your batch and running it all. This workaround would avoid this problem.

My assumption is that when a parameter is changed, it returns through the entire request to try to figure out how to execute the execution plan, and when it does this, it captures (incorrectly) the initial value of the variable. Since it uses OPTION (RECOMPILE), this parameter is placed on the plan as a constant, so you only see this error when using this option.

It is very strange that if you run this select statement again immediately after that, it will start returning the correct value. This is what I did not expect, and gives you another way that allows you to turn on statistics and run something else after it, for example, oddly enough, SET STATISTICS IO ON.