What does Create Statistics do in SQL Server 2005?

Question

The Database Tuning Advisor recommends that I create a bunch of statistics in my database. I'm something like SQL n00b, so this was the first time I've ever come across such a creature. The MSDN entry was a bit dumb - can anyone explain what exactly this is doing and why is this a good idea?

Answer 1

Cost - based query optimization is a method that uses histograms and row counting to heuristically evaluate the cost of executing a query plan. When you submit a query to SQL Server, it evaluates it and generates a series of Query Plans , for which it uses heuristics to estimate costs. He then selects the cheapest query plan.

Statistics are used by the query optimizer to calculate the cost of query plans. If statistics are missing or out of date, they do not have the correct data to evaluate the plan. In this case, it can generate query plans that are moderately or highly suboptimal.

SQL Server (in most cases) automatically generates statistics for most tables and indexes, but you can add or force updates. The query setup wizard allegedly detected missing statistics or identified associations in the query to which statistics should be added.

Answer 2

The statistics are used by the optimizer to determine whether to use a specific index for your query. Without statistics, the optimizer has no way of knowing how many of your lines will meet the given condition, which will lead to optimization for the case of "many lines", which may be less optimal.

Answer 3

In short, it prepares your database for efficient operation. After receiving the statistics, your database knows (before it needs to figure out the execution plan), which may be its most efficient route.

Answer 4

Basically, SQL is simply updated with the type of indexing, the number of rows, etc. This helps SQL better evaluate how to fulfill your queries. Keeping updated statistics is good.

Answer 5

From BOL ...

Creates a histogram and its associated density groups (collections) over a column or set of columns in a table or indexed view. Statistics summary is also created on statistics built on char, varchar, varchar (max), nchar, nvarchar, nvarchar (max), text and ntext columns. The query optimizer uses this statistical information to select the most efficient extraction plan or update data. Updated statistics optimize the optimizer to evaluate the cost of various query plans and choose a high-quality plan.