Esat Erkec
SQL variable and its performance effects

Impact of SQL Variables on Performance

May 10, 2021 by

In this article, we are going to learn how SQL variables negatively affect query performance and also examine the causes of this issue.

Introduction

SQL variables are used to store a single data value during the execution period of a query and they are widely used in the design of the queries. The local variables are declared by the users and their name starts with @ sign. The first initialization of the value of the local variable is set to the NULL value.

The following syntax defines how to declare a local SQL variable:

The drawback of the SQL variables is that the query optimizer can not know these variables’ values during the query plan generation process. For this reason, the query optimizer can not use statistics information efficiently. In this case, the query optimizer uses an unexact estimated number of rows of information and as a result, it can not generate effective and optimal execution plans. There is no doubt that an inefficient query plan can get us in trouble.

Pre-requirements

In this article, we will use the Adventureworks2017 sample database for our examples. At the same time, we will also use the Create Enlarged AdventureWorks Tables script to obtain an enlarged version of the Adventureworks database.

It is useful to take a look at some concepts related to the statistics without going through the details of the examples.

SQL Server Statistics

SQL Server statistics are the metadata objects that store distribution information of the column values. Statistics are the main input of the query optimizer because they hold the distribution of the table data and the optimizer uses this statistical information to estimate how many rows will be returned from a query. For the high performer queries, SQL Server requires a high degree of accuracy of this statistical information to generate more effective query plans.

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS command displays the header, the density, and the histogram datasets of statistics. These datasets include detailed information about the statistics.

Histogram of a statistics

The header data shows the basic information about the statistics. It gives information about the when last update time of the statistics and rows number of tables. The rows number identifies the total number of the underlying table when the statistics were last updated. The Rows Sampled column shows how many rows were analyzed to generate the statistics.

The density vector date set shows the distribution of values. The All Density column value always between 0 and 1 and if this value is closer to zero the column values are unique.

The histogram displays the statistical distribution of the column values:

  • RANGE_HI_KEY column identifies the upper bound value for a histogram step
  • RANGE_ROWS column identifies the number of rows greater than the previous histogram step upper bound and not equal to the current upper bound value
  • EQ_ROWS The number of rows that column value equals the upper bound of the histogram step

Estimated Number of Rows

When trying to find out a solution to a query performance problem, we often hear the name of the same property. This property is the Estimated Number of Rows and this property value is calculated using the statistics. The Estimated Number of Rows is an estimation of how many rows the operator will return per execution and it is calculated during the compilation of the query plan by the optimizer. Accuracy of the Estimated Number of Rows value calculation is significantly important for the query optimizer because a query memory amount calculation is based on the estimated number of rows and rows size if so this value is incorrect to the desired memory calculation will be incorrect.

How The Estimated Number of Rows is Calculated by The Query Optimizer

In this part of the article, we will learn how the query optimizer calculates the estimated number of rows. Firstly, we will enable the actual execution plan and execute the query using literal values.

Execution plan of a query and SQL variable

As seen in the above image, the query plan suggests an index but before creating this index we will look at the SELECT operator OptimizerStatsUsage property. This property list all statistics names that were used during the execution of the query by the optimizer.

Execution plan OptimizerStatsUsage property

The LastUpdate time is equal to the execution time of the query because the Auto Create Statistics option is enabled, and the SQL optimizer creates statistics on the nonindexed columns that are used in a query predicate. As a second step, we will look at the Index Scan operator Estimated Number of Rows attribute.

Index Scan property of a execution plan

The Estimated Number of Rows shows 10991,9 but how this value is calculated. The answer is very simple, using the statistic histogram. The histogram details can be displayed through the following query.

Index Scan property of an execution plan

In the histogram:

  • 762,7984 is the number of rows that the ModifiedDate column value is equal to ‘2014-06-16’
  • 4421,455 is the number of rows that ModifiedDate column value greater than ‘2014-06-16’ and ModifiedDate less than ‘2014-06-19’
  • 5807,669 is the number of rows that a ModifiedDate value is equal to ‘2014-06-19’

When we sum these three values (762,7984 + 4421,455 + 5807,669 = 10.991,9224‬) equals the the estimated number of rows in the above execution plan.

What is the difference between Actual Number of Rows and Estimated Number of Rows?

We may ask, why a huge difference exists between the Actual Number of Rows and the Estimated Number of Rows values. The question-answer is very simple the histogram data is generated only % 5,747 of the data.

Execution plan SamplingPercent attribute

Now, we will create the suggested index and re-execute the same query.

After creating the index, the execution plan of the query will completely change. The clustered index scan operator replaces its place with a clustered index seek operator.

SQL Server index seek operator and estimated number of rows

At the same time, we can not see any difference between the Actual Number of Rows and the Estimated Number of Rows values.

The execution plan Actual Number of Rows property

The SamplingPercent value shows %100 because creating an index generates new statistics to touch every column value.

Get the list of the statistics when used during the execution of the query

When we re-calculate the estimated number of rows using the histogram.

How we can use statistics histogram data

In the histogram:

  • 1459 is the number of rows that the ModifiedDate column value is equal to ‘2014-06-16’
  • 4409 is the number of rows that ModifiedDate column value greater than ‘2014-06-16’ and ModifiedDate less than ‘2014-06-20’
  • 1932 is the number of rows that a ModifiedDate value is equal to ‘2014-06-20’

When we sum these three values (1459+ 4409 + 1932 = 7800) equals the estimated number of rows in the above execution plan.

SQL Variable and Statistics Interaction

After executing the following query the query plan will change but the Estimated Number of Rows value dramatically increase.

SQL variable and its performance effects

The reason for this situation is that the query optimizer has no idea which value is held in the SQL variable so it can not use the histogram efficiently. The query optimizer uses 16% of all data as the estimated number of rows. Another interesting thing about this execution plan is that we can not see the OptimizerStatUsage option in the SELECT operator attributes.

As for how to get rid of this problem, we can use the sp_executesql stored procedure. sp_executesql is a built-in procedure that allows us to dynamically run SQL statements and it also allows us to parameterize them. In the following query, we will declare the parameter of the and then we set its values.

How to eliminate SQL variable performance problems

As we can see in the execution plan details that sp_executesql performs much better than the query that used SQL variable.At the same time we can use RECOMPILE hint to avoid of this type of SQL variable problems.

Conclusion

In this article, we have learned some hidden secrets about the statistics and how the SQL variables effects performance of the queries. The local variables are widely used by database persons but these usage types slow down the query performance due to unavailable statistical data. Using the sp_execute procedure can help to resolve this problem.

Esat Erkec
Latest posts by Esat Erkec (see all)
1,933 Views