SQL SERVER Management Studio – Update Column Statistics Via SSMS

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).

SQL SERVER Management Studio - Update Column Statistics Via SSMS ColumnStatistics-800x179

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.

SQL SERVER Management Studio - Update Column Statistics Via SSMS ssms186-1

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.

SQL SERVER Management Studio - Update Column Statistics Via SSMS ssms186-0

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.

SQL SERVER Management Studio - Update Column Statistics Via SSMS ssms186-2

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)

, ,
Previous Post
SQL SERVER – Input and Output Parameter for Dynamic SQL – Simple Example
Next Post
SQL SERVER – Counter Temp Tables For Destruction Not Working

Related Posts

Leave a Reply

Menu