Update Stats

Question

Post reply

Update Stats

ALI SQLDBA

SSCrazy

Points: 2071
More actions
January 14, 2010 at 9:54 am

#137491

Hello Gurus
I have a weird situation with update stats. It is quiet usual that in prod database, updating tables, deleting rows and infact tables takes palce. I was in a assumption that when the task starts, sql server dynamically change the stats on the tables. the task is failing with the following error.
"Executing the query "UPDATE STATISTICS **** WITH FULLSCAN
" failed with the following error: "Table '***'does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
I experimented by creating a new maintenance plan. It worked fine with the new one for one day and failed the following day. Is it using the same stats that it might have captured during the maint. plan creation?
Please suggest. our environment has daily updates/inserts/deletes to our tables, in this we also delete some tables automatically. Not sure why this is happening. Also if this helps, our maint.plan was running fine until last week, with similar kind of situation where the tables are being dropped.
Please suggest at your earliest convinience.
Thanks
Ali

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

Can you tell us the fragmentation level on this table..

post the results for the below query on your table

dbcc showcontig('table_name') with fast

With so many DML's I suppose you are better off doing index rebuilds, which do not need to have update statistics run..you should only run update statistics if you have re-organized the indexes...otherwise its just a waste of resouces

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 2

Thanks for your early reply.

The_SQL_DBA (1/14/2010)
Can you tell us the fragmentation level on this table..
post the results for the below query on your table
dbcc showcontig('table_name') with fast ..

do you mean the showcontig on the deleted table?

With so many DML's I suppose you are better off doing index rebuilds, which do not need to have update statistics run..you should only run update statistics if you have re-organized the indexes...otherwise its just a waste of resouces

we also have rebuild index task running every day. I read somewhere that running an update stats is must after rebuild index. Isnt it correct?

Thanks

Ali

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 3

post the results for the below query on your table
dbcc showcontig('table_name') with fast

The above query on my deleted table is givng this message as expected

"Msg 2501, Level 16, State 45, Line 1

Cannot find a table or object with the name "[dbo].[*****] ". Check the system catalog.

"

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

Ali_SQLDBA (1/14/2010)
Thanks for your early reply.
The_SQL_DBA (1/14/2010)
Can you tell us the fragmentation level on this table..
post the results for the below query on your table
dbcc showcontig('table_name') with fast ..
do you mean the showcontig on the deleted table?
With so many DML's I suppose you are better off doing index rebuilds, which do not need to have update statistics run..you should only run update statistics if you have re-organized the indexes...otherwise its just a waste of resouces
we also have rebuild index task running every day. I read somewhere that running an update stats is must after rebuild index. Isnt it correct?
Thanks
Ali

It is wrong to say that Update Stats is a MUST after a Re-build on the index. In case you do, you have to do it with FULL SCAN, but as I said before its a waste of resources if you update the statistics as it is not required in the case of a rebuild.

Only when you reorganize the indexes, it will be useful to update the statistics.

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 5

ok, I got that.

Do you have any thoughts on my error message.

Please let me know

Thanks

Ali

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 6

Ali_SQLDBA (1/14/2010)
post the results for the below query on your table
dbcc showcontig('table_name') with fast
The above query on my deleted table is givng this message as expected
"Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "[dbo].[*****] ". Check the system catalog.
"

Well the table has to exist for any such operations to be performed on it.

Just to clarify, is there a new table being created after each time a table is dropped?

If you answered Yes to the question above, are you indexing the newly created table?

HTH...

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 7

But, How is that linked with the update stats failing over a non-exsisting table?

Ali

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 8

By the way, there are indexes on the tables that are created newly

Ali

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 9

Did you have the table names specified explicitly in your maintenance plan job?

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

ALI SQLDBA SSCrazy Points: 2071 More actions · Answer 10

Following are the options that are checked in the maint. plan

1. Update All exsisnting databases

2. scan type full scan

3. database-- All databases

Thanks

Ali