MS SQL Server - Advanced Index Statistics Query

Author

Advanced Index Statistics Query

Andrew Pike

2006-10-25, 6:00 am

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

Tibor Karaszi

2006-10-25, 6:00 am

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/pr...5/qrystats.mspx

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Andrew Pike" < AndrewPike@discussio
ns.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C- 2996B63A60B6@microso
ft.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
>

Andrew Pike

2006-10-25, 6:00 am

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/pr...5/qrystats.mspx
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Andrew Pike" < AndrewPike@discussio
ns.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C- 2996B63A60B6@microso
ft.com...
>
>

Tibor Karaszi

2006-10-25, 6:00 am

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@discussio
ns.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716- 9C98DF0EE209@microso
ft.com...[color=darkred]
> 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:
>

Sponsored Links