October 27, 2004 at 7:50 am
The execution plan on a simple query on a view indicates several Missing Statistics for each table referenced in the view. However, I run a nightly jobs to optimize, rebuild indexes, update stats, etc. Why doesn't the normal updating of stats do the job?
The view simply UNIONs identical tables from mulitple SQL databases.
create view vw_coship as
select *
from (
select 'corp' as site, * from db1.dbo.[co-ship]
union
select 'crca' as site, * from db2.dbo.[co-ship]
union
select 'cvky' as site, * from db3.dbo.[co-ship]
union
etc.
) coitem
The query that causes the Missing Statistic is...
select * from vw_coship where [ship-date]='10/20/2004'
If you use the same query to directly query one of the source tables, it's fine.
select * from db2.dbo.[co-ship] where [ship-date]='10/20/2004'
When I right-click on the red node and choose Create Missing stats, it suggests to create about 9 stats. I have to do that for each database. I can automate this, but I don't understand why sp_updatestats or rebuilding indexes doesn't get it.
Other important info that might need to be known: Nightly, I truncate each table, then DTS INSERTs thousands of records in each one. I then rebuild indexes and update the stats.
Thanks for any help.
smv929
October 28, 2004 at 7:00 am
Should I use "EXEC sp_createstats" after re-loading each table. This is supposed to create statistics for all eligible columns in all user tables.
1. Is there a problem using this daily (for a reporting database)? That is, does it hurt to have statistics on every eligible column even though many might not be used. It's not an OLTP database.
2. Should you drop all statistics before re-loading tables and the recreate them?
smv929
October 28, 2004 at 8:53 pm
does your database have 'auto create stats' and 'auto update stats' turned on ?
If auto create stats is not turned on, you would have to manually create statistics. Auto create and auto update is probably best as SQL server will constantly create and update the stats, increasing sql servers execution planning and times.
Julian Kuiters
juliankuiters.id.au
November 12, 2004 at 10:00 am
yes, all my databases have the Auto create and auto update options on. That's another reason I'm confused as to why the statistics are missing. Anyone?
smv929
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply