Very Computer

I have a 30 gig database that we added millions and millions of records to.
It ended up being 42 gig when done.  I then used DBCC DBREINDEX on each
table.  I then did an UPDATE STATISTICS <table> WITH FULLSCAN, ALL on all
tables.

But when I run queries and look at the plan, the plans have some tables in
red that say there is statistics missing for the table.

What am I missing here?

Update statistics only updates statistics that are already there, it doesn't
create new statistics, i.e. statistics are not created by default on every
column, only when they are needed. Running a query with a WHERE clause,
ORDER BY, GROUP BY etc on a column will generate statistics on that column.

--
Jacco Schalkwijk
SQL Server MVP

Quote:> I have a 30 gig database that we added millions and millions of records
to.
> It ended up being 42 gig when done.  I then used DBCC DBREINDEX on each
> table.  I then did an UPDATE STATISTICS <table> WITH FULLSCAN, ALL on all
> tables.

> But when I run queries and look at the plan, the plans have some tables in
> red that say there is statistics missing for the table.

> What am I missing here?

Hi Kevin,

Have you used "Create Missing Statistics" Dialog Box in Query Analyzer?

Use the Create Missing Statistics dialog box to create missing statistics.
SQL Query Analyzer suggests additional indexes and statistics on nonindexed
columns that would improve the query optimizer's ability to process a
query. In particular, SQL Query Analyzer displays missing statistics so
that they can be created.

How to create statistics (Query Analyzer)
To create statistics

On the Query menu, click Show Execution Plan.

Execute the Transact-SQL script in the query pane.

In the result pane, click the Execution Plan tab.

Right-click the icon of the physical operator that suggests that statistics
need to be created (table name in red), and then click Create Missing
Statistics. The database, table, and column(s) that the Graphical Execution
Plan suggests need new statistics are automatically selected.

Optionally, in Statistics name, enter the name for the statistics.

Optionally, in Amount of data to sample, select:
Default to let Microsoft? SQL Server? determine the number of rows to
sample automatically.

Sample all the data to instruct SQL Server to sample all of the data in the
table.

Sample % of the data and enter a percentage of data to sample to base the
statistics on.
Optionally, select Do not automatically recompute statistics (not
recommended) to prevent SQL Server from updating statistics automatically
as the data is updated.

Optionally, click Edit SQL to view and edit the Transact-SQL statement used
to create or update the statistics.

By monitoring the Missing Column Statistics event class, you can determine
whether there are statistics missing for a column used by a query. Missing
statistics can cause the optimizer to choose a less-efficient query plan.
For more information about creating column statistics, see Statistical
Information.

Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

| Subject: Missing statistics in plan
| Date: Thu, 16 Oct 2003 09:37:39 -0500
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165

| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: 12.96.65.193
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:396441
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I have a 30 gig database that we added millions and millions of records
to.
| It ended up being 42 gig when done.  I then used DBCC DBREINDEX on each
| table.  I then did an UPDATE STATISTICS <table> WITH FULLSCAN, ALL on all
| tables.
|
| But when I run queries and look at the plan, the plans have some tables in
| red that say there is statistics missing for the table.
|
| What am I missing here?
|
|
|