Weird 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 apparently make the variable regress to its initial value?

I've never seen SET STATISTICS TIME ON having an influence on the result before.
Am I missing something, here?

Answer 1

This does appear to be a bug, and one that I can reproduce in SQL 2008 R2 SP2. I believe your case statement takes a way from it a bit, as you can reproduce the issue 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 believe this is a bug, I'm not going to attempt to fix it. However, I would suggest moving all SSMS settings to the beginning of your batch and run it all that way. This work-around would have avoided the issue.

My speculation is that when the option is changed it goes back through the entire query to try to figure out how to make the execution plan, and when it does that it's grabbing (incorrectly) the initial value of the variable. Since it's using OPTION(RECOMPILE) that parameter is placed into the plan as a constant, which is why you only see this bug when using that option.

It is very odd that if you run that select statement again immediately after that it starts to return the proper value. This is something I did not expect, and gives you another work-around allowing you to turn your statistics on and run something else after it, such as, oddly enough, SET STATISTICS IO ON.