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 INDEX
es, 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 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.