It reorgs indexes when fragmentation is below 30% else it rebuild the index. This is other myth which needs attention. Paul Randal. Yup. DBCC SHRINKFILE does what the name implies. You’d have to set this up on your own. I have a mssql database, we'll call it: mssqlDB01. 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. For the SQL Servers we manage for our. Expand the Indexes. The data of partitioned tables and indexes is divided into units that may be spread across more than one filegroup in a database or stored in a. It's a. name AS IndexName, qs. The method (not a trick), therefore, is to Create Clustered Index only for the purpose of de-fragmenting the Heap, and drop it afterward. 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). The defragmentation schedule must be created manually in all versions. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK. Location : HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows Search. Index fragmentation occurs when SQL Server must split a page to allow for the insertion of a new row. SQL Defrag Manager is described as 'Find and Fix SQL Fragmentation' and is a Defrag tool in the development category. [SQLSTATE 01000] DBCC: Defrag phase of index. 2. 0. Right-click on the index that you need to check and choose the Properties option. SELECT ips. Q2: Generally you do not need to run anything for reindex/update stats. Working this way is usually optimal, since SQL Server does not have to update the NCIs while the data is being imported. The datafile. Starting with v1. On version 2019 it runs for over 30 minutes. This should be part of our SQL Server Maintenance jobs. Alter Index Rebuild – 217454 ms (218 secs or just under 4 minutes to complete). 0. 1. The results for this command are up for comparison against the Alter Index Rebuild. Rebuild does not drop and recreate the clustered index. It's. ; @tblName specifies if you only want to defrag indexes for a. Paul Randal. Sure, the database files are still in access by the running Sql Server service, but it is possible to defrag them and it takes effect. To check for index fragmentation using SSMS, open Object Explorer and navigate to the index you want to check. The issue was not enough contiguous free space inside the database to arrange the index consecutively. Fragmentation. The first time you enable it, the Fragmentation ManagerWizard displays. In SQL Server, when rebuilding an index which previously had statistics updated with PERSIST_SAMPLE_PERCENT, the persisted sample percent is reset back to default. SQL Server. Viewed 439 times. In older versions of SQL Sentry, this feature is disabled by default. Google led me to discover a practice to defrag all indexes per table, but I can't figure out how to do it on all tables. For an example, see the SQL Server index and statistics. dm_ db_ index_ physical_ stats (in 2005+). There are several types of fragmentation that can occur. 1. It depends on the degree of fragmentation of Indexes. The length of time the rebuild takes is related to the size of the index, not the amount of fragmentation in it. 8. (You could even go with 0% if you want. Friday, August 26, 2016 2:15 PM. – Dave Mason. This article shows an example of how to manually perform defragmentation on an index using SQL Management Studio. 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. If an index contains less than 100 pages, I will perform no maintenance. database_id = 6. Those rebuilds have been causing some locking pressure on one of our apps and I am looking at the possibility of scaling the rebuilds. Greater than 30% – rebuilding the index with the REBUILD option is probably the best option. It automates the time-consuming process of finding fragmented indexes based on policies for the targeted databases. 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. Then we can use contig [Filename] as shown below to defragment. In 2016, auto update of stats have been enabled for most databases. The reason why too many page splits can decrease the performance of SQL Server is because of the large number of I/O operations. Since we updated from SQL Server 2008 to 2019, the queries have become much slower. This setting is known as the fill factor. These commands are great, but you really need to collect the information and then analyze the data to. SQL Defrag Manager is a unique solution for defragmenting indexes for SQL Server. Double Click on one of the SQL. g. Check indexes from the index grid and click desired defragment operation from the ribbon. The name of the index. 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. I've been tasked with performing defragmentation on all of the tables. When the SQL Server service is running, all of the mdf and ldf files are considered open and thus skipped. The database will be shrunk leaving it. Expand the Indexes node. You need free space in the. This reversed the defragmentation step and gave them a database with nearly 100% fragmentation. Rebuild Index using ApexSQL Defrag ApexSQL Defrag Overview. The sys. Defrag indexes on very large production tables. 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. FragmentationLevel1. If you're looking for whenif the index was ever rebuilt, the Ola sp does have the ability to log to a table and the previous DBA hopefully utilized this feature. 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. _in_percent > 50 AND ss. Auto shrink is a database option that is used to conserve disk space. 2. Review resource utilization before defragmentation to avoid contention. In this article, I will provide a mechanism for automating SQL Server index defragmentation implemented in a database used in the company I work for. 7, on any database context you choose to create the usp_AdaptiveIndexDefrag and its supporting objects, open the attached script, and. It depends on the degree of fragmentation of Indexes. This mechanism allows defragmenting required indexes on a regular basis since index fragmentation takes place constantly in the 24/7 system. Example: Name: Indexrebuild_job; Description: RDS for SQL server agent job for index rebuild. December 16, 2013 10:03 am. Artemakis Artemiou. All necessary information is provided in this graphical tool with custom visuals and policies along with various informative reports. About; Products. This statement defragments the leaf level of the index so that the physical order of the index pages match the left-to-right logical order of the leaf nodes. 1. 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. This can be demonstrated by small test. Defragment indexes on your SQL SCCM database every night. Scott Dexter, 2008-08-05 (first published: 2008-06-12) The following is a script that I have worked off and on with until I finally decided that I had worked on it. Compression enabled on both. Another possibly is that the index is being rebuilt and then re-fragmenting again. If your table has a clustered index, the state of the index reflects the state of the table. 3. For example, one of the indexes with a page_count of 967 has a fragmentation percentage. Create a clustered index and move it away from the heap. Now, all you have to do is download the T-SQL script, execute it on your Azure SQL DB and execute the maintenance procedure. 1. The best SQL Defrag Manager alternative is dbForge Index Manager for SQL Server. If you have space constraint on the Server then you may need to Shrink the db. I took a look at its log and it looks like it hadn't even finished with the indices from the first table by the time I cancelled the job: Job '02. Product', PK_Product_ProductID); GO. Heavily fragmented Indexes - SQL Server 2005. MyTable. It makes your indexes larger and many operations slower by making the SQL Server access more pages than necessary. This must. Defragmentation improves disk space and. You also can create a linked server to SQLAZURE and create a sql agent job. USE [TestFileShrink] GO DBCC SHRINKFILE (N'TestFileShrink_data', 3000) GO. The index fragmentation is the index performance value in percentage, which can be fetched by SQL Server DMV. Thanks for the info on Showcontig and Index Defragmentation. If not specified, all non-system databases plus msdb and model will be defragmented. And if it is a clustered index, the entire table is copied. 9. Last week I decided to play with SQLFool's Automated Index Defrag Script and found it very usable and worthwhile. k. This REBUILD option is available in SQL Server 2008 onwards. 31, the table and index data collection is enabled by default during installation. SQL Server will be asking for much more data form the data (SSD) pages to satisfy the queries need to read through indexes root levels and intermediate levels to get to the data. A quick remark about the options you have: exec AzureSQLMaintenance @operation,@mode. Here are some steps you can follow to free up some space in a hurry. For more information, see sys. According to the index performance value, users can take the indexes in maintenance by revising the fragmentation percentage with the help of Rebuild or Reorganize operation. Reorganizing an index defragments the leaf level of an index by physically re-ordering pages to match the logical order. Optimal. The indexes are left as they were at the time of backup. Coworker started REBUILD on both tables. MDF is contiguous on disk. This internal fragmentation occurs when inside the. Manually shrinking or growing files using T-SQL commands can also contribute to physical file fragmentation. The scripts has some cool features like. is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation AND ss. That article was where the old "Best Method" recommendation of Reorg between 5 and 30% and Rebuild at > 30% fragmentation used to live. Weekly – 3% fragmented. Custom SQL Server Index Defrag and Rebuild Procedures. Manually shrinking or growing files using T-SQL commands can also contribute to physical file fragmentation. The method (not a trick), therefore, is to Create Clustered Index only for the purpose of de-fragmenting the Heap, and drop it afterward. For partitioned indexes, sys. Everything worked fine. One solution is to deploy a faster I/O subsystem, where page splits would be less of an issue. HSQLDB version 1. The SQL Server Database Engine automatically maintains indexes whenever insert, update or delete modifications take place. You need free space in the db of table_size x 1. Below is the output of database data file size and. SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated. Defragmentation in Practice. The application allows detailed index fragmentation analysis on server, database and table level. Larger indexes have more intermediate levels and pages. 1. The wise use of indexes is a proven method of improving database performance. On the second pass, SQL Server swaps the next physical page (7) with the next logical page (2). On smaller databases it ran quite quickly and with the correct and desired effects. We need to identify all the indexes that are fragmented and defrag them to achieve better performance. Paul pointed out a couple of other points. Indexes with small number of pages (<1000) will usually. Name : CoreCount. If avg_fragmentation_in_percent value is > 5% and < = 30% then you should Reorganize Index. In order to begin, proceed to Manage Index. record_count > 0 AND o. MS SQL Server always updates index statistics whenever an Insertion, Updating, or Deletion activity is performed on the . 31 and later, this feature is enabled by default for all targets. For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command. Defrag indexes on very large production tables. (Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and. We defrag if the index is large, otherwise reindex. Check the comment header block for its documentation. 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. In that case, the Storage Engine creates a new, empty page and moves 50 % of the rows (usually, but not always) from the full page to the new page. #1637994. index_type_desc AS IndexType, qs. Expand the specific table. PS: The above script is for row-store indexes. There are four alternatives to SQL Defrag Manager for Windows and SQL Server Management Studio. index_type_desc ,IPS. Automate the identification of index fragmentation hot spots. Rob Sheldon tackles the subject of Index Defragmentation in SQL Server 2005 and 2008, using the sys. Contig can defragment SQL Server database and log files while the databases are online, but there is some performance cost (whether or not the. 2005, but if you read around the 'net you'll find many more references to choose to rebuild your indexes via the ALTER INDEX command, which is new for SQL Server 2005. However, the index maintenance defaults. Open Object Explorer & expand the database containing the table you wish to reorganize. The operation only requires an intent-shared (IS) table lock. The above query is database specific. physical defragmentation by orders of magnitude. I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented. INDEX DEFRAG on SQL SERVER 2005. Set log file to start at 1 GB. The automation built into SQL Defrag Manager enables you to implement a "setThis is one of the optimizations SQL Server does when. In Update Statistics they mention one of the solutions:. index_id as [SI. It gives you the flexibility to reduce the SQL fragmentation immediately or during off-peak hours, all. Based on the query analysis output, rebuilding or reorganization of indexes must be done. 0. Sc (Comp Sci) SQL In The Wild : Discussions on DB performance with occasional diversions into recoverability We walk in the dark places no others. 1. Odds are, you’re not actually making things better – you’re making them worse! I explain why in this video: Why Index Fragmentation Doesn't Matter in SQL Server. /*Perform a 'USE <database name>' to select the database in which to run the script. 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. Aaron Bertrand April 13th, 2009 SQL Server 3. You should always do some form of intelligent rebuilding - i. The index on my RDS 2012 R2 server is growing out of control. The first step in fixing an index fragmentation performance problem is to determine how big the problem is. Improve this answer. One of the biggest performance hits that you will see on your databases, next to not having indexes, is indexes that are very fragmented. 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. 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. December 16, 2013 10:03 am. This might be due to MAXDOP setting on your server instance. To create SQL database maintenance plan, launch SQL Server Management Studio > expand the database instance > Management > right-click on the maintenance plan > New Maintenance Plan. This procedure automatically chooses. Contact the manufacturer of the defragmentation tool to learn how files are locked and how this. To defragment indexes, SQL Server 2000 provides several statements. Enter a Name and Description for the agent job, and then select OK. name AS index_name, i. Use the ALTER TABLE command to rebuild the Heap. Enter a Name and Description for the agent job, and then select OK. Automatic index and statistics management. Copy the detached database file to a different drive on the server. Store the data in a temporary table, truncate the old data, and re-insert the data back. Index rebuild is a heavy-duty process where index is deleted and then recreated from scratch with entirely new structure, free from all piled up fragments and empty-space pages. If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics. [object_id] = [idx]. Disable the indexes on the target table (at least the non-clustered indexes, or NCIs) Insert the new data. DBCC DBREINDEX('TableName') will rebuild the indexes on the table. 0. Receive email notifications for policy and. Q2, Q3: OPTIMIZE TABLE rebuilds the table by CREATEing a new table and INSERTing all the rows, then RENAMEing and DROPping. When we were listing the fragmanted indexes our scope was on the indexes that are used in expensive SQL Statements. Over time, the indexes created for an SQL Server database can become fragmented. This should be part of our SQL Server Maintenance jobs. 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. Its wide range of features allows users to create and manage custom policies, monitor index fragmentation on multiple servers as well as to create index fragmentation reports. To test this you can comment out (or remove completely) the line second from the bottom with two minus signs (--). This means every time we need to scan the index, it’ll take 10%. Now we will see how to defrag indexes. ; @dbScope specifies a database name to defrag. We have several databases in which a large number of tables are created and dropped. Click OK. Kalman Toth, SQL Server & BI Training; SQL 2008 GRAND SLAM. The DB restore may de-fragment (physically) at the disk level, but there will still be the same internal fragmentation at the page level. You always has some "white noise", so to speak. Fragmentation typically happens owing to insert, update and delete transactions. Or with other fill factors I tested: - Setting a Fill factor of 80 on the clustered index (Fragmentation > 98%. When i try to defrag them using ALTER INDEX. This causes a lot of free space or, as you call it, fragmentation. Note that the time limit is checked BEFORE an index defrag begins, thus a long index defrag can exceed the time limit. Only files marked by the system as "not moveable" can't be defragmented. " 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. Your best assistant here is our SQL Defrag feature, seamlessly integrated with dbForge Studio for SQL Server . Share This: As the data in the database tables of Microsoft SQL Server changes, their indexes change. From that you can easily run a defrag on each index as follows DBCC INDEXDEFRAG (8, 1113627606, 5) DBCC INDEXDEFRAG (8, 1113627606, 2). Often, this is not enough to. Refer to the below links:The downside to using the Reorganize Index task is that its index defragmentation capability is not as thorough and complete as the Rebuild Index task. For maintenance purposes, you can use Azure Automation and create a recurring script that checks for fragmented indexes and optimizes it. Example: Name: Indexrebuild_job; Description: RDS for SQL server agent job for index rebuild. 1. T-SQL script. SP_BlitzCache can help with finding your worst performing queries. Demo on impact of fragmentation while using non-clustered index on GUID primary key. It is very common to see big increase in performance after optimizing your SQL indexes. In this case Reorganize option is selected:Databases with no index defragmentation were an average of 5% fragmented. In this type of index, a pointer to the actual table data is stored in the leaf node. 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. 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. The next thing to know about index recommendations has to do with the keys in the index. e. Fragmentation typically happens owing to insert, update and delete transactions. A. 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. I know that there are really three kinds of fragmentation that I need to be concerned about as a DBA: Index Fragmentation in the SQL data files, including clustered index (table) fragmentation. Go to DBACOCKPIT –> Performance –> SQL Statements. Hello Marcos, I can't confirm this. In enterpise edition of SQL server you can also rebuild index online (which is better than index defrag for performance) Yes. 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. To create a linked server to azure, you can see this SO link:I need to add a linked server to a MS Azure SQL ServerSQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance. ApexSQL Defrag is a powerful application used for scheduled analyzing and defragmenting of SQL Server database indexes. Proceed to the Steps tab: Clicking on New. Columnstore Online Index Rebuilds – Columnstore indexes need more attention to maintenance. Contact the manufacturer of the defragmentation tool to learn how files are locked and how this could. [index_id] = [idx]. To work around a very large index that won't defragment, the BEST solution is to pre-size a new database and move all your objects to that DB. DON’T: Update records to make them longer. fragmentation is the index value, described in percentages, which can easily be fetched by the SQL Server DMV. NAME + '] ON [' + Object_name(ind. You have the option LOB_COMPACTION. If no results are returned it may be because you are running the script against the wrong SQL database or that there are no indexes with fragmentation above 15%. Therefore, defragmentation should be a part of the SQL Server maintenance process and needs to be taken seriously. Added support for indexes with the ALLOW_PAGE_LOCKS option enabled. DBCC SHRINKDATABASE(WorldOfHurt, 1); And it’ll reorganize the pages in the WorldOfHurt to leave just 1% free space. Microsoft recommends Index Rebuild operation to defrag indexes if the fragmentation level of your index is greater. If you have fragmentation, you need to REBUILD or REORGANIZE. This common maintenance task actually causes fragmentation, which is especially terrible if you’re doing both this and defragmenting your indexes. There are three options to remove fragmentation in heaps: Create a clustered index and drop it once it is completed. This REBUILD option is available in SQL Server 2008 onwards. SQL Defrag Manager is a powerful solution that automates and optimizes your SQL Server database defragmentation. 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. Fragmentation causes issues where it takes SQL Server more time to traverse the index tree to find the necessary records. In general no, the restore, at least in "modern" sql servers, is optimized to read and write data quickly. Summary: SharePoint Server uses SQL Server to store most of the content for the Web site and configuration settings. Creating a SQL Server Maintenance Plan. There are three options to remove fragmentation in heaps: Create a clustered index and drop it once it is completed. ) Then rerun the above free-space query again to see how the shrink worked: Free space after. Next the New Job window will open. "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). This may affect the concurrency of the server that's running SQL Server. SQL Server allows us to create only one Clustered index per each table, as the data can be sorted in the table using one order criteria. For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. fulltext_index_fragments catalog view can be used to query the number of fragments comprising a full-text index. Compression enabled on both. You can use SQL Server Management Studio (SSMS) or T-SQL to get started managing index fragmentation. From what we can tell, SQL Server does not conduct any internal maintenance on the system base tables, meaning that they can become very fragmented over time and bloated in size. Create a execute sql task and schedule it through sql agent . Indexes were 75% fragmented. 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). 19. dm_db_index_physical_stats to get information about your index fragmentation (see the avg_fragmentation_in_percent column). 1. Share. Index names must follow the rules of. As administrators will know, identification of fragmentation and of the same are crucial elements in database . The syntax is: "java. Niko Neugebaeur is the go-to for in-depth columnstore tips, and in this post, he analyzes the online. Just a reminder that index reorganize is always online (all indexes are available during defrag) and index rebuild can be made also online (WITH (ONLINE=ON) as long as the index does not contain BLOB columns. We have a Non-Clustered index on the varbinary field and our index maintenance scripts perform a REORGANIZE on this every 2-3 days. Perform logical consistency checks on indexes. The T-SQL below will shrink the data file to 3GB. We have a database with 2 tables, one with hundreds of millions of rows (row size<1KB), another 14 million rows. between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG. With spinning disks, this can be bad. OBJECT_ID) AS TableName, fq. 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. You can verify that by doing an "edit" on the page and. How Defragmentation Improves Performance. The other thing you can do, for when you do need to defragment indexes (if for example you have deleted a large amount of data), is using SQL Server's Service Broker to set up the maintenance plan for multiple tables in a batch. How Defragmentation Improves Performance. Watch this video by @A_Bansal to understand index fragmentation & page splits. This is because the doc set was reorganized for the SQL Server 2012 release, and not all topics/categories made it. One other item. When you dig down through my computer to the properties for the drives, and hit the "defragment" button on. If you don't want to grant so granular. December 8, 2009. It can be turned on by using the in-app wizard in any version. One of the reasons for that is fragmentation. A disk defragmenter moves all files, including the database file, into contiguous clusters on a hard disk. SQL Index Fragmentation and Maintenance. In fact, once you start focusing on defragmentation at the SQL Server level – whether with manual defragmentation or with the automated defragmentation provided with SQL Defrag Manager, you may decideDatabase Index Defragmentation Tool for SQL Server. If you want to run it on every user database on your server, simply replace DB_ID () with NULL in the call to sys. Azure SQL Database is a PaaS solution for migrating your on-premises databases to the Azure cloud infrastructure. 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). The datafile is also showing as 220gb but the actual space used is 140gb. Identify and remove index fragmentation – this is obviously what we have been talking until now and the biggest part of the SQL index maintenance. how to find out defragment index pages in sql server 2008 using script? 0. It shouldn't be used on WSUS 2. SQL Sentry Fragmentation Manager helps you make intelligent decisions about index management based on the table and index information collected. February 6, 2019. 16. A starting point for you could be to run a maintenance plan against this database. If you have fragmentation, you need to REBUILD or REORGANIZE. Remarks. ApexSQL Defrag is a SQL Server index monitoring and analysis 3 rd party tool that is used to monitor and manage SQL index defragmentation operations in SQL Server.