SDU Tools: Update Statistics on SQL Server Tables
Having up to date statistics is critical for SQL Server choosing appropriate query plans. Poor statistics can lead to poor query plan outcomes.
Generally, SQL Server manages this well by itself. As a rough rule, most versions auto-update statistics when the number of changes is about twenty percent of a count of the number of rows in the table. (Some recent changes have slightly altered how this works but the ball park is pretty good).
There are situations though where this self-management isn’t enough.
In T-SQL, you can use the UPDATE STATISTICS statement to force a recalculation, and you can specify a sample percentage to determine how many rows SQL Server looks at while calculating the statistics. But the command is a bit painful to use when you want to do many tables, or all the tables in a schema, etc.
One of our free DBA and developer SDU Tools is designed to make this easier. In the image above, you can see it used with a selection of tables included. In this case, it’s looking in the WideWorldImporters database, and processing all tables called Cities or People in all schemas. It is using a sample size of 30 percent.
Here’s another example, where all tables in a database are being processed:
Note that the tool outputs the commands that it is executing into the Messages tab so you can see what it did.
You can see it in action here:
For more information on joining SDU Insiders to get our free tools and other resources, please visit here:
http://sdutools.sqldownunder.com
2018-04-25