2

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.

5

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.

  • @VladimirBaranov I'd say that VACUUM FULL equivalent would be shrink + index defrag. – Marcin S. Oct 4 '16 at 7:28
1

Effectively you are talking about CREATE STATISTICS in SQL Server. You are able to manually create them, however, by default in databases (unless you change the configuration on the database, or the model database) then statistics will be automatically created by SQL Server when it needs to.

Statistics are used by the SQL Server query optimizer to evalute and choose execution plans. They are kept up to date by periodic automatic updates (by default) which are done based upon the number of changes within a given table, or can also be updated manually.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.