Is it OK to drop the User created Statistics

Udham Singh 96 Reputation points
2023-06-06T07:41:05.2733333+00:00

Hi,

We have around 4.5K stats in a DB (DB size is 1TB, SQL version 2016 STD), so when we are updating the statistics as a part of our weekly maintenance plan, it is taking around 24 hours (WITH SAMPLE 50 PERCENT) to complete. So we modified the scripts to make use to parallelism (Update stats WITH FULLSCAN MAXDOP 8), now job is taking around 19 hours. But still not good enough.

Is there any thing we can do more to improve the job execution time ?

Also what if we drop the USER created STATISTICS (updating the user created stats taking 12 hours) ? Would it be OK ? How much performance impact we should anticipate in query execution due to this.

User's image

NOTE: Auto create statistics and auto update statistics are enabled.

--

Udham Singh

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,710 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2023-06-06T11:54:23.28+00:00

    There is no one here that can answer your question about user statistics, since we don't know they were created in the first place.

    But generally, user-created statistics are rarely called for, since SQL Server creates statistics automatically when needed. The cases where user-created statistics makes sense I can think of:

    1. Multi-column statistics (but you only get density for the second, third and other columns).
    2. Filtered statistics.

    So, yes, if these statistics were created on a whim with a real purpose, you could drop them.

    But there are other alternatives. Updating a non-index statistics with FULLSCAN requires a full scan of the clustered index, and it's a table scan for every statistics. Index statistics are cheaper to update FULLSCAN, since in this case the index can be scanned, and the index is smaller.

    So you could consider

    UPDATE STATISTICS tbl WITH FULLSCAN, INDEX
UPDATE STATISTICS tbl WITH COLUMNS

    If you identify that for a specific non-indexed column you need fullscan statistics, you can handle that separately.

    I should add that in many places, they update all statistics with the default sample rate and are happy with that. If you can identify which indexes for which you really need fullscan indexes, you can save a lot of computer resources. Then again, determining which indexes you need this for will consume considerable human resources.

    By the way, a sample rate of 50 per cent is quite useless. It takes about the same resources as FULLSCAN and sometimes even more.

    0 comments
  2. AniyaTang-MSFT 12,316 Reputation points Microsoft Vendor
    2023-06-07T05:33:17.1966667+00:00

    Hi @Udham Singh

    I found this link: https://www.brentozar.com/blitz/user-created-statistics-in-place/. It and the jump links in it explain some questions about statistics, and you can use it as a reference.

    If you want to delete all user created stats, you can check this link: https://blog.sqlauthority.com/2018/01/28/drop-user-created-statistics-sql-server-interview-question-week-158/.

    Best regards,

    Aniya

    0 comments