April 13, 2006 at 1:30 pm
We run DBCC UPDATEUSAGE every week. I updated the statistics using "update statistics tableX" command.
I have not done the reindex after deleting 50 million rows. This table is huge and I cannot run dbcc reindex becuase that table is used by users throught the day except for 2 to 4 hrs. Reindex will take almost 1 to 2 days. I tried running DBCC defrag, it ran for almost 2 days and completed only 50% of the defrag phase.
This table has 1 clustered and 1 non-clustered index.
I will try running "update statistics tableX with fullscan" and see what happens.
Thanks for all the suggestions.
April 13, 2006 at 1:40 pm
Please, let us know what your results is.
April 13, 2006 at 2:03 pm
Got to thinking even more about how NOLOCK reacts to things in the system. How big is your Transaction Log currently and when was the last time you did a database backup?
April 13, 2006 at 2:23 pm
try
DBCC dbreindex(table),
this might take some time and table will be locked...
other option to defragment the index...
some how i think it related to fragmented index and/or pages
Borik
April 13, 2006 at 2:23 pm
We take full backup every 2 weeks and differential daily. Last successfull diiff backup was day before yesterday and the transaction log size is 70GB of which only 600MB used so far.
April 14, 2006 at 10:11 am
I ran updstats with fullscan, now the query takes 1 minute to complete. It's still bad. I will try running dbcc dbreindex.
April 20, 2006 at 4:17 am
Since NOLOCK does not apply any locks the time saved is from the Lock acquired, Lock released. It does run the risk of dirty reads if there are any transactions tacking place on the table however.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 7 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply