Maintenance Plan Reorganize Index & Update Statistics questions

Question

Hello, In SQL Server Management Studio for SQL Server 2014 using the "Maintenance Plan Wizard" setting up 2 tasks I have the following questions:

• [Reorganize Index] - Seems like the "Compact large objects" option saves some type of space but what impact does it have on performance or anything else when it runs or concerns afterwards?
• [Update Statistics] - Is it better in general to select 'Full scan' as Scan type or if specify number what value is general rule of thumb?
• Is it correct the order should be Reorganize first and then Update Statistics?

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

Welcome to Microsoft Q&A!

1.[Reorganize Index] - Seems like the "Compact large objects" option saves some type of space but what impact does it have on performance or anything else when it runs or concerns afterwards?

This option will deallocate space used for the index if the space is no longer required and therefore will free up space in your database. Please refer to ALTER INDEX (Transact-SQL) to get more information, see below:

LOB_COMPACTION = ON
1.Specifies to compact all pages that contain data of these large object (LOB) data types: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can reduce the data size on disk.
2.For a clustered index, this compacts all LOB columns that are contained in the table.
3.For a nonclustered index, this compacts all LOB columns that are nonkey (included) columns in the index.
4.REORGANIZE ALL performs LOB_COMPACTION on all indexes. For each index, this compacts all LOB columns in the clustered index, underlying table, or included columns in a nonclustered index.

2.[Update Statistics] - Is it better in general to select 'Full scan' as Scan type or if specify number what value is general rule of thumb?

For most workloads, a full scan is not required, and default sampling is adequate. However, certain workloads that are sensitive to widely varying data distributions may require an increased sample size, or even a full scan. I recommend that please refer to this link to get more information.

3.Is it correct the order should be Reorganize first and then Update Statistics?

Please refer to SQL Server Maintenance Plan Reorganize Index and Update Statistics Tasks to get more information about how to design the maintenance plan. Additionally, after reorganizing indexes it is reasonable to update the statistics as this operation does not update the statistics like the index rebuild operation.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

So do you have any LOB columns in your database? Assuming that you have, no one here can answer how much time it will take on your database, because we don't know the size of your database and state of your LOBs. If want to know, restore a backup to a test environment and run with option off and with the option on and compare. Restore the database between the tests, so that you have the same starting point.

As for statistics, this is a trade-off. FULLSCAN will give you better statistics, but it also take a much longer time to compile FULLSCAN stats, particularly for non-indexed columns. If you find that it takes too long time, go with sampled. Don't bother about setting a sample per cent, but go with the default.

As for the order, that does not matter for reorganize. But if you do index rebuilds, don't run the update stats job later, because index rebuilds will give you fullscan stats included in the price, so it's silly to replace them with sampled.

Also, consider exploring Ola Hallengren's solution, which is the standard solution for index and stats maintenance: http://ola.hallengren.com.

Answer 3

Thanks Erland and Carrin for the the informative replies, both were very helpful.