SQL Server – Analyze, Vacuum Equivalent?

sql serverstatistics

In Postgres, ANALYZE collects statistics about the contents of tables in the database, and store it. These statistics are used to determine the most efficient query plan, in PG. Running analyze command improves db's performance. I want to know if there is something similar in SQL Server.

Does SQL Server have commands equivalent to Postgres' Analyze and Vacuum?

If SQL Server doesn't have the exact equivalents, are there any similar commands?

If those commands do have SQL Server versions, please explain them or post links to useful documentations.

Best Answer

As far as I understand, rough equivalent of ANALYZE would be UPDATE STATISTICS.

Updates query optimization statistics on a table or indexed view. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.

Quite often automatic updates of statistics is enough, but I came across few cases when I had to do it manually to get a good query plan. One common case is when rows are added to a large table and the range of values for new rows lies outside the existing statistics histogram, the so called Ascending Key Problem.

Rough equivalent of VACUUM FULL would be commands that shrink database.

Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can be deallocated and returned to the file system.

In many cases shrinking a database is a bad idea.

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

When is it OK to shrink a Database?

The feature is there, but it should be used to address some very specific problems.

As @dezso pointed out in the comments, simple VACUUM command only marks pages as free for reuse in the same table, it doesn't compact the file. SQL Server does it automatically via the Ghost cleanup background process. Actually, it is possible to turn it off. So, it looks like this Ghost cleanup process is a rough equivalent to a simple VACUUM.

Related Question