January 18, 2002 at 2:17 am
After updating a table (insert, update) the 'rows' column in sysindexes for the cluster index is false (10000 instead of 300000) or sometimes is set to 0. I have often to run dbcc update_usage.
Has somebody an idea? Thanks.
January 18, 2002 at 5:16 am
Sounds like you have auto stats turned off? If you dont want stats updated automatically then you need a job to do it periodically (or remember to do it after big updates, data loads, etc). You can use sp_updatestats to update all statistics in a db.
Andy
January 22, 2002 at 12:30 am
Auto stats is ON. Other indexes (non cluster) are correct. It seems to be a problem when we run a query on this table that could use the clusterred index. Update statistics is not sufficient; i must run before it a dbcc update_usage.
January 22, 2002 at 7:46 am
Could the clustered index have been created with the STATISTICS_NORECOMPUTE option?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 24, 2002 at 1:52 am
I'm almost sure that index has not been created with STATISTICS_NORECOMPUTE option.
How can I be absolutely sure. When I execute sp_autostats on this table, flags for all indexes are ON.
January 24, 2002 at 7:52 am
If sp_autostats is showing the flags to be on, then it should be. I've been looking at the Microsoft Knowledge Base article, and based on what it says, the statistics should be updated, and you've indicated that they are on the nonclustered indexes. Very peculiar. Any case, here's the article, though it's not much help in this situation:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q195565
I'll keep looking.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 25, 2002 at 12:48 am
I've been looking at the Microsoft KB too but found nothing that sounds like my problem.Thanks for your contribution.
I' ll tell you about the continuation.
January 25, 2002 at 1:07 am
Please do. BTW, what version of SQL Server and what Service Pack?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 25, 2002 at 6:12 am
It's SQL Server 7 SP 3 .Perhaps I'd not been very plain: the "rows" column is updated but with a bad number of rows. Very strange.
February 5, 2002 at 5:29 am
Try rebuilding the indexes.
Look at DBCC REINDEX in SQL Books Online.
After this the run updateusage and see if this helps.
One things to keep in mind, the tables will become unavailable while the index is rebuilt.
February 5, 2002 at 5:34 am
Sorry on previous should be DBCC UPDATEUSAGE, this should clean up and inaccuracies in the sysindexes table.
February 6, 2002 at 2:00 am
OK I've already made all that (re-create index, dbcc update_usage ...) but I would like to have nothing to do !!! since auto-update is ON.
February 6, 2002 at 6:34 am
I have those processes in a job I run based on the size and number of non-clustered indexes I have on a database. But you will always have something to do.
February 6, 2002 at 7:54 am
aribi,
One question i never asked is if you have this operating in multiple environments (say development and production) with the same issue?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 26, 2002 at 1:22 am
It should be OK now. It was "just" a little bug: you can see the description at this address: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q308126.
I got a FIX for this problem and I'll try it.
Thanks to all.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply