Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 730
Serge Avatar asked Jan 07 '14 10:01

Serge


People also ask

What is set statistics time on in SQL Server?

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.

What does statistics do in SQL Server?

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).

What is set statistics time in SQL Server?

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.

How do I see time in SQL Server?

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.

What does “one row affected” mean in SQL?

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.

What are the execution times for SQL Server?

SQL Server Execution Times: CPU time = 1528 ms, elapsed time = 15901 ms. SQL Server Execution Times: CPU time = 1248 ms, elapsed time = 8055 ms.


1 Answers

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.

like image 167
Steve Hood Avatar answered Oct 19 '22 20:10

Steve Hood