41

I have found plenty of information on what STATISTICS are: how they are maintained, how they can be created manually or automatically from queries or indexes, and so on. But, I have been unable to find any guidance or "best practices" information regarding when to create them: what situations benefit more from a manually created STATISTICS object than from an Index. I have seen manually created filtered statistics helping queries on partitioned tables (because the statistics created for the indexes cover the whole table and are not per partition--brillaint!), but surely there must be other scenarios that would benefit from a statistics object while not needing the detail of an index, nor worth the cost of maintaining the index or increasing the chances of blocking / dead-locks.

@JonathanFite, in a comment, mentioned a distinction between indexes and statistics:

Indexes will help SQL find the data faster by creating lookups that are sorted differently than the table itself. Statistics help SQL determine how much memory/effort is going to be required to satisfy the query.

That is great info, mostly because it helps me clarify my question:

How does knowing this (or any other technical info on the whats and hows related to the behaviors and nature of STATISTICS) help determine when to choose CREATE STATISTICS over CREATE INDEX, especially when creating an Index will create the related STATISTICS object? What scenario would be better served by having only the STATISTICS info and not having the Index?

It would be super-duper helpful, if possible, to have a working example of a scenario where the STATISTICS object is a better fit than an INDEX.

Since I am a visual learner / thinker, I thought it might help to see the differences between STATISTICS and INDEXes, side-by-side, as a possible means of helping determine when STATISTICS are the better choice.

Thingy           PROs                             CONs
-------          ----------                       -------------------
INDEX            * Can help sorts.                * Takes up space.
                 * Contains data (can             * Needs to be maintained (extra I/O).
                   "cover" a query).              * More chances for blocking / dead-locks.

STATISTICS       * Takes up very little space.    * Cannot help sorts.
                 * Lighter maintenance / won't    * Cannot "cover" queries.
                   slow down DML operations.
                 * Does not increase chances
                   of blocking / dead-locks.

The following are some resources that I found while looking for this, one that even asks this same question, but it was not answered:

SQL Server Index vs Statistic

SQL Server Statistics Questions We Were Too Shy to Ask

Statistics. Are multicolumn histograms possible?

** To be clear, I do not have an answer for this and am actually looking to get feedback from hopefully a few people to provide what appears to be oddly missing information out here in the interwebs.

Improve this question
  • 2
    Indexes will help SQL find the data faster by creating lookups that are sorted differently than the table itself. Statistics help SQL determine how much memory/effort is going to be required to satisfy the query. – Jonathan Fite Oct 23 '15 at 19:05
  • @JonathanFite Thank you for that comment. I have incorporated it into my question :). – Solomon Rutzky Oct 23 '15 at 19:18
  • 1
    Following @JonathanFite's comment it would seem Statistics are best for increasing performance on ad hoc systems/tables/query patterns while Indexes are better for predictable query patterns. I mean this as more of a question than a statement. – Dave Oct 23 '15 at 19:34
21

You question revolves around - When is it a good thing to just create statistics vs create index (which create stats).

From my sql server internals notes (SQLSkills class- IE1 and IE2) and SQL Server internals book, below is my limited understanding :

SQL Server statistics are nothing but system objects that contain vital information about the index key values and regular column values.

SQL Server uses a cost based model to choose a "good enough" execution plan as fast as possible. Cardanility estimation (estimating no. of rows to be processed on each step of the query execution) is the most important factor in query optimization which inturn affects the join strategy, memory grant requirement, worker thread selection as well as choice of indexes when accessing data.

SQL Server wont use nonclustered indexes when it estimates that a large no. of KEY or RID loopup operations will be required, so it maintains statistics on indexes (and on columns) which will help in such estimations.

There are 2 important things about stats :

  1. Histogram stores info about data distribution for the leftmost statistics (index) column ONLY. It also stores info about multi column density of the key values. So essentially, histogram stores data distribution for the leftmost statistics column only.

  2. SQL Server will retain at most 200 steps in histogram irrespective of table size. The intervals covered by each histogram step increase as the table grows which leads to "less accurate" stats for large tables.

    Remember that index selectivity is a metric which is inversely proportional to density i.e the more unique values a column has, the higher its selectivity.

When particular queries do not run very often, you can select to create column-level statistics rather than an index. Column-level statistics help Query Optimizer find better execution plans, even though those execution plans are suboptimal due to the index scans involved. At the same time, statistics do not add an overhead during data modification operations, and they help avoid index maintenance. This approach works only for rarely executed queries.

Refer :

Note: Someone like Paul White or Aaron Bertrand can chime in to provide more color to your good question.

Improve this answer
  • "SQL Server wont use nonclustered indexes when it estimates that a large no. of KEY or RID loopup operations will be required" So, can the Q.O. use the stats object based on an index independently of the index? Meaning, if the index is not optimal, but the leading column is in the query, then the stats are still relevant. So would they be used? Or is this info implying that there might be cases when an index wouldn't likely be used, but since the stats still have value, then no real reason to create the index, just do the stats? – Solomon Rutzky Oct 23 '15 at 20:57
10

I'd say you need an index when you need to be able limit the amount of data / get to the correct data quickly based on the field(s).

You need statistics when you need the optimizer to understand the nature of the data to be able to perform the operations in best possible way.

What I have figured out, filtered statistics help when you have skews in your data that affect the plan heavily, for example in stack overflow few users have huge number of posts, so using just average posts per user isn't really the best estimate. So you could create a filtered statistics on userId based on the user name and then SQL Server should know that when this user name is in the query, this is the user id it will get, and it should be able to figure out, that the indexed field in the posts table will have a huge amount of rows with that id because histogram exists there. With averages, it's not possible to do that.

Improve this answer
  • 1
    Hi there, and thanks for answering. So, when would I need/want the optimizer to better understand the nature of the data, and yet not be limiting that data or wanting to get to it faster, or need it to "cover" the query? Same for your filtered index example. I do get what you are saying in terms of breaking out edge-cases from averages, but why would the filtered statistics be better than a filtered index on the same fields? This is the distinction I am trying to get to. – Solomon Rutzky Oct 23 '15 at 19:30
  • Like in the example, you can't create a filtered index on the user name to the posts table because it doesn't exists there. You could create it based on user id, but that's not in the where clause. – James Z Oct 23 '15 at 19:31
  • But wouldn't UserID be in the JOIN condition, even if not in the WHERE? And wouldn't that be good enough to pick up a filtered Index? – Solomon Rutzky Oct 23 '15 at 19:36
  • @srutzky Maybe more likely in the most current versions, but in general I wouldn't rely on that... in most cases, the predicates have to match exactly. I forget if they fixed this but at one point a filtered index WHERE BitColumn = 0 would not be selected for a simple query WHERE BitColumn <> 1. (And to be clear, the bit column was not nullable.) I think there were similar cases like IntColumn > 10 not matching IntColumn >= 11. – Aaron Bertrand Oct 23 '15 at 20:11
  • Filtered indexes cannot be used if there is a chance that next time someone uses the plans the filtered index is not suitable any more. I can't think any joins that could use a filtered index. Even variables can't be used because next time the value could be something not suitable. – James Z Oct 23 '15 at 20:16
6

From 70-461 Training book by Itzik Ben-Gan

There are only a few possible reasons to create statistics manually. One example is when a query predicate contains multiple columns that have cross-column relationships; statistics on the multiple columns can help improve the query plan. Statistics on multiple columns contain cross-column densities that are not available in single-column statistics. However, if the columns are already in the same index, the multicolumn statistics object already exists, so you should not create an additional one manually.

Improve this answer
  • Thanks for posting this. This answers part of my question but still leaves open the question of: If I need the multi-column statistics, why would I create only the STATISTICS instead of the Index, which would include the STATISTICS plus additional info that could further help the query(ies)? – Solomon Rutzky Oct 23 '15 at 19:40
  • 2
    I think Kin's explanation would further explain what you are after. Perhaps a heap that's frequently inserted, but rarely queried? – Kentaro Oct 23 '15 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.