2

While trying to improve the performance of few queries, we found via execution plan that there were lot of index / clustered index seeks.

Therefore:

First thing we did, was to check our Re-indexing and update stats job which runs weekly for this user DB ( Around 400 GB in size and is used 24*5). The job was running fine.

Later when we ran SP_Blitz, we came to know that auto-update-stats is disabled for this user DB. We expected this to be a possible cause and change it from false to TRUE (Auto update stats)

Also, per SP_blitz there are user-created statistics for this DB. When ran the query to check how many, we saw around 7K user stats out there.

So my questions would be

  1. setting the Auto update stats to TRUE would require a reboot or once changed I need to track the performance

  2. Should we consider dropping those user created stats or manually look into them one by one?

How should we proceed on this, please suggest, thanks!

  • 3
    1- What is the problem with finding lots of index seeks? That's typically a good thing. Are they inefficient seeks? 2- Custom user created stats could be just done by accident or on purpose, let's check the usage. It's relatively high level work though to pick your own stats and not let SQL Server do it, it does a good job making stats on the proper columns to check to pick which index or scan to use. – Ali Razeghi Mar 17 '15 at 23:41
  • Thanks@AliRazeghi, Agree, index seeks are good. But when i see the same highlighted with 72% operator cost on clustered index seeks in execution plan,wondered is something is not correct. Also is there a way i can check the usage of user created stats as mentioned above in your comment? – KASQLDBA Mar 19 '15 at 10:12
2

You have two questions:

1. Do you have to restart after setting auto update stats on? No.

2. Should you consider dropping the user created stats? If your statistics updates jobs (typically done with a maintenance plan or with Ola Hallengren's maintenance scripts) are taking longer than your maintenance window allows, then yes. SQL Server has to read the entire index (or table) to update each statistic, so you can end up with really long stats update jobs. If your maintenance window isn't a problem for you, then you can leave 'em as is.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.