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 all the data to instruct SQL Server to sample all of the data in the
Sample % of the data and enter a percentage of data to sample to base the
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
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: 126.96.36.199
| 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
| 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
| 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?