Shulou(Shulou.com)06/03 Report--

Understand SQL Server statistics

Original English text:

Http://blog.idera.com/sql-server/understanding-sql-server-statistics/

"Statistics provide a tool to intelligently respond to what you hear or see"-David Lane,2003

Use Idera's free tool to identify expired SQL Server statistics-SQL Update Statistics!

If there is an upcoming election and you are running and are going to travel around the country and city with flyers, you want to know about how many flyers you will bring.

If you are a coach of a sports team, you want to know the status of the players before deciding who, when to play and who to play against. You often play a matching game, and even if you have 20 players, you may be allowed to play 5 times at the same time, and you want to know which player of yours best matches the player list of other teams. You don't want to look at one by one during the game (table scan), you want to know who is the most suitable based on their statistics.

Like electing candidates and sports team coaches, SQL Server tries to use statistics to "IntelliSense" in its query optimization. Knowing the number of rows, page density, histogram, or available indexes helps the SQL Server optimizer determine more accurately how best to get the data. A common misconception is that if you have an index, SQL Server will use the index to get the record in the query. Not necessarily. If you create an index on less than 90% of the City columns with a value of "Vancouver", SQL Server is most likely to scan the table instead of using the index if it knows these statistics.

Most of the time, we should at least keep the statistics up-to-date (based on your configuration), but a better understanding of statistics is to help us better understand SQL Server optimization.

If you create statistics?

Statistics can be created in different ways:

N Statistics are created automatically when each index is created.

C

N if the database sets automatic creation statistics to enabled, SQL Server automatically creates statistics for non-indexed columns used in the query.

N CREATE STATISTICS statement.

What are the statistics like?

If you are curious, there are many ways to see what the statistics look like.

Method 1-you can "Statistics" node in SSMS, right-click the property, and then select "Details". The following is an example of statistics and histograms collected from a table in my database.

Method 2-you can use DBCC SHOW_STATISTICS WITH HISTOGRAM

Histograms are a great way to visualize the distribution of data in tables.

How do I update statistics?

The default setting in SQL Server is to automatically create and update statistics.

Note that Auto Update statistics has two options.

N Auto Update Statistics means that if you have a query and the statistics are obsolete, SQL Server will update the statistics before generating the execution plan.

N Auto Update Statistics Asynchronously means that if there is a query and the statistics are stale, SQL Server uses stale statistics to generate the execution plan, and then updates the statistics.

However, Idera provides cool free tools that simplify the process of finding and updating out-of-date SQL Server table statistics, which is easy to understand using the UI interface. You can find it here:

Https://www.idera.com/productssolutions/freetools/sql-server-statistics

How do we know that statistics are being used?

When generating the execution plan of the query, you can do the following check:

Check "Actual Number of Rows" and "Estimated Number of Rows".

If the quantity (continuous) is quite close, it is likely that your statistics are updated and used by the optimizer for query. If not, you should re-check how often statistics are created or updated.

How should we set up this configuration?

It is possible that when you are making a large number of updates to a table, you want to temporarily disable statistics updates, and you do not want automatic updates to slow down execution.

However, in most cases, you will keep the settings:

N Auto create statistics

N Auto update statistics

Reference:

Rob Carrol. Http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx

Elisabeth Redei has three excellent series of articles on SQL Server statistics:

Http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx

Http://sqlblog.com/blogs/elisabeth_redei/archive/2009/08/10/lies-damned-lies-and-statistics-part-ii.aspx

Http://sqlblog.com/blogs/elisabeth_redei/archive/2009/12/17/lies-damned-lies-and-statistics-part-iii-sql-server-2008.aspx

An excellent book on statistics:

N Apress. Grant Fritchey & Sajal Dam. "SQL Server 2008 query performance tuning essence"

N RedGate. Holger Schmeling. "SQL Server Statistics"

More resources:

White Paper-Waiting on Wait Stats

Webcast-What Are You Waiting For?

Idera Free performance Monitoring tool-SQL check

Trial version of Idera performance tuning product-SQL doctor

The translator recommends:

Statistics used by query optimizer in SQL Server 2008

Https://msdn.microsoft.com/en-us/library/dd535534%28SQL.100%29.aspx

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.