Consulting is all about sharing the best practices with your client and helping them tune then SQL Server. When we together on Comprehensive Database Performance Health Check there are always lots of tricks and tips we share. Today we will see how we can update Column Statistics via SSMS (SQL Server Management Studio).
If you have attended my real-world training and consulting engagement, I am sure you might be familiar that we do not focus on Index Fragmentation as that is not optimal. Often we can get performance from the server by updating the statistics. Statistics often do more wonders than index rebuild or index organizations.
SSMS before 18.6 – Bug
This feature was broken for a while in the previous version of SQL Server Management Studio (before 18.6). Every time when you navigate to statistics and update it via SSMS or try to generate the T-SQL script via SSMS, it was really doing nothing at all. Many people often thought when they clicked on OK that their statistics have been updated but that was indeed a bug in an earlier version of SSMS.
This was a big issue as many were under the wrong impression that their statistics have been updated but in the real world, it was not. However, this issue has been fixed in the recent release of SSMS 18.6.
Update Column Statistics in SSMS 18.6 – Fixed
Here is how you can update the statistics of the column via SSMS in SSMS 18.6 and onwards version. First, find the table inside the database of which you want to do statistics update. Expand the statistics folder and right-click on the statistics, go to properties.
Next click on the checkbox which says “Update statistics for these columns“. Now you have two options… Either click on OK or generate the script. Either will work fine and your statistics are updated.
If you are still using the previous version of SQL Server Management Studio, I suggest you upgrade to the latest version of the SSMS. I have blogged about it over here how you can do it yourself. Additionally, if you are a fan of a dark theme, here is how you can do it.
Reference: Pinal Dave (https://blog.sqlauthority.com)