Recently, for example, I watched a query go from taking hours to taking seconds just because we got a good set of statistics on the data.
The topic of statistics and their maintenance is not straightforward.
This is a hot topic in the SQL Server DBA community.
Some people say that a statistics update blocks transactional activity.
Could you please describe what are the benefits of using the Auto Create Statistics and Auto Update Statistics options?
I am also curious about how these statistics are automatically created and updated. How do the statistics increase the performance of your SQL Server query execution?
We described how to work with SQL Server statistics using SQL Server Management Studio options and T-SQL.They’re created at different points and, unless you’re creating the statistics manually yourself, they’re created slightly differently.The statistics on an index are created with the index.After populating the table with data its time to run the statistics update.While the statistics update is running, open a new session and execute the following query against sys.dm_tran_locks Dynamic Management View in order to analyze the locked resources by the statistics update.