In SQL Server, our Auto Update Statistics algorithm still relies on the default 20% modification on a given column to trigger update statistics. However different customers have different workloads and for some this threshold value triggers Update stats less frequently (e.g SAP databases) while for some workload the default 20% setting triggers Auto Update Stats more frequently.

To resolve the issue of less frequently updated stats TF 2371 was introduced as described in detail by Juergen Thomas’s blog

But some customers don’t like Trace Flags since it is considered not a best practice to run a product with Trace Flags.

Further According to me customers or more specifically DBAs/Application Vendors are the best judge to decide when update stats should be triggered and hence a configuration setting should be introduced to allow the customers to configure the thresholds for Auto update stats.

So I filed this suggestion as Product Feedback in Connect site. So if guys agree with me on this. Make sure you Vote for this feature in the following link

https://connect.microsoft.com/SQLServer/feedback/details/714770/need-sp-configure-parameter-and-database-properties-setting-to-control-the-threshold-to-trigger-auto-update-stats#details

Parikshit Savjani
Premier Field Engineer

Similar Posts

|

I killed Reindexing Job and now it is in KILLED/ROLLBACK State for more than 9 hours

  One of our Customers ran into this issue where in he killed a Reindexing Job and following that it went into KILLED/ROLLBACK State and was in that State for more than 9 hours. First of all we would not recommend you to kill Job like Reindexing or DBCC CHECKDB which performs internal operations. However…

|

SQL Server Datafile maxsize reported incorrectly

Recently we encountered and filed a benign reporting BUG which reports the maxsize of the data file incorrectly. If you create a database & then alter the initial size of the database to be greater than maxsize & then query sys.database_files, sys.master_files, sysaltfiles the maxsize appears to be incorrectly reporting the older value of maxsize….

Powershell script to script all the Jobs from the given instance of SQL Server

Following is the powershell script to script out all the Jobs in one go. param($sqlserver) [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | Out-Null $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver $srv.JobServer.Jobs | foreach {$_.Script()} You can copy the above code in a notepad and save it as .ps1 file in some location on server. Next from the Object Explorer in SQL Management…

| |

SQL Server: Does Parallel Plan for rebuild index impact the fragmentation of index?

The title for this post might sound ironical since rebuild of the index is used to remove the fragmentation of the index so how does rebuild of index affect the fragmentation of index? Well, this is what I would like to explain in this blog post. Generally, for rebuilding of indexes (alter index … rebuild)…

|

Fragmentation should not be the only factor which decides the Rebuild Index

Until now I was under the impression that fragmentation in the Index is the only factor which decides whether we should rebuild the index. As a rule of thumb we always recommend that when the avg_fragmentation_in_percent in the view  sys.dm_db_index_physical_stats  is between 5 and 30 We should reorganize the index and when the fragmentation is…

Leave a Reply

Your email address will not be published. Required fields are marked *