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?
When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed. The setting of SET STATISTICS TIME is set at execute or run time and not at parse time.
SQL Server statistics are essential for the query optimizer to prepare an optimized and cost-effective execution plan. These statistics provide distribution of column values to the query optimizer, and it helps SQL Server to estimate the number of rows (also known as cardinality).
SET STATISTICS TIME (Transact-SQL) Displays the number of milliseconds required to parse, compile, and execute each statement. Syntax. Remarks. When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed.
In SQL Server, you can use the SET STATISTICS TIME statement to display the time it takes to execute a T-SQL statement. More specifically, it returns the number of milliseconds required to parse, compile, and execute each statement. When SET STATISTICS TIME is ON, the time statistics for a statement are displayed.
The “one row affected” text refers to the number of rows returned by the select statement that appears between the set statistics time on and off statements. Now, you know the basics of how to run the set statistics time statement, and you have some grasp of the output from the statement.
SQL Server Execution Times: CPU time = 1528 ms, elapsed time = 15901 ms. SQL Server Execution Times: CPU time = 1248 ms, elapsed time = 8055 ms.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With