Advanced Index Statistics Query

I have noticed that running UPDATE STATISTICS without specifying any sampling
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
> >I have noticed that running UPDATE STATISTICS without specifying any sampling
> > options, thus accepting the default selected by SQL Server, can reduce the
> > level of detail contained within the index distribution statistics.
> >
> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
> > much less accurate, which I suppose you'd expect with a smaller sample size.
> > However, I do not understand what the optimizer gains from reducing the
> > number of steps during an UPDATE STATISTICS execution.
> >
> > Kind Regards
> >
> > Andrew Pike
> > --
> > SQL Server DBA
> > UBS IB
> >
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS
>> without
>> specifying sampling options *will* sample:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
>> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>> >I have noticed that running UPDATE STATISTICS without specifying any sampling
>> > options, thus accepting the default selected by SQL Server, can reduce the
>> > level of detail contained within the index distribution statistics.
>> >
>> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
>> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
>> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
>> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
>> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
>> > much less accurate, which I suppose you'd expect with a smaller sample size.
>> > However, I do not understand what the optimizer gains from reducing the
>> > number of steps during an UPDATE STATISTICS execution.
>> >
>> > Kind Regards
>> >
>> > Andrew Pike
>> > --
>> > SQL Server DBA
>> > UBS IB
>> >
>>