2

I've run the Tuning Advisor on a workload I had saved. In its recommendations are a lot of create statistics and a few create indexes. I know the indexes will improve performance but can anyone explain how gathering statistics would be a performance improvement.

Improve this question
1
  • Statistics are a way for the query analyzer to get an idea of the data distribution within an index (or table). Generally, statistics are created automatically when needed, but the TA will recommend new ones based on the workload.
    – user3914
    Aug 23, 2012 at 20:23

2 Answers 2

Reset to default
3

At a very high level, statistics are kept so that SQL Server's Query Optimizer can determine the best way to go about executing the statements. Execution plans are generated for each operation and statistics help to determine the most efficient execution plan to use in order to complete a task.

There is an absolute ton of information out there on this topic. Before implementing tuning suggestions, though, you should be absolutely sure of what they are doing. For example, it's not always a given that adding an index will improve performance. Sometimes the effects are negative. Keeping statistics up to date, though, is a vital piece.

Here is a MSDN article describing the statistics that Query Optimizer uses in SQL 2008.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

I would also recommend reading through the offerings from these experts:

Improve this answer
1

SQL Server Tuning Advisor is a tool designed expressly for Microsoft SQL Server systems that enables database tuning in a variety of different ways. It can be used in both online and offline tuning applications depending on the needs of the individual or business. It has the ability to greatly improve the overall performance for searches by tuning various elements like indexes. It can also be used to optimize an SQL Server by creating or deleting partitions and indexes and modifying these types of elements in a way that is designed to make things go as fast as possible.

Gathering statistics would be a performance improvement by SQL Server Tuning Advisor for a number of different reasons. These statistics themselves are objects that are made up of information pertaining to the way that data and values are distributed across columns of a table or when in indexed view. Once the necessary statistics have been gathered, SQL Server Tuning Advisor will use that information to estimate the number of rows in the result of a particular query. The estimation is called the "cardinality estimate," and the information allows the application to create a query plan that is high in quality. Doing so will show a substantial performance improvement over the way that queries are traditionally run.

Giving a performance improvement to an SQL Server with SQL Server Tuning Advisor is similar in concept to improving the "Search" function in the Microsoft Windows operating system by allowing indexing. If SQL Server Tuning Advisor is able to use statistics to estimate what query results are going to look like, it will then be able to generate those results faster than it would if no statistics were present at all. Faster query results will give way to a smoother working environment overall

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .