When the wrong plan is implemented in a video game, it could result in the player having to repeat that level until the correct plan is implemented or it results in wasted time and resources to complete the level. If the statistics are out of date and wrong, the query optimizer is creating a plan using misinformation which could result in it not choosing the optimal query plan. SQL Server uses the cardinality as a factor to help determine the best query plan. The histogram is used to determine cardinality, number of rows in a query set. What happens if that information is out of date and wrong? That could result in delays, mishaps, or death. To make a plan, one must use the information they have on hand to determine the best steps. This gives us 3.333333… If DISTINCT_RANGE_ROWS is not greater than 0, the AVG_RANGE_ROWS is 1.īefore going into battle in a video game, it is probably not a good idea to just rush out into an unknown area guns blazing with only an end goal in mind. 6 is greater than 0, so we divide 20 by 6. Example using step 199 from the photo above. DBCC SHOW_STATISTICS ('',IX_Person_LastName_FirstName_MiddleName) WITH HISTOGRAM īreakdown of the data in Step 199: Select LastName The following is an example of the data contained in a histogram. A user defined statistic can be created by using the CREATE STATISTICS statement without an index. These statistics are usually automatically generated when a query is compiled by the query optimizer and when an index is created/rebuilt. Correlation statistics are collected when more than one column from the same table is specified. This helps determine the number of duplicated values between columns. Correlation Statistics, also known as densities, contain the number of distinct rows/values in a column. Distribution Statistics refers to how values are distributed in the columns of a table or index. There are two types of statistical information used when creating the histograms, distribution and correlation. Histograms measure the frequency of which a value occurs within a data set. They are statistical information about the distribution and/or correlation of data in the first specified column of indexes or tables in the form of a histogram. Statistics are exactly what the name implies.

Update statistics sql server