RE: How to TEST if statistics should be updated.

SSC Guru

Points: 323482

December 29, 2003 at 10:05 am

unfortunately, the db is in another state, and administered by a dba there; I was trying to diagnose issues by remote;

both Auto Update Statistics and Auto create Statistics are turned on in the restored version of this database, by looking at the Properties>>Options in EM after I restored a backup from the client.

Supposedly they used the Database Maintenance Wizard to create the job to do the reindex; over the phone i verified that there is a job scheduled using this format:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 3415B2E6-933F-4361-8517-EA23F6D807E0 -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '

So the extended stored procedure seems to be rebuilding indexes.

How about this...is it possible that the indexing plan is cached, and only refers to indexes that existed say, a year ago? and if I've added columns and indexes since then, so only some of the indexes get rebuilt?

I'm at a loss as to why the sp_createstats "seemed" to fix the issue, but should not have been required.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!