September 14, 2009 at 12:19 am
Hi.
We have a named instance with +- 55 databases on it. Most of them are relatively small except for one that has a couple of tables that have about 3.8 million records in them. We used the database tuning advisor to create some indexes and statistics on these tables, all went well, created the indexes and statistics in a couple of seconds.
A few weeks went by when we wanted to delete some records, it took 3 days to delete the records, so we disabled the indexes, and tried to rebuild them but the process kept being suspended, we just couldn’t get the indexed rebuild. (Did some testing and tried to insert records into a temp table with the same amount of records, the moment we try to insert more than about 27000 records the process goes into suspended mode and the inserts just hangs.(We left it for a couple of hours.) We tried the same insert on a different server with a backup of the database, inserts in one or two seconds. We have loads of disk space, increased the tempdb's size to about 40 gb, no luck.
We then started to detach all the other databases until only about 10 was left on the instance. After we did this, the indexes rebuild in 4 seconds. And everything was fine.
Can anyone help me regarding this? We need to re-attach the databases again but we need to know if there is some administrative task that needs to be done or setting that must be set?
Any help would be greatly appreciated.
September 30, 2009 at 4:13 pm
Did you take a backup of the databases before using the tuning advisor? Sometimes it creates hypothetical indexes that kill your performance. If you have a backup you should probably restore that copy and start over.
September 30, 2009 at 11:21 pm
Hi
Thanks for the reply, but we finaly figured out what the problem was, all of the databases had change tracking enabled on most of the tables, once we disabled that, the performance picked up again, the change tracking writes away to system tables but it, creates duplicate records and cannot write them to these tables but continuesly tries, seems to be a microsoft bug.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply