manual create statistics that can improve performance

Question

are there any example that manual create statistics that can improve performance ?

Answer 1

If you disable autostats entirely, you may have to create statistics on selected columns for better performance.

If you have autostats enabled, there are still a few reasons why you may want to create statistics manually:

1. You want a higher sample rate, for instance FULLSCAN.
2. You want multi-column statistics.
3. You want filtered statistics.

I don't think any of these are very common. I have played with these options occasionally, but success has been limited. For instance, multi-column statistics does not give that much, since SQL Server only tracks the density for the second and further columns.

I seemed to recall that I once cooked up a demo where filtered statistics made a difference, but it might have been a far cry from a realistic scenario.

Answer 2

with autostats on, manually created statistics for either multi-column or filtered could be helpful,
however, I do not recall seeing multi-column statistics having the desired effect, but have seen unusual situations in which filtered statistics are important in providing a better execution plan
On fullscan statistics - mostly this applies to indexes in which the lead key is not unique, as opposed to column statistics.

Answer 3

Hi,

The Auto-created SQL Server statistics are single column statistics. You can manually create multi-column and filtered statistics.

For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.
More details, please see Statistics.