Post reply

cannot drop statistics

  • April 30, 2008 at 1:29 pm

    #187087

    I have a sql2k database that I was tuning a month ago. Just doing the donkey work at that time - I am now back and implementing my recommendations.

    I have noticed that in the SSMS viw of the database objects in the Statistics tab I see a whole bunch of stats all named starting with '_dta_'.

    I know that these are the database tuning advisor stats recommendations, however I did not implement any indices nor stats recommended by the DTA (which I did use) at the start of this project.

    If I implement a dta rec I normally remove the initial underscore - this way I know its me that has put this onto the schema.

    During one of the dta sessions I did have to abort, could this have caused all these objects to have been left in the database? If so how do I clean these up?

    I have tried the drop statistics command with no luck

    thanks

  • May 1, 2008 at 11:29 am

    #809740

    To drop statistics, you need to qualify it with its table name like this:

    Drop statistics my_table.my_statistics

    But let me ask you a question: why it bothers you ? It occupies very small amount of space, but sometimes it may be helpful to create better execution plan.

  • May 1, 2008 at 4:05 pm

    #809855

    I've managed to do this - it appears that in SSMS the _dta_index% and _dta_statistics% all appear in the Statistics tab of the table properties tree.

    Any the problem is that last tuesday night I implemented a whole bunch of these new column statistics and 5 new indices as recommended by DTA. It appears that the day operations has gone OK. Only 1 person complained with a problem when normally there would be more. But it has blown out a nightly scheduled job by 6 hours.

    So I needed to remove them and all orphaned ones from a previously aborted DTA session to return the schema to before implementing the "improved" features.

    I expected some affect to nightly operations but did not expect a blwoout of 6 hrs cutting into the morning.

    I'm back to square 1 and need to have a look at the nightly job to try and figure out why its being adversly affected by having full and complete stats

    thanks for your help

Viewing 3 posts - 1 through 2 (of 2 total)

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