Acrobatics field--estimated rows of table variables

When discussing the difference between a temporal table and a table variable, one of the key points is the estimated number of rows for both, where the estimated number of rows for a table variable is always 1, and the estimated number of rows in the temporary table varies with the amount of data in the table. Because of this distinction, it is often recommended to use temporal tables rather than table variables (and, of course, indexing issues) when dealing with large data volumes.

In popular science, the query optimizer estimates resource consumption based on the estimated number of rows and operator operators, chooses a relatively "better" execution plan based on resource consumption, and can generate an less efficient execution plan if the estimated number of rows is larger than the actual row count.

To raise a chestnut, looking at the distance of small khaki not far, riding a horse ran a half-day to find not yet, this is to see the story of the mountain run dead horse, if you can relatively "accurate" estimate the distance, then not riding but fly, this is the estimated number of rows impact implementation plan!

Learn about the estimated number of rows for a table variable today.

Test 1: First look at the estimated number of rows for the default settings table variable

DECLARE @TB1 TABLE(IDBIGINT IDENTITY(1,1)PRIMARY KEY, C1BIGINT)INSERT  into @TB1(C1)SELECT object_id  fromSys.all_columnsSELECT COUNT(1) from @TB1  asT1INNER JOINSys.objects asT2 onT1. C1=T2.object_id

With the execution plan above, it is easy to see that the actual number of rows for the temporary table @tb1 is 7490 and the estimated number of rows is 1.

Test 2: Using temporary tables

CREATE TABLE#TB1 (IDBIGINT IDENTITY(1,1)PRIMARY KEY, C1BIGINT)INSERT  into#TB1 (C1)SELECT object_id  fromSys.all_columnsSELECT COUNT(1) from#TB1 asT1INNER JOINSys.objects asT2 onT1. C1=T2.object_id

From the execution plan, the estimated number of rows for the temporary table is the same as the actual number of rows, all 7490.

Test 3: Use the option (RECOMPILE) query hint

DECLARE @TB1 TABLE(IDBIGINT IDENTITY(1,1)PRIMARY KEY, C1BIGINT)INSERT  into @TB1(C1)SELECT object_id  fromSys.all_columnsSELECT COUNT(1) from @TB1  asT1INNER JOINSys.objects asT2 onT1. C1=T2.object_idOPTION(RECOMPILE)

Option (RECOMPILE) is explained on MSDN as follows:

Instructs the SQL Server database engine to discard the plan generated for the query after it executes it, forcing the query optimizer to recompile the query plan the next time the same query is executed.

When compiling a query plan, the RECOMPILE query hint will use the current value of any local variable in the query, and if the query is in a stored procedure, these current values will be passed to any parameter.

In this test, using option (RECOMPILE) to make the query optimizer have an "accurate" estimate of the number of rows for a table variable, you can see that the actual number of rows and the estimated number of rows is 7490 using the query hint option (RECOMPILE).

PS: Through test 1 and test 3, it can be found that the execution plan changes with the estimated number of rows.

Test 4: Using trace flag 2453

Trace flag 2453, introduced by SQL Server SP2 and SQL Server CU3, is similar to option (RECOMPILE), which causes the query optimizer to have an "accurate" estimate of the number of rows in the table variable when it generates the execution plan. Instead of simply brutally using the estimated number of rows 1.

DBCCTRACEON (2453)DECLARE @TB1 TABLE(IDBIGINT IDENTITY(1,1)PRIMARY KEY, C1BIGINT)INSERT  into @TB1(C1)SELECT object_id  fromSys.all_columnsSELECT COUNT(1) from @TB1  asT1INNER JOINSys.objects asT2 onT1. C1=T2.object_idDBCCTraceoff (2453)

As you can see, when the trace flag is turned on, even if you do not use query hint option (RECOMPILE), the estimated row count and the actual number of rows for the table variable are 7490.

Test 5, the applicable scenario for trace flag 2453

Some articles are described as trace flag 2453 used only with join operations, and for some "simple" queries, trace flag 2453 has no effect

The simplest query is the select from, such as:

DBCCTRACEON (2453)DECLARE @TB1 TABLE(IDBIGINT IDENTITY(1,1)PRIMARY KEY, C1BIGINT)INSERT  into @TB1(C1)SELECT object_id  fromSys.all_columnsSELECT COUNT(1) from @TB1DBCCTraceoff (2453)

True, for the above query, turning on trace 2453 still does not resolve the table variable estimated number of rows is 1.

What about queries that are slightly more complex but do not have joins? Such as:

DBCCTRACEON (2453)DECLARE @TB1 TABLE(IDBIGINT IDENTITY(1,1)PRIMARY KEY, C1BIGINT)INSERT  into @TB1(C1)SELECT object_id  fromSys.all_columnsSELECTC1,COUNT(1) from @TB1  asT1GROUP  byC1SELECT *  from @TB1  asT1ORDER  by NEWID()DESCDBCCTraceoff (2453)

You can see that turning on trace 2453 affects the estimated number of rows for a table variable, even without a join operation.

After careful analysis, for a simple select from operation, no table variable in the amount of data, can only be clustered index scan, and for the next two queries, whether the group by or order by, the table variable data volume, the algorithm used to sort the data will change, Presumably: Under Turn on trace 2,453, if the data size of the table variable affects the resulting execution plan, an "exact" estimated number of rows will be returned for the table variable.

Test 5, recompile the problem

Because using option (RECOMPILE) causes the stored procedure to recompile, and frequent recompilation consumes a lot of CPU resources, will using trace flags cause recompilation problems?

I did not test this issue, and I learned from the reference article that the trace flag does not cause the stored procedure to recompile frequently, but if the data volume of the table variable that is used internally varies significantly with the incoming parameters of the stored procedure, the stored procedure is re-compiled. To play a Jasper, the same is out, 10 kilometers inside a taxi, 50 kilometers within the subway, 10000 kilometers to fly, different distances lead to different ways of travel, different parameters lead to different execution plans.

Although this trace flag does not introduce frequent recompilation problems, it also introduces a new problem, namely that different parameters require different execution plans, but because there is no recompilation, the reuse of the old execution plan causes performance problems.

Query hint option (RECOMPILE), although it leads to recompilation every time, is a good way to prevent "parameter changes causing the data volume of the table variable to change and ultimately generate an unplanned execution plan" issue.

Test 6, Custom table type

Since table variables can be affected by this trace flag, what are the user-defined table types? The answer is that custom table types can also be affected by this trace flag.

##==================================================##

Summarize:

Because of the presence of trace flag 2453, we can make the query similar to the effect of increasing query hint option (RECOMPILE) without modifying any code, and also avoid the problem of duplicate compilation of option (RECOMPILE). and trace flags can be set at the session level and at the instance level, by setting SQL Server startup parameters, it is easy to solve the problem that the table variable estimates the number of rows is 1, it looks good, but things have two sides, the instance-level trace flags need to be rigorously tested, It is also necessary to verify the effect of different parameters on the data volume of the table variable.

Personally, understanding the various trace flags is good for us to learn more about the nature of SQL Server, but in most cases we want to avoid using trace flags as much as possible, and it's best to use conventional methods to solve problem problems.

Who said "Can do, try not to mouth"!!!

##==================================================##

Reference connection:

Https://msdn.microsoft.com/zh-cn/library/ms181714.aspx

Http://sqlperformance.com/2014/06/t-sql-queries/table-variable-perf-fix

##==================================================##

Acrobatics field--estimated rows of table variables