Updating statistics in sql server 2016

If you care about SQL Server performance you need to take SQL Server Statistics into account.

Statistics are lightweight and small objects which describe how data in SQL Server tables are distributed.

Presumably, if you don’t have a full-time DBA, you also don’t need the system to be available 24×7, so you can take the performance hit at night or on the weekend while all statistics update. But, if you are a DBA and you know how to write T-SQL, then you can absolutely write some code that programmatically looks at your statistics and decides what to update and what to skip.

Whatever method you use, just make sure your updates are scheduled to run regularly through an Agent Job, and make sure you have Auto Update Statistics enabled just in case the job doesn’t run and you don’t get notified for some reason (this would be Plan B, because it’s always good for DBAs to have a Plan B!

Using the Update Statistics task or sp_updatestats is easier if you’re not familiar with SQL Server and if you have the maintenance window and resources for it to run.

To be perfectly clear: if you’re a system administrator and want to update statistics, I’d rather you use this approach than nothing at all.

If I have a table with 2,000,000 rows, and only 5 rows have changed, I really don’t need to update statistics.The other method is to use UPDATE STATISTICS in a scheduled job.The UPDATE STATISTICS command can be run for individual statistics or for a table (updating all statistics for a table).This DMF tracks modifications, and also tells us how many rows were in the table when statistics were last updated, and the date statistics were updated. Sales Order Detail, and then look at the output from the DMF, you can see that the modification counter matches the number of rows I changed* for the Product ID index: USE [Adventure Works2012]; GO UPDATE [Sales].[Sales Order Detail] SET [Product ID] = [Product ID] WHERE [Product ID] IN (921,873,712); GO SELECT [so].[name] [Table Name], [ss].[name] [Statistic Name], [ss].[stats_id] [Statistic ID], [sp].[last_updated] [Last Updated], [sp].[rows] [Rows In Table When Updated], [sp].[rows_sampled] [Rows Sampled], [sp].[modification_counter] [Number Of Modifications] FROM [sys].[stats] [ss] JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id] CROSS APPLY [sys].[dm_db_stats_properties] ([so].[object_id], [ss].stats_id) [sp] WHERE [so].[name] = N'Sales Order Detail'; GO *You’re correct, I technically didn’t change Product ID to a new value, but SQL Server doesn’t know that.Also, there’s a foreign key on that column which is why I can’t easily change it a random number.