Query To Delete Statistics 1

[HestonJames]

Hello Guys,

I've been having this problem:

http://www.tek-tips.com/viewthread.cfm?qid=1555259&page=1

Seems that DTA has crashed at some point and left lots (295) statistics on my database and it's causing problems

I need a query which will delete all statistics from a table which are prefixed with '_dta_' such as '_dta_stat_545454654_45_42_75'

Can anyone advise on the best way to do this?

Thanks,

Heston

 

[gmmastros]

dropping statistics can be scary. I mean.... statistics are used to determine optimal execution plans. Ya know?

Here is a query that will build the drop statistics code for you. I encourage you to examine each one before deleting it. My fear is that you could end up severely harming your performance by deleting the statistics.

Select 'Drop Statistics ' + Object_Name(sysindexes.Id) + '.' + sysindexes.Name
From   sysindexes
       Inner Join sysobjects
         On sysindexes.id = sysobjects.id
Where  sysindexes.Status & 64 = 64
       And sysobjects.xtype = 'U'

copy/paste the output to a new query window so you can run the drop statistics code.

Good luck


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

George,

Thank you for your code, that'll help a great deal. I appreciate your concerns on dropping stats, it could be a real problem for me, thing is I'm having the issue described here:

Take a look here

http://msdn.microsoft.com/en-us/library/ms190172.aspx

See, DTA has obviously crashed at some point and left it's prospective stats on the table, hundreds of them in fact.

This is why I only plan on removing the ones with the dta prefix, leaving all the originals in place.

Does that make sense? I won't run that code until I'm sure.

Heston

 

[gmmastros]

sure. it makes sense. I just want you to be careful. And it sounds like you are.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

yeah, I'm a little anxious about it, is there any way to get a list of dates that statistics were created? this would help me confirm which were created in batch by dta and which were done intentionally.

Cheers, Heston.

 

[HestonJames]

Sorry George,

As a quick addition, in my statistics list (in the object explorer) there are also a load of entries for what I guess to be proposed indexes which look like '_dta_index_MessageLog_16_277576027__K7_K6_K3_K4' which don't appear to be grabbed by you query above, any ideas?

Cheers,

Heston

 

[gmmastros]

I don't really know that much about statistics either (sorry). Just for kicks, I deleted all the statistics from my development database. Then, I ran a couple queries. I then checked and some statistics had been re-created automatically for me. This is because I have auto statistics turned on.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Ah ok, well that's very good to know! As I understand it I also have auto stats on so they'll be created. I'm not too worried about dropping them. However, what about the _dta_index doobies, any idea how to get rid of those?

Cheers mate, appreciate your advice a great deal.

Heston

 

[HestonJames]

Haha well this is a problem I could really do with not having to deal with at this time of night

Anyway, I've used this script in the end:

http://www.graytechnology.com/Blog/post.aspx?id=e21cbab0-8ae2-478e-a027-1b3b14e7d0b9

Quite similar to yours George, might be worth book marking for safe keeping, seems that this issue with DTA is fairly common.

Thanks again mate,

Heston

 

[gmmastros]

Those are probably hypothetical indexes. Do they return with this query?

Select  Name,
        Case When Status & 1 = 1 Then 'Y' Else '' End As IgnoreDuplicateKeys,
        Case When Status & 2 = 2 Then 'Y' Else '' End As [Unique],
        Case When Status & 4 = 4 Then 'Y' Else '' End As IgnoreDuplicateRows,
        Case When Status & 16 = 16 Then 'Y' Else '' End As [Clustered],
        Case When Status & 32 = 32 Then 'Y' Else '' End As Hypothetical,
        Case When Status & 64 = 64 Then 'Y' Else '' End As [Statistics],
        Case When Status & 2048 = 2048 Then 'Y' Else '' End As PrimaryKey,
        Case When Status & 4096 = 4096 Then 'Y' Else '' End As UniqueKey,
        Case When Status & 8388608 = 8388608 Then 'Y' Else '' End As AutoCreate,
        Case When Status & 16777216 = 16777216 Then 'Y' Else '' End As StatsNoRecompute
From     sysindexes
Where   Status & 64 = 0
        And Status & 32 = 32

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This should generate the Drop Index code for you.

Select  'Drop Index [' + Object_Name(id) + '].[' + Name + ']' ,
        Case When Status & 1 = 1 Then 'Y' Else '' End As IgnoreDuplicateKeys,
        Case When Status & 2 = 2 Then 'Y' Else '' End As [Unique],
        Case When Status & 4 = 4 Then 'Y' Else '' End As IgnoreDuplicateRows,
        Case When Status & 16 = 16 Then 'Y' Else '' End As [Clustered],
        Case When Status & 32 = 32 Then 'Y' Else '' End As Hypothetical,
        Case When Status & 64 = 64 Then 'Y' Else '' End As [Statistics],
        Case When Status & 2048 = 2048 Then 'Y' Else '' End As PrimaryKey,
        Case When Status & 4096 = 4096 Then 'Y' Else '' End As UniqueKey,
        Case When Status & 8388608 = 8388608 Then 'Y' Else '' End As AutoCreate,
        Case When Status & 16777216 = 16777216 Then 'Y' Else '' End As StatsNoRecompute
From     sysindexes
Where   Status & 64 = 0
        And Status & 32 = 32

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hey George,

Thanks! I'll keep that code on file, I'm not sure if you spotted my post above, there is a link to a rather tidy solution to the problem!!

I appreciate your advice a great deal, as ever!

Heston *big wet kisses.

 

[gmmastros]

I saw the post. But it was already after I wrote the stuff, so I thought I would post it anyway.

I also saw the bit about 'if you get an error, change it to...'. You probably won't get that with the code I posts. Apparently, DTA creates statistics and indexes. My code filters out each one so that it generates the correct code.

But... still... it's nice to know I was on the right track.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Yeah, your code is a little more Verbose but like you say it gives you DROP STATISTICS/DROP INDEX dynamically which is a really nice touch, hence the purple star!

Cheers my man,

Heston

 

[gmmastros]

Another difference is that my code works on SQL2000, where the code you posted will only work on sql2005 and up.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom