Statistics on non-indexed columns < second >

statistics on non-indexed columns

Sometimes, there may not be an index on a column in a join or filter condition. Even for such non-indexed columns, if the query optimizer knows the data distribution (statistics) of these columns, it is also likely to make the best choice.

In addition to the statistics on the index, SQL Server can establish statistics on columns that do not have indexes. even if it is not an indexed column, when you turn on SQL Server Auto-create statistics, SQL Server automatically creates statistics on query columns that execute where, join, and so on. information about the distribution of data or the likelihood of a particular value appearing on a non-indexed column can help the query optimizer determine the optimal processing strategy. This is still advantageous even if the query optimizer does not really use the index to locate these columns. If SQL Server is convinced that this information is beneficial for creating better plans (which usually happens when these columns are used for an assertion), statistics are automatically created on non-indexed columns. By default, statistics created on non-indexed columns are turned on. It can be configured by automatically creating statistics settings from the property = "option =" database. You can use the ALTER DATABASE command to programmatically override this setting. However, for better performance, it is recommended to keep this feature open.

Here is an actual combat to determine the statistics of this non-indexed column is also useful. First of all, two tables ta1,ta2 have 100,000 rows of data, but the contrast is very large, where ta1 column2 only one behavior 1, the remaining rows are all 2. Ta2 is just the opposite. None of the two columns are indexed.

The approximate appearance is as follows:

　　

Execute the following SQL statement:

SELECT Ta1.column2,ta2.column4  from JOIN Ta2  on = Ta2.column4 WHERE = 2

Look at the execution plan and I/O situation:

　　

　　

Is there a comparison to know which is better if SQL Server does not have statistics and how does it work?

Let's do this first:

 alter  database  Test set  auto_create_statistics off  --view statistics on TA1 table  sp_helpstats  ta1   "   ta2    --unload statistics on TA1 table  drop  statistics   dbo.ta1._wa_sys_00000002_0bc6c43e  --unload statistics on TA2 table  drop  statistics  ta2._wa_sys_00000002_0daf0cb0 

We'll execute the same statement again:

　　

We see a lot of exclamation marks on the execution plan above, which is due to SQL Server's lack of statistics tips.

Mouse click on an execution plan with an exclamation point:

　　

The properties also give a hint, and then look at I/O:

　　

Various readings have been added.

Summary:

Keep SQL Server's default statistics, but generally don't care about it. Unless query performance becomes slow, you can manually update statistics.

Statistics on non-indexed columns < second >