Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL Server, Azure SQL Database, and Azure SQL Managed Instance support table and index partitioning. It’s like a looking at the results of a developer writing SQL after three Red Bulls – it doesn’t make any sense, and you just want to wad it up and throw it away. Even though it should be safe, I would not want to defrag disks in Production while SQL is running. Otherwise, nothing special. Fragmentation typically happens owing to insert, update and delete transactions. "SQL Fool") has an automated index. Based on the. Share This: As the data in the database tables of Microsoft SQL Server changes, their indexes change. You can use SQL Server Management Studio (SSMS) or T-SQL to get started managing index fragmentation. REBUILD WITH (ONLINE=ON) some of them still remain fragmented. object_id) AS object_name, i. index : will run only index. There are two ways of doing that: (i) by using the ‘ DBCC SHOWCONTIG ‘ command, and (ii) by using the ‘ sys. When auto-index defragmentation is enabled in Orion, databasemaintinence will sometimes fail to defragment highly fragmented indexes will get defragmented during the Orion database maintenance, which runs at 2:15 AM by default. Like SQL Server Management Studio, you run Tuning Advisor from a client computer that connects to your Amazon. Defragmentation utilities may lock sections of the file, preventing SQL Server from completing a read or write operation. 0. The execute sql task should contain the index rebuild code along with stats rebuild . It was noticed that the data has high fragmentation. The first time you enable it, the Fragmentation ManagerWizard displays. This puts unnecessary pressure on the buffer pool and. Expand the table on which you want to reorganize an index. Otherwise you could just reindex the lot, but you'll. For this example, the defragmentation reduces the size of the index by more than 3x (from 25 pages to 7 pages) and the table consumes just over 50% less space. . 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. In Object Explorer, Expand the database that contains the table on which you want to reorganize an index. This might be due to MAXDOP setting on your server instance. Yup. It doesn’t work on the intermediate pages between the root and the leaf. Greater than 30% – rebuilding the index with the REBUILD option is probably the best option. For more information, see sys. Compression enabled on both. Indexes were 75% fragmented. Your best assistant here is our SQL Defrag feature, seamlessly integrated with dbForge Studio for SQL Server . 11:33. There's a general consensus that you should reorganize ("defragment") your indices as soon as index fragmentation reaches more than 5 (sometimes 10%), and you should rebuild them completely when it goes beyond 30% (at least that's the numbers I've heard advocated in a lot of places). The index on my RDS 2012 R2 server is growing out of control. Math. So, how to identify the fragmentation of all indexes in a database? Here is the query. Or with other fill factors I tested: - Setting a Fill factor of 80 on the clustered index (Fragmentation > 98%. Missed Connection: Employer Seeking Employee. ; usp_AdaptiveIndexDefrag_PurgeLogs, which will purge the log tables of data older than 90 days, to avoid indefinite growth. record_count > 0 AND o. This is quite a big problem in SQL Server, as your indexes will fragment over time. For the SQL Servers we manage for our. The reason why too many page splits can decrease the performance of SQL Server is because of the large number of I/O operations. Weekly – 3% fragmented. The sys. It does exactly that if ON. Weekly – 3% fragmented. SQL Defrag Manager is a unique solution for defragmenting indexes for SQL Server. The sys. " ALTER INDEX MyIndex ON MyTable REORGANIZEThe more keys and included columns, the bigger the indexes get and the longer SQL Server will hold locks to insert, update, and delete data. The tempdb is used a lot – place it on separate drive to the Log and MDF files. Any indexes above 50% in fragmentation percentages that affect more than 4000 rows are bad, and the system performance can significantly benefit from defragmenting the indexes. Features: Supports Microsoft SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016 and 2017 CTP2. This causes a lot of free space or, as you call it, fragmentation. Any full-text indexes of non-trivial size with fragmentation of at least 10% will be flagged to be re-built by our over-night maintenance. That article is in-depth and difficult to summarise in this reply, but it comes down to creating an asynchronous. According to Microsoft guidelines, based upon index fragmentation percentage, we may choose to either REORGANIZE, REBUILD indexes or just ignore. SQL Defrag Manager offers a totally new way to identify, optimize, manage and automate SQL Server defragmentation. Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation. index_type_desc ,IPS. Between 10% and 30% – defragging the index with the REORGANIZE option is recommended. Automate the identification of index fragmentation hot spots. You're allowed to define online/offline rebuilds, reorganizations, stats updates, partition level operations, etc. In order to solve this problem, it is necessary to start with analyzing the index and evaluating the degree of its fragmentation. 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. In a nutshell, SQL Server’s performance can be most improved by focusing on its internals. We need to identify all the indexes that are fragmented and defrag them to achieve better performance. The syntax is: "java. But over a period of time, because of DML operations (inserts/deletes/updates), the data will get spread across the disc (Physical order of pages are NOT in sequence). Index fragments occur when any transaction takes place in database table. Below is a simple query that will provide a list of indexes, fragmentation percentage, and record counts for each table in a database. It is compatible with all versions of SQL Server 2005 , 2008 , 2012 , 2014 and 2016. 0 databases. The DBCC Defrags will put the data in better order. DBREINDEX will always defragment as clean copies are made whereas INDEXDEFRAG churns pages. Let it autogrow with at least 200 MB at the time. Consequently, the fragmentation adversely affects the performance of SQL Server. Here is a. Here are some steps you can follow to free up some space in a hurry. Fragmented indexes will primarily slow down ‘search’ performance. Between 10% and 30% – defragging the index with the REORGANIZE option is recommended. If it grows often, it could concievably become fragmented. Its wide range of features allows users to create and. It gives you the flexibility to reduce the SQL fragmentation immediately or during off-peak hours, all. Within sql not only is there file fragmentation, but also fragmentation on the underlying objects, tables and indexes. 0. Monitor tempdb during rebuild operations. If an index contains less than 100 pages, I will perform no maintenance. As administrators will know, identification of fragmentation and of the same are crucial elements in database . There is all reason to only rebuild index that are fragmented, and a good maintenance. Reorganize indexes in a table using Transact-SQL. Select Steps, and then select New to add an execution step. NAME + '] ON [' + Object_name(ind. index_id] ,SI. SQL Defrag Manager is described as 'Find and Fix SQL Fragmentation' and is a Defrag tool in the development category. Rebuild if > 30%. The method (not a trick), therefore, is to Create Clustered Index only for the purpose of de-fragmenting the Heap, and drop it afterward. Missed Connection: Employer Seeking Employee. A proper query analysis plan has to be built and followed. avg_fragmentation_in_percent as Fragmentation, indexstats. The DB restore may de-fragment (physically) at the disk level, but there will still be the same internal fragmentation at the page level. Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. You always has some "white noise", so to speak. Weekly – 3% fragmented. For SQL Server, I wouldn't recommend defragmenting the data files or log files unless the server is at low load, but it can help. Alter Index Rebuild – 217454 ms (218 secs or just under 4 minutes to complete). It’s a tool that simplifies the management of SQL Server indexes across your organization. dm_db_index_physical_stats DMF to. You’d have to set this up on your own. It appears to be a bug in 2012 R2. physical defragmentation by orders of magnitude. Here is a simple query to check fragmentation on your existing indexes:From SQL Server Books Online: "REORGANIZE:Specifies the index leaf level will be reorganized. Based on the query analysis output, rebuilding or reorganization of indexes must be done. Optimal. I have a plan that I am not sure will work: Normally defrag the partition while the service is still running until the only fragmented files are the database files. This is what we call external fragmentation. The link points to SQL Server 2014 docs, but the syntax should work on 2005 and 2008 as well. Internal fragmentation means there is free space inside the data pages rather than having them fully packed with row data. I have created multiple. Indexes with small number of pages (<1000) will usually. alloc_unit_type_desc FROM sys. Heaptest default values go 50 select index_type_desc,avg_fragmentation. This clause is equivalent to DBCC INDEXDEFRAG. This job needs to be scheduled and ran on a weekly basis due to large data load. Mar 8, 2021, 2:47 PM. Viewed 439 times. This job needs to be scheduled and ran on a weekly basis due to. Columnstore Online Index Rebuilds – Columnstore indexes need more attention to maintenance. Manually shrinking or growing files using T-SQL commands can also contribute to physical file fragmentation. Create a clustered index and move it away from the heap. Time time went down, physical reads went way down and read-ahead reads went way up. At first glance, that would seem to indicate that your database got worse off if you defragmented! But like all good novels (and most bad ones), the plot thickens. DBCC DBREINDEX('TableName') will rebuild the indexes on the table. As of version 2020. But this takes. He makes the point that, by analyzing indexes effectively, you can save a lot of. In the CREATE INDEX to can tell SQL Server to sort in TempDB vs. On the right panel, right-click SQL Server Agent. SQL Defrag Manager is a unique solution for defragmenting indexes for SQL Server. Index names must follow the rules of identifiers. In enterpise edition of SQL server you can also rebuild index online (which is better than index defrag for performance) Yes. [name] as 'Index', indexstats. SELECT ips. I'd consider doing a LOB_COMPACTION less frequently but both options comes down to your maintenance windows duration pretty much. But it is not recommended. SQL Index Fragmentation and Maintenance. Type : DWORD. 8. On the second pass, SQL Server swaps the next physical page (7) with the next logical page (2). In SQL 2005, you can query DMV sys. To create a new job, right click on SQL Server Agent, select New and then Job. The issue was not enough contiguous free space inside the database to arrange the index consecutively. In order to begin, proceed to Manage Index. If you want to run it on every user database on your server, simply replace DB_ID () with NULL in the call to sys. With spinning disks, this can be bad. – Dave Mason. 2 (X64). Then, choose New, Job to create a SQL agent job. Analysing the results of both the DBCC DBReIndex and the Alter Index Rebuild the percentage of fragmentation after the command was run is the same but there is a big difference in the amount of time to complete the operation, nearly 1/3. Here are the options that I came up with: Expand the 1st file in the PRIMARY filegroup by ~28GB (7 files x 4GB) Move the data off each of the successive files and mark them for deletion. You can verify that by doing an "edit" on the page and. The datafile is also showing as 220gb but the actual space used is 140gb. Burt King. @dbScope specifies a database name to defrag. Use the Rebuild Index Task dialog to re-create the indexes on the tables in the database with a new fill factor. That means that if i stop the process and restart it, indexes not fragmented won't need reindex. Q2, Q3: OPTIMIZE TABLE rebuilds the table by CREATEing a new table and INSERTing all the rows, then RENAMEing and DROPping. SQL Server will by default perform a NOTRUNCATE which will move data pages from the end of the file to any free space at the beginning of the database data file. Watch this video by @A_Bansal to understand index fragmentation & page splits. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. indexes. This is called fragmentation. In this article. SQL Server locks acquired with SQL Server online index rebuilds. Normal Windows defrag cannot defrag open files. Yes it will - but only to a certain degree. -- Compute fragmentation information for all full-text indexes on the database SELECT c. Create/restore new database from the recently created backup. Copy the detached database file back to the. On the fourth pass, SQL Server swaps the next physical page (5) with the next logical page (4). Q1: None. Here I use 4 conditions : 1. However, the index maintenance defaults. Here are the changes: Added support for database and object names with special characters. Database size was ~66GB. On versions prior to SQL Server 2005 you can defrag standard indexes using the DBCC INDEXDEFRAG command:For a linked list (a B-tree index), than can cause SQL Server to "jump back and forth" in the database file when it read lots of pages (a scan or partial scan). To rebuild or reorganize indexes manually, head over to the Fragmentation tab. Otherwise, nothing special. Fragmentation may become significant in tables with more than 1000 pages in them. I do not understand the distinct, group by or sum(row_count) clauses. This optimizes and speeds up file access. There is one other difference between DBCC DBREINDEX and CREATE INDEX DROP_EXISTING. This script has been tested and will work with SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 and SQL Server 2019. If it grows often, it could concievably become fragmented. fulltext_index_fragments catalog view can be used to query the number of fragments comprising a full-text index. And if it is a clustered index, the entire table is copied. From the SQL Server Index Properties page, browse the Fragmentation page, and you will see the index fragmentation percentage and how much the SQL Server index pages are full. 25. It shouldn't be used on WSUS 2. SQL - defragmenting an index on a compressed table. Use DBCC INDEXDEFRAG to defragment an index. 0. This article explains how to create customized policies for index defragmentation jobs for SQL Server Introduction When creating indexes, database administrators should look for the best settings to ensure minimal performance impact and degradation. The maintenance plan. It gives you the flexibility to run the defragmentation utility immediately or during off-peak hours, all through an easy. As people have mentioned here, your indexes do not automatically rebuild. This may affect the concurrency of the server that's running SQL Server. It’s like a looking at the results of a developer writing SQL after three Red Bulls – it doesn’t make any sense, and you just want to wad it up and throw it away. INSERT INTO [dbo]. These pages can get empty space on them and become out of order over time as well. To create SQL Server agent that will defragment specified indexes automatically, perform the following steps: Expand SQL Server Agent in Object explorer, right click on Jobs, and select New Job…: In General tab, specify the name and description for the job. dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS. It's. Starting with v1. 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. This to me would be a sign that constant rebuilds of that index actually aren't a good help. Index fragmentation can cause high CPU usage, which means disk usage, and therefore performance degradation. Two types of fragmentation can afflict SQL Server indexes and consequently the performance of your databases. The first method is the Fragmentation page of the SQL Server Index Properties window. However, over time and especially when heavy. How Defragmentation Improves Performance. The datafile. SQL Server index. Contact the manufacturer of the defragmentation tool to learn how files are locked and how this could. It is very common to see big increase in performance after optimizing your SQL indexes. dm_db_index_physical_stats there you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. From that you can easily run a defrag on each index as follows DBCC INDEXDEFRAG (8, 1113627606, 5) DBCC INDEXDEFRAG (8, 1113627606, 2). Thanks for the info on Showcontig and Index Defragmentation. Make sure to thick the Enabled checkbox. After the index rebuilds, query was way faster when comparing to clean buffer caches. Since we updated from SQL Server 2008 to 2019, the queries have become much slower. "The ALTER INDEX rebuild and reorganize options cannot be used to defragment and reclaim space in a heap (but they can used to defragment non-clustered indexes on a heap). The results for this command are up for comparison against the Alter Index Rebuild. Index fragments occur when any transaction takes place in database table. This must. SQL SERVER – Sharpen Your Basic SQL Server Skills – Learn the distinctions between unique constraint and primary key constraint and the easiest way to get random rows from a table Next Post SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2 This causes a lot of free space or, as you call it, fragmentation. Right click the index, click Properties, then click Fragmentation to see the fragmentation and page fullness percentages. 4. DBREINDEX = rebuild a copy of the index (es) from scratch. DON’T: Insert records with random key values. Columnstore Online Index Rebuilds – Columnstore indexes need more attention to maintenance. This may affect the concurrency of the server that's running SQL Server. Logical fragmentation happens when an index leaf page is full and space is required on it, either for an insert or to make an existing record longer (from updating a variable-length column). SELECT ips. Fairly easily, you can use my sp_RebuildIndexes procedures as a base and then change the code to DEFRAG instead of rebuild. Then we can use contig [Filename] as shown below to defragment. Enable Fragmentation Manager through the right-click context menu of any instance or by opening the Indexes tab of SQL Sentry and selecting Enable Now. Microsoft best practice says that you should generally not do a rebuild operation unless fragmentation is over 30%. In this article. Daily – 6% fragmented. If you want to defragment a heap in SQL Server 2005, you have three options: 1) create a clustered index on the heap, then drop the clustered index;Many of my clients to defrag their hard drives; some however do not. A SQL Server DBA myth a day: (29/30) fixing heap fragmentation. Index names must be unique within a table or view but don't have to be unique within a database. Let’s use DBCC SHRINKDATABASE to reclaim the empty space. Yes, rebuilding indexes will take a toll on your transaction log file. Azure SQL Database is a PaaS solution for migrating your on-premises databases to the Azure cloud infrastructure. Apart from disk space you should not be too worried. This database has a few hundred tables and each table has a range of 1 to 15 indexes per table. Expand the Tables folder. PS: The above script is for row-store indexes. Rob Sheldon tackles the subject of Index Defragmentation in SQL Server 2005 and 2008, using the sys. 1. Azure Automation: Automate Azure SQL Database indexes and statistics maintenance. If you are seeing this behavior, then its better to use ( MAXDOP 1) ( serial index rebuild) at query level when. DM_DB_INDEX_PHYSICAL_STATS DMV displays fragmentation information for the data and indexes of the specified object. April 29, 2010. Starting Price: $1,196 per. as our primary concentration is to defrag indexes. If you have space constraint on the Server then you may need to Shrink the db. This can be checked using:. 1. You can use SQL Server Management Studio (SSMS) or T-SQL to get started managing index fragmentation. Reorganizing only works on the leaf pages. dm_db_index. avg_fragmentation_in_percent, ips. Follow. set @current_sql = (select top 1 Reorganize_SQL from @fragmented_indexes) execute sp_executesql @current_sql--select. SQL index fragmentation reports. avg_fragmentation_in_percent <= 30 THEN 'ALTER INDEX [' + ind. Maintaining indexes is an ongoing and very responsible job assignment. When using EXECUTES to rebuild and exclude indexes from the defrag SP, I receive the following errors: Msg 50000, Level 16, State 1, Procedure IndexOptimize, Line 918 The value for the parameter @Indexes is not supported. Index fragmentation occurs when SQL Server must split a page to allow for the insertion of a new row. the filegroup of the index. Reorganize: Reorganize indexes with between 11-30 percent fragmentation. This generates a fragmentation of this clustered index of more than 99% (with default Fill factor). This index uses column-based data storage and query processing to achieve gains up to 10. How Defragmentation Improves Performance. is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation AND ss. This puts unnecessary pressure on the buffer pool and also negatively impacts. He makes the point that, by analyzing indexes effectively, you can save a lot of unnecessary rebuilding of indexes. object_id) AS schema_name, OBJECT_NAME(ips. Fragmentation causes issues where it takes SQL Server more time to traverse the index tree to find the necessary records. Miscellaneous tasks such as enabling trace flags or removing a DLL. The sys. Over time, the indexes created for an SQL Server database can become fragmented. You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. In the heap tables, the absence of the clustered index means that the data. That article was where the old "Best Method" recommendation of Reorg between 5 and 30% and Rebuild at > 30% fragmentation used to live. We can see after the command there are only 4 fragments. Check your SQL File allocation. Like Ola's scripts, mine only defrag indexes that need it. SELECT CASE WHEN indexstats. "Partitioning Clustered Indexes When partitioning a clustered index, the clustering key must contain the partitioning column. Now we will see how to defrag indexes. Kalman, When you use keyword ALL you do not specify particular index name instead you just specify table name. Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance. You can also change this threshold via parameters. Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Added support for indexes with the ALLOW_PAGE_LOCKS option. 2027. index_type_desc AS IndexType, qs. In general no, the restore, at least in "modern" sql servers, is optimized to read and write data quickly. Only files marked by the system as "not moveable" can't be defragmented. 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. In addition, you can use the dynamic management view sys. Set the lower limit, as a percentage, for medium fragmentation. We need to identify all the indexes that are fragmented and defrag them to achieve better performance. We have a SQL 2016 database that has a 1. or. In order to solve this problem, it is necessary to start with analyzing the index and evaluating the degree of its fragmentation. Fragmentation >=30 AND PAGES>1000 then. DBCC SHRINKFILE does what the name implies. Review SQL Server script to rebuild all indexes for all tables and all databases tip. For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command. Veritas now uses the heavily debated command DBCC SHOWCONTIG WITH ALL_INDEXES,or DBCC SHOWCONTIG WITH ALL_INDEXES,tableresults (Table results will give you a table format to read) to monitor the health of the table indexes when the use of the sys. Find and remove unused indexes – everything that is unused doesn’t do anything good. Remarks. SQL Server Internal Fragmentation SQL Server Internal Fragmentation is caused by pages that have too much free space. This can be demonstrated by small test. With the SQL performance analyzer in DPA, DBAs can check SQL index fragmentation on the server and get pinpointed advice about wait times, SQL statements, and the actual workload—across the past five seconds or five years—they. SQL Defrag Manager is a powerful solution that automates and optimizes your SQL Server database defragmentation. SQL Server Agent job alert configuration. [name] , ips. Basically, an index rebuild copies the index to another place. Extra disk space required during SQL Server online index rebuilds. It has its own disk space and is arranged in a B-tree structure. Email alerts can be tailored to inform the staff when fragmentation is necessary and when the process is complete. This means you can grant alter table on every table of interest that already exists. -- Now do an index defrag. Artemakis Artemiou. It can be done with online option in enterprise edition. Basically, an index rebuild copies the index to another place. Until yesterday, we always limited the WHERE-clause: where date > '20180101'. To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. dm_db_index_physical_stats function is the preferred tool to use to check index fragmentation on any CA or DA database table. Reports index length stats for indexes who's lengths exceeding parametrized thresholds (defaults to 0 - get all) for total length and seek length. This reversed the defragmentation step and gave them a database with nearly 100% fragmentation. For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command. A SQL Server DBA myth a day: (29/30) fixing heap fragmentation. SQL Server 2000 introduces a new DBCC INDEXDEFRAG statement to defragment clustered and nonclustered indexes on tables and views. This mechanism allows defragmenting required indexes on a regular basis since index fragmentation takes place constantly in the 24/7 system. OBJECT_ID) AS TableName, fq. They are also configured from SSMS. We are constrained to use Indexdefrag as we may not take down servers UNLESS there is a breakage that. Share. The wise use of indexes is a proven method of improving database performance. This is great if the . This article serves as a guide as to how to automate and schedule SQL Server index defragmentation, and covers three different ways of doing it. Check indexes from the index grid and click desired defragment operation from the ribbon. Index defragmentation is a part of Microsoft SQL Server maintenance where the index of database engine indexes of insert, update and delete operations is optimized to reduce fragmentation. 1. Here's one from MSDN:--This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold. select object_name(IPS. Failing that (ie Standard Edition etc), look at re-organizing indexes - these will keep them online. Defragment indexes on your SQL SCCM database every night. You need free space in the db of table_size x 1. Open-file defragmenting affects performance. This new DMF ( sys. When i try to defrag them using ALTER INDEX. For this example, the defragmentation reduces the size of the index by more than 3x (from 25 pages to 7 pages) and the table consumes just over 50% less space. SQL Server 2005 Index Fragmentation. This script has been tested and will work with SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 and SQL Server 2019. Chad Boyd. Index names must follow the rules of. 1. Msg 50000, Level 16, State 1, Procedure IndexOptimize, Line 1009 The documentation is available at. For more details, we could refer to the following articles:I think you need index_id in the joins against sys. I figured, the reason was because of the page_count, which was lower than 1000 for each of the indexes that were still very fragmented. indexes AS [idx] ON [ips]. Review Deeper insight into unused indexes for SQL Server tip. Delete the database with fragmented MDF file. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting.