Hello there,
I have found an issue in our environment that I cannot explain.
There is a statistic based off of two columns in our central table that are used in many stored procedures to JOIN back and forth to this table. There is an auto generated column statistic based off of these two columns. This table as ~47,000,000 rows.
When the statistic is refreshed WITH FULLSCAN, it produces a histogram with 96 steps. Numerous stored procedures that rely on this statistic then take several seconds to complete.
When the statistic is refreshed with a really low row count sample, or dropped and recreated from scratch without giving any sample rate allowing SQL to determine the sample to use, the procedures that were previously taking several seconds to run, complete in < 1 second.
My question is, how can a full scan statistic refresh produce a histogram that causes performance issues over a very small sampled refresh.