deleting autocreated statistics?

Question

deleting autocreated statistics?

nawillia

SSCrazy

Points: 2401
More actions
August 22, 2011 at 8:22 am

#256110

Is there some best practice of identifying unused autocreated statistics?
I trying to update the statistics for my databases, but I only want to update the stale statisics that are left after I run the index defrag process.
But there are a ton of auto generated statics and I don't want to update them if they were some 1 time generate statistic that is never being used.
what is the general thoughts on deleting autocreated statisics?
Can a you write a script to find used statisics?
Does leaving autocreated statisics have any negative effects on your system?
thanks

Viewing 15 posts - 1 through 15 (of 39 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I've never seen a maintenance window so tight that you had to think it through so hard.

Granted I have only a small db to maintain here but even updating 100% of the stats with fullscan takes only a few minutes (25 Gb db).

If you really don't have time you can go the reactive way. Which mean just let the server auto-create / update stats.

Then once you have plans that go wrong because of stale stats update just the ones you need. Then see how long it takes before the stats go stale again. Then tells you how often you need to update those particular stats.

As for removing "unused" stats I really don't see the need. They take virtually no room and if they are not used then they likely won't need to be updated either so I wouldn't waste my time trying to hunt them down.

Lowell SSC Guru Points: 323482 More actions · Answer 2

my advice is you are looking for a problem that does not exist. leave the statistics alone, and update them often.

statistics are used by the system to find the data faster. delete statistics, and if the Query engine decides it needs them, it will create/recreate, and then do the work it was supposed to...you'd introduce an extra bit of work when it's not necessary.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Statistics are very small. By & large they won't get in the way and usually help. Unless you're seeing an issue, I wouldn't mess with it.

That said, sure, you can use the system generated name as a mechanism to drop them, but then some of your queries, maybe even a lot of your queries, will be slow for a little bit while the stats that they needed get recreated again.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

nawillia SSCrazy Points: 2401 More actions · Answer 4

thanks for the responses. this was the kind of information I was looking for.

I didn't want to drop any of the statistics unless they were not used.

I don't know if i've decided to run the command to update the stale statistics or just let the autoupdate take care of them.

and when i say stale, i mean they were not updated as of the last index rebuild/reorg.

I think for now I will just leave them as is until there is a problem.

I do know that earlier in the year, there was a problem and the developer/admin used some join hints to fix the problem. Back then I was thinking it was probably stale statistics but I had just joined the team and was not working on that particular project. By the time I heard about it, the fix was already being used.

Now that i'm on the project and i'm set up the index rebuild/reorg using fragementation and # of pages to run weekly. Prior to that I think they ran index defrags once a year -mainly cause they could get by with it.

So I was just trying to determine if I should mess around with those autocreated statistics -- but i'm hestitant because if there are join hints hidden in code then that could cause some havac too (right?).

thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

The real problem is the join hint.

I've never heard of <auto>-update stats causing problems. I'm not saying it's impossible, but on the very improbable side.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

Ninja's_RGR'us (8/22/2011)
The real problem is the join hint.
I've never heard of <auto>-update stats causing problems. I'm not saying it's impossible, but on the very improbable side.

I have heard of it causing issues, but we're talking 10s of terabyte systems, not your run of the mill system. Haven't seen it myself though.

But I agree. Let the auto-update stats handle most of them.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Grant Fritchey (8/22/2011)
Ninja's_RGR'us (8/22/2011)
The real problem is the join hint.
I've never heard of <auto>-update stats causing problems. I'm not saying it's impossible, but on the very improbable side.
I have heard of it causing issues, but we're talking 10s of terabyte systems, not your run of the mill system. Haven't seen it myself though.
But I agree. Let the auto-update stats handle most of them.

I was going with the let's help the 99.99% case that will read this in the future and forget about the bleeding edge case(s). :-D.

Thanks for confirming my understanding of that point!

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

There is one problem with Autocreated statistics. It is rare but can happen and has happened in our environment.

Let us say we have a table with 6 columns with one PK. No other indexes. The stats get created on some of these columns. Now you create an index on one of these columns. Now you have overlapping stats and can cause bad query performance. I am not sure I explained it properly but in nutshell overlapping stats can have bad query performance. One thing that can solve the issue would be to drop the overlapping statistics.

-Roy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Roy Ernest (8/22/2011)
There is one problem with Autocreated statistics. It is rare but can happen and has happened in our environment.
Let us say we have a table with 6 columns with one PK. No other indexes. The stats get created on some of these columns. Now you create an index on one of these columns. Now you have overlapping stats and can cause bad query performance. I am not sure I explained it properly but in nutshell overlapping stats can have bad query performance. One thing that can solve the issue would be to drop the overlapping statistics.

I don't understand. Got a demo script to explain / prove this?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 10

Roy Ernest (8/22/2011)
There is one problem with Autocreated statistics. It is rare but can happen and has happened in our environment.
Let us say we have a table with 6 columns with one PK. No other indexes. The stats get created on some of these columns. Now you create an index on one of these columns. Now you have overlapping stats and can cause bad query performance. I am not sure I explained it properly but in nutshell overlapping stats can have bad query performance. One thing that can solve the issue would be to drop the overlapping statistics.

Do you mean that the existence of the stats could interfere with the selection of the index by the optimizer? Or is it something else entirely?

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

Yes, the existence of the stats for that column interferes with the selection of the index by the optimizer. I was banging my head with this issue one year back. I had a query that was performing very badly. The table underlying in this query had a datetime column that I added an index since it needed it.

But what ever I did, it would not use the index unless I gave a hint. When I gave the hint, the execution time dropped down. I was asking about this on twitter and Kendal Van Dyke (@SQLSoldier)told me that it could be because there is already an existing Stats on that column. If we drop the column it will start using the index. He gave me a query to find the Overlapping stats and it proved correct. As soon as the stats was dropped, the optimizer picked up the index.

-Roy

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 12

Roy Ernest (8/22/2011)
Yes, the existence of the stats for that column interferes with the selection of the index by the optimizer. I was banging my head with this issue one year back. I had a query that was performing very badly. The table underlying in this query had a datetime column that I added an index since it needed it.
But what ever I did, it would not use the index unless I gave a hint. When I gave the hint, the execution time dropped down. I was asking about this on twitter and Kendal Van Dyke (@SQLSoldier)told me that it could be because there is already an existing Stats on that column. If we drop the column it will start using the index. He gave me a query to find the Overlapping stats and it proved correct. As soon as the stats was dropped, the optimizer picked up the index.

Cool stuff. Thanks for bringing it up.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I'd still like a demo script if you have one laying around :Whistling:

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 14

I do not have a demo script to reproduce the performance problems. But I have the script that Kendal gave me to find the overlapping stats. If you want that, I can put it here.

-Roy