Oct 4, 202335. 例えば、10 % 以上 30 % 未満であれば再構成 (Reorganize)、 30% 以上であれば再構築 (Rebuild) するとすれば、. index_type_desc AS IndexType, indexstats. Reorgs require statistics run manually (exec sp_updatestats) From the description it sounds like a less intrusive operation and only less recommended because it's an older, slower process during which the DB will be less responsive. One option is to execute an index rebuild, which will move data around on pages. Online rebuilds are online available on the Enterprise version of SQL Server and your version probably isn't, so just set the operation not to be run "Online" on you Wizard. On this MSDN Page it says if you should reorganize or rebuild based on the amount of fragmentation:. When you reorganize the index, you go through the existing index, cleaning up blocks for deleted records etc. Shrinking is a wasteful operation. An index reorganize can be interrupted and the worst that will happen is that a single page move operation is rolled back. These scripts are widely tested in the community and are much flexible so that you. Index rebuilding and reorganizing are the two methods to maintain indexes and improve database performance. If your indexes are fragmented : If index has less than 1000 pages - do not perform any index maintenance operation. The SQL Server Maintenance Plan reindex task is unintelligent, and will do a full rebuild on every index whether it needs it or not. Select * from sys. dm_db_index_physical_stats ‘ Dynamic Management View (DMV). Answers. First, we will start with the "Maintenance Plan Wizard":The rebuild command will defragment all those intermediate pages. In this case you can perform index reorganize and index rebuild operations only on those partitions that really need it, thus making it more efficient by reducing the time and resources needed for this maintenance. 3. This blog entry I came across a while back will explain it much better than I can. In this one case, it happens to make the query. Connection. From stackoverflow page ( How Often should the indexes be re-build in sql-server DB?) i got this query : SELECT t. Jonathan’s right in that fragmentation changes the query plan. If you use Ola Hallegren's scripts then you can set the thresholds for a rebuild/reorganise (say over 50% fragmentation for a reorganise, over 80% for a rebuild) and this can run overnight out of hours. There are two main ways to defragment a Heap Table: Create a Clustered Index and then drop it. You can still run the index reorg/rebuild as part of your maintenance scripts. The discussion of columnstore indexes thus far has focused on clustered columnstore indexes that target a primarily OLAP workload. I used this approach to improve performance and it worked perfectly for a long time. Sorted by: 5. Quick explanation: An online Rebuild, rebuilds the indexes on the tempDB making it available to the queries while it is being rebuilt. : 15% com. You can always update statistics on their own. The guidance from Microsoft is that indexes be rebuilt if the fragmentation is over 30% and reorganized if the fragmentation is between 5 and 30%. The. The script will do a reorg if fragmentation goes over 5 %. The real moral of the story is that it’s really hard to build demo code that effectively conveys the point you want to make. On large tables, that may be a while and not frequent enough. Failing that (ie Standard Edition etc), look at re-organizing indexes - these will keep them online. Under Select a page, select Options. Do not use REORGANIZE. 1 Answer. Additionally, after reorganizing indexes it is reasonable to update the statistics as this operation does not update the statistics like the index rebuild operation. Index Rebuild operation first drops and then recreates the index. And that frequency can be lowered by using carefully selected fill factor. This blog entry I came across a while back will explain it much better than I can. To create a new maintenance plan, right click on Maintenance Plan and select New Maintenance Plan and the following window will open. @LeeWalters shrink is done, and yes, due to disk issues, not possible to add drives, that's why, in addition, db is populated with blob data, so the question is more from the index fragmentation side. I made them up. sp_start_job N'NAME OF JOB'. However, a new database server backed with Intel PCIe SSDs is showing the opposite of what we expect. ALTER FULLTEXT CATALOG [CatalogName] REBUILD. All they do is waste space and resources. This is a best practice for performance when you rebuild or reorganize indexes. May be index was not there, may be you were just rebuilding the index. Right-click the index for which you want to set the max degree of parallelism and select Properties. Yes -. So far, all good. MSDB is utilized by database mail, sql. The answer is: it depends. Rebuild of indexes is the prime job of a DBA as heavily fragmented indexes can degrade query performance and cause your application to respond slowly. ;WITH cte AS ( SELECT object_id, index_id, partition_number, rows, ROW_NUMBER () OVER (PARTITION BY object_id, index_id, partition_number. Expand the Indexes folder. Hence, we have configured and tested the Rebuild Index task. For rows that contain LOB columns, the normal page will contain a reference to the LOB page. 1. 2. ALTER INDEX CCI_TEST on DBO. 1. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK. One can also reorganize an index using. 2. #1162454. I think there is another way to rebuild fragmented index, You can create an sp and scheduled it via SQL Server Agent or via task scheduler. It can be done with online option in enterprise edition. Whether you rebuild or reorganize indexes depends on the following guidelines: avg_fragmentation_in_percent value Corrective statement. Next the New Job window will open. . RCSI utilizes tempdb heavily but gives performance boost. DBCC DBREINDEX - use to re-index with older versions (SQL Server 2000 and earlier) sys. Dec 19, 2021, 9:55 PM. In the previous SQL Server versions, using the Rebuild Index and Reorganize Index Maintenance Plans tasks to fix the database indexes fragmentation issue is not highly recommended. Largest table has around 500. Check total_pages in sys. > 5% and < = 30% ALTER INDEX REORGANIZE. ApexSQL Defrag tool is a 3 rd party SQL index defragmentation tool that you can easily use to perform number of operations related to the SQL Server indexes. So now once you have identified the high fragmentation level in your database, which could. Index should be rebuild when index fragmentation is great than 40%. Check indexes from the index grid and click desired defragment operation from the ribbon. ALTER INDEX ALL ON [table_name] REORGANIZE; However, if you want to rebuild all the indexes on the table, you can run the following. This tip will explore two features to speed up SQL Server index and statistics maintenance. So you should explicitly set a deadlock priority, and perhaps a retry loop, to enforce the behavior and document it. index_id > 0 -. record_count > 0 AND o. From this tab, select a SQL Server instance in the server explorer on the left and choose database (s) as a target of index operation. Once you select that option it will bring up the following screen. Add a comment. That's why as a rule of thumb, for fragmentation greater than about 30%. SQL Server Database Engine. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics. SQL Server 2005 Index Rebuild/Reorganize. The performance benefit may not be noticeable for indexes that are used primarily for seek operations. During index optimization, nothing accesses the server other than Red Gate SQL Monitor. dm_db_index_physical_stats DMV). Users(DisplayName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 1); Those parameters mean: ONLINE = ON means you’ve got the money for Enterprise Edition. This. The index uses the main filters on the sales table from the time dimension, i. For now, I have just turned off the weekly index reorganize task which is not really a good long term solution. Until now I'm using the Maintenance Plan Index rebuild task to. We can also run the task by running the corresponding job in SQL Server Agent. The import process should be reworked to bulk-load one table at a time. Starting with SQL Server 2016 (13. If the underlying table is a clustered index, then for the CI and all non-clustered indexes both rebuild and reorganize are available. Index should be reorganized when index fragmentation is between 10% to 40%. SELECT * FROM sys. Problem. If you truly want to REBUILD, and you want to do it for ALL indexes on a given table, then the command would be (straight from the official docs that npe pointed you at): For more instructions see the official docs. The link points to SQL Server 2014 docs, but the syntax should work on 2005 and 2008 as well. 1. index rebuild/reorganize frequency. USE AdventureWorks; GO ALTER INDEX ALL ON Production. I know the sql to perform this action on all indexes in a table is alter index all on table_name reorganize; But I only want to rebuild or reorganize if . Index rebuilding and reorganizing are the two methods to maintain indexes and improve database performance. ALTER INDEX REORGANIZE statement is always performed online. Designing efficient indexes is paramount to achieving good database and. If you read complete article it was trying to point out the commands or operations in SQL Server which would require additional disk space and others that would not. You could also refer another query which may be helpful to you. Surly not, the Maintenance Plan was created in the current Server itself. To rebuild or reorganize indexes manually, head over to the Fragmentation tab. Below is an example of this technique, where the @PartitonNumber value can be changed in a restart scenario. Regards. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a specific table in a specific database. Here is MSDN article on this: Reorganizing and Rebuilding Indexes. Rebuilding basically creates an entirely new copy of the index, and is much more effective at reducing fragmentation - but this comes at a cost, both in terms of time and disk space. Right-click Maintenance Plan and select Execute. The script will do a reorg if fragmentation goes over 5 %. With SQL Server 2005 you have the option of rebuilding or reorganizing your indexes. Cancelling / Stopping ALTER INDEX REORGANIZE. As data is added to the table, the free space fills because the fill factor isn't maintained. Solution. it will reorganise it. . But commit your whole transaction before reorganize. 3. x)) e Banco de Dados SQL do Azure, REORGANIZE executa as seguintes otimizações adicionais desfragmentação online: Remove fisicamente linhas de um grupo de linhas quando 10% ou mais linhas foram excluídas logicamente. Reorganizing or rebuilding a cluster index does not have any impact on the non-cluster indexes in SQL Server 2000 SP4 or above, or any version of SQL 2005 or. Under Select a page, select Options. Reorganize: The Reorganize operation is an online operation, and moves the closed row groups into the Columnstore. Reorganize Index Log Generation Now here is the final demo of Reorganize the Index, it generates lots of Transaction Log records, a very simple demo, we will capture transaction log using sys. Reorganize all indexes on the queue internal table. Using above query, you could also query the progress of backup,restore,dbcc command and so on. We use SQL Server Index Rebuild and Reorganize operation to remove fragmentation level of the indexes. Create a execute sql task and schedule it through sql agent . I would like to rebuild and reorganize my indexes, but our system works 24/7. ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] ON [Sales]. A clustered index exists, along with 3 non-clustered indexes. The more the fragmentation you need to rebuild if its less you can reorganize. Creating or dropping the table’s clustered index causes all its nonclustered indexes to be rebuilt. Because I was blamed for the slow SQL Server mainly because I. Rebuild Index. Here are the steps to reorganize indexes using the SSMS: In Object Explorer, expand the database that contains the table on which we want to reorganize an index. Deletes catalog and creates new. By allowing an index to be rebuilt dynamically, indexes enforcing either PRIMARY KEY or UNIQUE constraints can be rebuilt without having to drop and re-create those constraints. Less than 10% - then do nothing; between 10% and 30% - then do a reorg; more than 30% - then rebuild them. You can read more on rebuilding indexes here . x) and in Azure SQL Database, we recommend using ALTER. Reorganizing the indexes: ALTER INDEX ALL ON dbo. We can however get it back to a compressed segment by issuing the following command we saw previously. December 8, 2009. Partitions can be rebuilt individually. Index should be reorganized when index fragmentation is between 10% to 40%. set @SQL = 'alter index ' + QuoteName ( @IndexName ) + ' on ' + QuoteName ( @TableName ) + ' rebuild';. The first part of this tip focuses on SQL Server Enterprise Edition to reduce the duration for index maintenance for off-line rebuilds. DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. You can also slow down fragmentation by inserting data in the order of the index. Reorganizing only works on the leaf pages. Reorganizing should be used at lower (<30%) fragmentations but only rebuilding (which is heavier to the database) cuts the fragmentation down to 0%. 35. When you double click on this task the following screen appears. This schema modification lock blocks all other concurrent access to the table, but it is only held for a very short period of time while the old index is dropped and the statistics updated. Use Index reorganize option when rate of index fragmentation is b/w 10% to 40%. Both create/rebuild of columnstore index is an OFFLINE operation at this time but you can defragment columnstore index ONLINE. Reorganize all index in SQL Server. 1. Yes. Right-click the index that you want to rebuild online and select Properties. A table may only have one columnstore index on it at a time, regardless of whether it is clustered or nonclustered. Please post more details like how are you running the reorganize via TSQL or SSMS or SSIS. The following example returns all statistics for all tables and indexes within the instance of SQL Server by specifying the wildcard NULL for all parameters. This removes fragmentation, reclaims disk space by. Here's another script to add to the list. こんにちは。開発部基幹SREチームの廣瀬です。 弊社では、システムの一部にSQL Serverを使用しています。 本記事では、SQL Serverにおけるインデックスのメンテナンス方法である再構成と再構築について、それぞれを実行した場合のクエリ性能の比較結果をご紹介したいと思います。 比較を実施. The database is using the Simple Recovery model. Rebuilding an index can be either an online operation or an offl ine operation, depending on several factors. The MSDN page says this about reorganizing and rebuilding indexes: The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. In the Description box, briefly describe your maintenance plan. . In SQL Server, the ALTER INDEX statement is used to modify an existing index. Next Steps This is a simple base script that could be modified into a stored procedure and also allow you to pass other parameters such as doing an index. Method 2: Set Change_Tracking to Manual, by using the following command: ALTER FULLTEXT INDEX ON table_name set Change_tracking = Manual Then, create SQL Server jobs to spread. Cancelling / Stopping ALTER INDEX REORGANIZE. You can also change this threshold via parameters. SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script. When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. LOB_COMPACTION Compacting large object (LOB) data can reduce the disk space used. This is great if you want to release some space quickly. NAME 'Index name', ips. . ALTER INDEX [myIndex] ON [dbo]. Executing this query requires the VIEW SERVER STATE permission. It's not documented to have a particular deadlock priority, and it does make data changes. Also, it is possible to do it with the help of SSMS (SQL Server Management Studio): Choose the database and then the table where you want to Reorganize and. This means every time we need to scan the. Prior to this index failing to reorganize, a different index (a non clustered unique index) on this table had this same error, and dropping and creating it fixed that one, but not this one. SQL Server provides sys. Again you most likely have some big indexes that are fragmented. Er…. Then, drag and drop Rebuild Index Task into the maintenance plan designer. With the following command:. Quote from "Microsoft SQL Server 2000 Index Defragmentation Best Practices": "Fragmentation affects disk I/O. By using the Rebuild Index task, a package can rebuild indexes in a single. August 1, 2013 at 3:52 pm. 0. However, none of the other metrics appear to indicate that the system is. Use the Reorganize Index task followed by the Update Statistics task; If you have the Enterprise Edition of SQL Server, the Maintenance Plan Wizard offers a Keep index online while reindexing option, which means that the index will continue to be available to users even while it is being rebuilt. the. ALTER INDEX index_name ON table_name REORGANIZE OR. It checks the SQL Server licences, if Enterprise it will rebuild the index online else it. The purpose is to reduce the size of database and increase the db performance. Particularly if you are in full recovery. Reorganize an index For rowstore indexes, the Database Engine defragments only the leaf level of clustered and nonclustered indexes on. Yes, just the table and any query that tries to access that table. ALTER INDEX index_name ON table_name REBUILD -- REORGANIZE. Rebuilding an index can be either an online operation or an offl ine operation, depending on several factors. In it, enter the Job name, owner, optionally Category. Update Statistics. > 5% and < = 30% ALTER INDEX REORGANIZE. Or if it swapping places of two pages. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. When you. it will reorganise it. Index Rebuild : This process drops the existing Index and Recreates the index. The SQL Server Database. If we have 10-30% fragmentation a REORGANIZE is performed, and a REBUILD is performed when we have greater than 30% fragmentation. Reorganizing the indexes will take less time, and less effort from the SQL server thus they can be done in a weeknight type of instances. Expand the Management folder. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. It drops index entirely and creates it from scratch. This allows you to interrupt the rebuild and preserve the work already done. Create a job to run your index reorganize ('Reorganize'). . Expand the table on which you want to reorganize an index. 1. Optimize SQL Server non-clustered indexes and queries by considering index fields, compound indexes and SQL Server statistics. From Rebuild or Reorganize: SQL Server Index Maintenance by Kendra Little: Short version: Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. I'd just add that adding the line PRINT @sql after the SET @sql =. Does it mean reorganize the file contents maybe. Rebuild the NCIs. Give it a go with its fully functional 30-day free trial. dm_db_index_physical_stats fincation have index_id which display heap and index informatiob. alter index all on table_name reorganize; But I only want to rebuild or reorganize if fragmentation percentage on each index is between a certain range. Default SQL Server value is 0 or 100%, which means that no free space should be left on each page. I have maintenance job on SQL Server 2012 Enterprise Edition that runs daily to check index fragmentation and reorganize or rebuild the index based on the percentage of fragmentation. dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL); GO. To fix the fragmentation either rebuild or reorganize the index on the table. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically. 23%. 11. Online index rebuild higher fragmentation on intermediate level. Expand Databases, and then expand the database that contains the full-text index. Msdn says: And also, avg_fragmentation_in_percent value says percentage of logical fragmentation (This is the percentage of out-of-order pages in the leaf pages of an index. reorganize pages. The Reorganize Index task encapsulates the Transact-SQL ALTER INDEX statement. ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] ON [Sales]. Rebuild Index. Depending on how many rows actually fit on the page, your mileage may vary. Since we are asking SQL Server to rebuild the clustered index on a quite large table twice, it will take a few. Reorganize or rebuild an index SQL Server Management Studio. : 15% com. Microsoft best practice says that you should generally not do a rebuild operation unless fragmentation is over 30%. 1 Answer. This makes it more likely that some other session is blocked by your session, and that you may, in turn, become blocked by a lock held by that session. Therefore you do not need to update statistics OR reorganize an index if it gets rebuilt. The rebuild can be resumed later after the previously completed partition number. This opens the Toolbox and the maintenance_plan_name [Design] surface with the Subplan_1 subplan. Which is the best method to reorganize sql server database. 2. Use the Rebuild Index Task dialog to re-create the indexes on the tables in the database with a new fill factor. allocation_units. Best regards,. Just kidding – although the evidence does point to that. He has the best practices coded into his scripts, so it should serve you well. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. For example, one of the indexes with a page_count of 967 has a fragmentation percentage of 98,98%! To me, it seems worth rebuilding that index! I did, and afterwards, the fragmentation was 0%. 2. For a turnkey solution, you may refer to Microsoft MVP Ola Hallengren's SQL Server Index and Statistics Maintenance. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance. TEST REORGANIZE WITH. So I ran reorganize indexes and that took care of the fragmentation problem. some action needs to be taken to remove the ghosted records. If you have a choice, then REBUILD. @OnlyModifiedStatistics = 'Y'. A SQL Server DBA myth a day: (29/30) fixing heap fragmentation. 0. Less than 10% - then do nothing; between 10% and 30% - then do a reorg; more than 30% - then rebuild them. For more information, see Data Types (Transact-SQL). And if the reorganize is a deadlock. The second part of this tip is intended to reduce the duration for update statistics as it pertains to both. On a small table just leave it alone. Script to reorganize all indexes on all tables in user databases. ALTER INDEX statement can be used to change the properties of an index, such as its fill factor or sort order, or to rebuild or reorganize the index. Designing the SQL Server Reorganize Index Task. From a transaction log standpoint, reorganize index generates a lot more small transactions and can lead to substantially more resource-consumption than a rebuild. Should be straight forward. Below the last two rows that displays the fail: Source: Reorganize Index Executing query "ALTER INDEX [PK_xxTableName] O. 7. The issue is that doing this manually is very tedious as we have 75+ tables, also I noticed the indexes seem to get fragmented very quickly. index_id > 0 -- Indexes. ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] ON [Sales]. Also trying to avoid logging to transaction log and log. Method 2: Manually create rebuild job using scripts and scheduled as. But no help, the rebuild itself not working. SQL Server Index Rebuild and Reorganize Script. Options. The fix is to update all rebuild index scripts with explicitly ONLINE option on ON and to make sure it will run on a SQL Server version that supports this (Enterprise and Developer editions) and that will rebuild online only allowed indexes. One solution is to deploy a faster I/O subsystem, where page splits would be less of an issue. Reorganize/Rebuild SQL Server database indexes using ApexSQL Defrag ApexSQL Defrag is a 3rd party tool made specifically to automatically fix index fragmentation in SQL Server databases. ApexSQL Defrag is a powerful application used for scheduled analyzing and defragmenting of SQL Server database indexes. Last night I killed the REORG on the clustered index after almost 6 hours of execution. Expand Tables. Note: You cannot reorganize an index (primary key, or unique key) that has an Allow Page Locks. SQL Server, MVP, M. In SQL Server, you "might" run into issues with "updating primary key". 3. the year and month columns. It doesn’t work on the intermediate pages between the root and the leaf. indexes. Thanks for your. Indexes with small number of pages (<1000) will usually. If you what you are saying is true, even reorganizing the. e. Index maintenance in my opinion is not one of them. We need to drag and drop tasks from the Toolbox (to open the toolbox use Ctrl + Alt + X) into the gray workspace area on the bottom as shown in the below screenshot. > 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*. For more information about this enhancement, check the SQL Server 2016. Remarks. The SQL Server instance should have enough memory available to hold the largest table and perform the largest nonclustered index sort at the same time. This will obviously alleviate both internal and external fragmentation but is a more heavy weight operation and by default causes the index to go offline, although it can be performed as an online operation, depending on your SQL. If you don’t spend much time with SQL Server and you. Applies to: SQL Server. SQL Server also supports nonclustered columnstore indexes. Best regards, Carrin SQL Server ALTER INDEX Syntax. I'm just using the reorg index task that is in the maintenance plan designer GUI. This tool provides index analysis to manage index defragmentation, including rebuild and reorganize fragmented indexes . SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated. There are a number of differences. setting shrink file = 2GB (max it allows) 3. One of the possibilities that we have in SQL Server to keep our indexes defragmented, is the index reorganize operation. I cannot understand the reason, can you ?. 1. The. I don't think Windows SQL Server Maintenance has this option yet. reorganize pages. SQL Server Index Rebuild and Reorganize Script The script uses the following parameters: @reportOnly (required) Values: - 0: The script will reorganize or rebuild the fragmented. For maintenance i create a procedure that: -Shrink Database file (if is enabled) -Shrink Database log file. ALTER INDEX CCI_TEST on DBO. Index Rebuild vs Index Reorganize. It is used when the operation takes a. Yes. setting shrink file = 361,360 (min allowed) Among your question steps, this is the right one. You can apply a new fillfactor when you rebuild an index. The transaction log backup size spikes during index reorganize - exponentially bigger. Recall the paper example from above: a rebuild would be like reprinting the document in the correct order and trashing the old ones. Rename it to the Nightly Index Maintenance job. dm_db_index_physical_stats DMV to identify the fragmentation. Expand the Tables folder. First, we will start the index reorganization in a session using the following T-SQL code. Create a new server connection to use when performing this task. Reorganizing or rebuilding a cluster index does not have any impact on the non-cluster indexes in SQL Server 2000 SP4 or above, or any version of SQL 2005 or. For more information, see sys. An index rebuild will always build a new index, even if there’s no fragmentation. Product REORGANIZE GO. You can also change this threshold via parameters. When you rebuild, SQL creates a new fresh index. MS advises using Reorganize for 30% fragmentation or less. Bad internal fragmentation (having lots of free space on the pages) means the index is bigger than it needs to be. We will use 3 statements in order to show the blocking: ALTER INDEX. This operation is always online, uses minimal system resources, honors the fill factor that has been used during the creation of the index (common misconception is that reorganize operation does not take into account. 1 Answer. If you choose to compact large object data, the statement uses the. Starting with SQL 2005 master keeps very little info regarding server configs and logins. Note that the time it takes to complete the operations depends on the size of the database and how fragmented the indexes. The following index operations require no additional disk space: ALTER INDEX REORGANIZE; however, log space is required. The Page Fullness is 98.