Post reply

Microsoft Recommendations for Update Stats?

  • November 21, 2024 at 3:32 pm

    #4487329

    I have an application team that is insisting on daily (and for some, weekly) jobs for UPDATE STATISTICS WITH FULLSCAN on all their databases. These jobs were created years ago by a previous DBA team.

    The jobs are running very long, into business hours often, one database's job is running 1.5 days, and butting heads with our corporate-implemented index rebuilds / stats updates with sampling, and sometimes butting heads with the application's own other processes.

    To be clear, I had nothing to do with setting up any of the indexing / statistics jobs. They were set up before I joined this team. But now I am responsible for fixing everything and removing the blocking. As I asked the app team for permission to disable all the old jobs, there was a lot of panic. Apparently back in 2018, several databases crashed hard and when they engaged Microsoft on the ticket, they got yelled at for only sampling stats instead of regularly running FULLSCAN. MS DBAs chewed everyone out for not following MS recommendations and white papers regarding this issue.

    So... I can't seem to find these white papers via Google. When I search, I find Books Online entries and misc. forum threads that don't quite answer the question I'm about to ask.

    Does MS recommend regularly updating stats with FULLSCAN? If so, can anyone point me to these white papers?

    All I see in BOL is "run with sampling except for circumstances where a specific workload may need FULLSCAN" with no details on exactly what that statement might mean.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • November 21, 2024 at 4:39 pm

    #4487354

    Hello Brandy,

    MS Updating statitics

    https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver16

    MS When to update:

    https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#UpdateStatistics

    Normally the default autoupdate will do unless in very specific cases ( you can find non-ms articles about this)

    Even then you would narrow it down to the culprits instead of full scan all tables

     

  • November 21, 2024 at 5:15 pm

    #4487365

    Stop rebuilding indexes.

    Microsoft's documentation states:

    Index maintenance decisions should be made after considering multiple factors in the specific context of each workload, including the resource cost of maintenance. They should not be based on fixed fragmentation or page density thresholds alone.

    https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

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

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