What does "Create Statistics" do in SQL Server 2005?

sql-server tsql statistics database-tuning-advisor
Electrons_Ahoy picture Electrons_Ahoy · Sep 24, 2008 · Viewed 15.9k times · Source

The Database Tuning Advisor is recommending that I create a bunch of statistics in my Database. I'm something of a SQL n00b, so this was the first time I'd ever come across such a creature. The entry in MSDN was a little obtuse - could someone explain what exactly this does, and why it's a good idea?

Answer

ConcernedOfTunbridgeWells picture ConcernedOfTunbridgeWells · Sep 24, 2008

Cost Based Query Optimisation is a technique that uses histograms and row counts to heuristically estimate 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 the costs. It then selects the cheapest query plan.

Statistics are used by the query optimiser to calculate the cost of the query plans. If the statistics are missing or out of date it does not have correct data to estimate the plan. In this case it can generate query plans that are moderately or highly sub-optimal.

SQL Server will (under most circumstances) generate statistics on most tables and indexes automatically but you can supplement these or force refreshes. The query tuning wizard has presumably found some missing statistics or identified joins within the query that statistics should be added for.

Related questions

How to find out SQL Server table's read/write statistics?

Is there a way to find a statistics on table read and write count on SQL Server 2005/2008? I am specifically looking for DMVs/DMFs without using triggers or audits. The goal here is to find out appropriate fill factor for …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More