You can also see if a reorganize will help until you can do a full rebuild. We will use 3 statements in order to show the blocking: ALTER INDEX. Rebuild or Reorganize SQL Index. – As well as maintenance plans, SQL Server Agent jobs are also a handy way to automate and schedule index defragmentation jobs in SQL Server. - 1: The script will just output the index reorganization or rebuild commands without running them. Please post more details like how are you running the reorganize via TSQL or SSMS or SSIS. in case you aren't on SQL enterprise edition, you cannot rebuild WITH ONLINE, hence you can cause additional blocking; ad. As a good start, read these: Rebuild or Reorganize: SQL Server Index Maintenance (Kendra Little)Highly fragmented, the application performs well. Also, running UPDATE STATISTICS gets you both index and columns stats updates. I used the sp_who2 procedure to see which queries were waiting, and which other query they were blocked by. Just go to the table, further expand the indexing folder and right after that you can right-click on it and select the option to rebuild all the indexes. T-SQL Alter Index. Well, I’d wager some of it is backwards compatibility. Reorg the Indexes if the Fragmentation level is > 5% and <30%. Yup, that is one perfectly valid way. Here's another script to add to the list. I have many DBs that are currently used for insert and delete. Click Next, and you can define the Update Statistics task. Step 1. If you need more tempdb space, scale up the pool. Large object data is data with the image, text, ntext, varchar (max), nvarchar (max), varbinary (max), or xml data type. Expand the Tables folder. Microsoft's guidance on index reorganize and rebuild supports this: For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. First it’ll try an index reorganize, which is an online operation. Use Ola Hallengren ‘s IndexOptimize but don’t use his defaults. dm_db_index_physical_stats in a script to rebuild or reorganize indexes). This would also keep the original order of columns. Here’s how: Download the MaintenanceSolution. Similarly, removing fragmentation in a. Starting with SQL Server 2016 (13. Depending on the type of index and database engine version, a rebuild operation can be done online or offline. Click OK. インデックスの断片化の状態がわかったら、それによってインデックス (index) を再構成 (Reorganize)するか、または再構築 (Rebuild) するか決定します。. . I'm currently using the approach described here: sys. In Object Explorer, connect to an instance of Database Engine. The maintenance plan. Frequency of the maintenance should be considered with the goal of minimizing impact of log generation. From all the research I've done, I can't really find any indication of why you would want page fullness to be low, and am anticipating that I'll want to do an index reorganize operation to set the value to. Index Rebuild vs Index Reorganize. Sorted by: 1. Index Rebuild operation first drops and then recreates the index. Workaround. This tool provides index analysis to manage index defragmentation, including rebuild and reorganize fragmented indexes . 2. 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. Proceed to the Steps tab: Clicking on New. Near the top of the script, around line 32, you will see a line of Transact-SQL code that looks like this: 1. SQL Server 2012 (11. 2. The script will do a reorg if fragmentation goes over 5 %. For example, the heaviest index (clustered index and also pk) is running reorg for around 4 hours 30 min even do the index was. To reorganize index SQL Server, right-click it & choose Reorganize. x) では、REORGANIZE は CLOSED 行グループを列ストアに圧縮するためにのみ使用されます。 最適化操作を実行し、すべてのデルタ行グループを列ストアに強制的に移動する唯一の方法は、インデックスを再構築することです。Yes, just the table and any query that tries to access that table. Reorganizing an index only deals with the fragmentation that exists, and the more fragmentation there is, the longer a reorganize will take. Here is a simple query to check fragmentation on your existing indexes:1 Answer. Select Allow online DML processing, and then select True from the list. This manual operation two-step. There is all reason to only rebuild index that are fragmented, and a good maintenance. It may also take longer to REORGANIZE a highly fragmented index than to REBUILD it. x) および SQL Server 2014 (12. The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. Here's another script to add to the list. It also enables faster growth in the future. How many pages are in these indexes. On the Table Designer menu, click Indexes/Keys. 000 rows. Artemakis Artemiou. This is how records are made unique in the context of a clustered index. Click the plus sign to expand the Tables folder. The Reorganize Index task encapsulates the Transact-SQL ALTER INDEX statement. If the clustered index is being rebuilt, an exclusive table lock is held. Index should be reorganized when index fragmentation is between 10% to 40%. 3,895 9 51 78. If the index is disabled, rebuilding brings it back to life. [Test] REBUILD WITH (ONLINE = ON); Additionally, I would only rebuild indexes which require it. dm_db_index_physical_stats (Transact-SQL). Then, drag and drop Rebuild Index Task into the maintenance plan designer. To create a new maintenance plan, right click on Maintenance Plan and select New Maintenance Plan and the following window will open. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. Rebuild or Reorganize SQL Index. Our Production instance is running SQL Server 2014. I used the sp_who2 procedure to see which queries were waiting, and which other query they were blocked by. Copy and paste the following example into the query window and click Execute. 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. From cruel experience, I know that I can repopulate that table from scratch (i. Unlike DBCC INDEXDEFRAG, or ALTER INDEX with the REORGANIZE option, DBCC DBREINDEX is an offline operation. In it, enter the Job name, owner, optionally Category. This is a best practice for performance when you rebuild or reorganize indexes. For maintenance i create a procedure that: -Shrink Database file (if is enabled) -Shrink Database log file. Rebuilding an index can be executed online or offline. Frequency of the maintenance should be considered with the goal of minimizing impact of log generation. Then, choose New, Job to create a SQL agent job. REORGANIZE INDEX blocks other queries. Are there any advantages or disadvantages to using " UPDATE STATISTICS (Index name) " as opposed to "ALTER INDEX (index. All indexes will be inserted to, updated to, or deleted from for every respective DML statement. MS advises using Reorganize for 30%. To fix the fragmentation either rebuild or reorganize the index on the table. The second part of this tip is intended to reduce the duration for update statistics as it pertains to both. Select “reorganize index” and “rebuild index. Optimize SQL Server non-clustered indexes and queries by considering index fields, compound indexes and SQL Server statistics. You don’t get bonused based on the amount of free space on your drives. All you might notice is a drop in performance executing queries while the indexes are gone but dropping and creating a (none-clustered) index has no impact whatsoever on the actual data stored in your tables (Creating a clustered index impacts the physical ordering of your data but again, no data. I don't remember if IDENTITY INSERT ON will help. 2 contributors. The first part of this tip focuses on SQL Server Enterprise Edition to reduce the duration for index maintenance for off-line rebuilds. Regular index maintenance and statistics updates are crucial, that much is certain. Instead of running rebuild index on whole database we are planning to run it against each table. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Use the online version of the Rebuild Index task ; 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. REORGANIZE. Right-click the index for which you want to set the max degree of parallelism and select Properties. And if the reorganize is a deadlock. dm_db_index_physical_stats ‘ Dynamic Management View (DMV). Also, in our nightly database maintenance plan, I have update statistics, reorganize index and rebuild index tasks setup. If page_count value( which is there in DMV sys. Expand the Indexes folder. Designing the SQL Server Reorganize Index Task The script below allows you to rebuild indexes for all databases and all tables within a database. Below the last two rows that displays the fail: Source: Reorganize Index Executing query "ALTER INDEX Stack Exchange Network Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Rebuild/Reorganize indexes weekly – As it was mentioned previously, this will depend on the environment, situation, database size, etc. This REBUILD option is available in SQL Server 2008 onwards. And if it is a clustered index, the entire table is copied. index_id = 0 -- Heap or table indexstats. For more information about this enhancement, check the SQL Server 2016. When complete, select Next. Dec 19, 2021, 9:55 PM. The. The fill factor determines the amount of empty space on each page in the index, to accommodate future expansion. Thank you, ShamAnswers. One time job to reorganize the indexes One time job to rebuild the indexes Scheduling ongoing reorganization job SQL databases, similar to the file system on a disk, will fragment over time. There’s no hard and fast rule here but I generally say where. 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. Which is the best method to reorganize sql server database. The Query Optimizer. I was going to take a look at Ola Hallengren's scripts and some other stuff but wanted to see if there was possibly a simple explanation for this. One or more of the databases used by SharePoint Server have fragmented indexes. Share. The only. The reason we want to load. Select the plus sign to expand the Management folder. They are also configured from SSMS. 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. Last weekend the index maintenance took more than 5 hours and the full backup was running at the same time. The below query rebuild or Reorganize only the necessary Indexes instead of all index rebuild, As a general recommendation the index need to rebuild that's get fragment percentage more. It is causing other queries to wait, even simple ones like: SELECT * FROM tableName WHERE indexedColumn = @value. I also removed the the second part of the case statement that uses REORGANIZE. 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. SELECT a. It’s an unbelievably, disgustingly, repulsively bad idea. There are two options to fix fragmentation. The Microsoft Docs page for SQL Server statistics states: Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Copy and paste the following example into the query window and select Execute. . – Ed B. SQL Server 2019 adds resumable online index creation, and it’s pretty spiffy: 1. Heaptest default values go 50 select. This index design guide contains information on index architecture, and best practices to help you design effective indexes to meet the needs of. REBUILD will not just rebuild index, but also force update of corresponding statistics. The Reorganize Index task encapsulates the Transact-SQL ALTER INDEX statement. If a nonclustered index is being rebuilt, a shared lock is held on the table in question during the operation. Coming to update stats, I would prefer to do it with Full Scan depending on the size of the databases. Note The commented fnMs_GenerateIndexMaintenanceScript function is a table-valued function (TVF) that can. FileID, a. from truncation) in 7 hours. ALTER INDEX index_name ON table_name REORGANIZE OR. That can be found using the STATS_DATE function. Best regards, Carrin Cancelling / Stopping ALTER INDEX REORGANIZE. That's why as a rule of thumb, for fragmentation greater than about 30%. Full-Text Engine. Compaction is based on the existing fill. 3. Check out my Pluralsight course on fragmentation or read. However, the meaning of those operations is different in the case of Columnstore. 1. Both versions allow you to specify the. In the IndexOptimize procedure, you can define a preferred index maintenance operation for each fragmentation group. Indexes with small number of pages (<1000) will usually. [myTable] REORGANIZE WITH ( LOB_COMPACTION = ON ) I have the above query running for 16 days (still running), the table is a dummy table used for benchmark tests, it has over 10 Billion rows. When rebuilding your indexes, be sure that you allocate enough memory to the session that rebuilds your index. Let’s first drop the Clustered Columnstore index that we created above using the below command. This tip will explore two features to speed up SQL Server index and statistics maintenance. The index uses the main filters on the sales table from the time dimension, i. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, among other parameters, and update statistics with a linear threshold. If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics. column_name DISABLE; ALTER INDEX. For that plan we will break those tables into seven groups and everyday run script against. These are two different modes to remove index fragmentation. 4. Index Reorganize During Database Full Backup. Yup, that is one perfectly valid way. You can do maintenance in phases, where each maintenance phase covers a subset of. The index reorganize operation will be always performed online. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics. That means that it sometime has to contend with blocking because it's sharing the same pages that. It has an IF condition for the Reorganize but i don't need it, i need to modify the code so it queries indexes of a certain database and schema and rebuilds only if the fragmentation is bigger than 5%. Many people (and even solutions) in the SQL Server community say you need to look at Index Fragmentation levels (e. Our Production instance is running SQL Server 2014. Rules For Index Maintenance: 1. In SQL Server, you "might" run into issues with "updating primary key". Doing so will: Eliminate all data in the deltastore. 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. x), you can create nonclustered B-tree or rowstore indexes as secondary indexes on a clustered columnstore index. To achieve availability similar to the reorganize. You can go the easy way and use SQL Server Maintenance Plans, but unfortunately they’re very simplistic: you can only say “rebuild all the indexes” or “reorganize all the indexes”. Start SSMS and connect to the SQL Server database engine. Depending on INSERT, UPDATE and DELETE activity against your tables, your physical data can become very fragmented. #1637994. Expand Databases, and then expand the database that contains the full-text index. Columns with text, image, ntext, varchar (max), nvarchar (max) and varbinary (max) cannot be used in the index key columns. x) and in Azure SQL Database, we recommend using ALTER INDEX REORGANIZE instead of ALTER INDEX REBUILD for. Make sure to thick the Enabled checkbox. Hi, Added an index maintenance job (Hallengren) to a database (SQL Server 2016) with a few large tables. 環境SQL Server 2012参考前回の以下の投稿に若干手を加えて作成しました。SQL Server の指定したテーブルのインデックス名を取得する方法ストアドプロシージャを実装するC…too slow alter index reorganize/rebuild (with online) I have 2 similar SQL Server installations on 2 similar GCP projects. Reorganizing an index uses minimal system resources. Eliminate all deleted rows. You need to assemble the SQL prior to calling SP_ExecuteSQL, i. Reorganizing an index uses minimal system resources and is an online operation. Microsoft recommends fixing index fragmentation issues by rebuilding the index. Rebuilding an index means that a whole new set of pages is allocated for it. Here we would like to introduce you to the three most common ways of how to Reorganize and Rebuild Indexes. The first rebuilds a single index on a table and the second rebuilds all the indexes on the table. Categorize fragmentation percentage – Microsoft suggests that we. dm_db_index_physical_stats function is the preferred tool to use to check index fragmentation on any CA or DA database table. 0. regarding reorganize - there is an exception to this - if you have columnstore indexes that are frequently subject to deletions or that are subject on inserts in small batches then running reorg. When an index becomes skewed, parts of an index are accessed more frequently than others. we have 5 databases in our instance, in that 2 databases are online and remaining are in offline. EventID, MAX (b. Select Maximum degree of parallelism, and then enter some value between 1 and 64. A more intelligent way is to check fragmentation first then rebuild or reorganise. So now once you have identified the high fragmentation level in your database, which could. The easiest way to reorganize an index is to simply drop and recreate the index using the DROP and CREATE INDEX commands. In this book "Professional SQL Server 2012 Internals and Troubleshooting" I've read this passage: "If you see indexes that have more than 10% fragmentation, you need to decide whether to reorganize them or simply rebuild them. INDEX index-name Specifies the index to use when reorganizing the table. 3 and a half hours later, it's not finished. If the index’s design or your SQL Server edition doesn’t allow for that, it’ll perform the last resort – an offline index rebuild. When rebuilding offline your index is completely unavailable, but the operation is faster than that online. e. Script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation. The index can be rebuilt using ALTER INDEX REBUILD T-SQL command. x) implemented major performance improvements for these index operations. ALTER INDEX . . Select the Compact large object column data checkbox to specify that all pages that contain large object (LOB) data are also compacted. The query causing the block is in the format: ALTER INDEX [indexName] ON tableName REORGANIZE. This can be demonstrated by small test. Large object data is data with the image, text, ntext, varchar (max), nvarchar (max), varbinary (max), or xml data type. . The algorithm to build an index from scratch is different to that which maintains it as data arrives so these non-clustered indexes will be unfragmented too. (About 1 TB of data including myIndex (non. In SQL Server, the ALTER INDEX statement is used to modify an existing index. e. Right-click the Maintenance Plans folder and select New Maintenance Plan. I have an index on a . ) Index Size is greater than 5 MB's. Index Rebuild : This process drops the existing Index and Recreates the index. How Fragmentation Hurts SQL Server Performance. Index rebuilds look. SQL Server 2008 provided a special type of column called a spatial column, which is a table column that contains data of a spatial data type, such as geometry or geography. Poorly designed indexes and a lack of indexes are primary sources of database application bottlenecks. It is compatible with all versions of SQL Server 2005 , 2008 , 2012 , 2014 and 2016. 2. 000. There are two files attached - one for versions prior to 6. index rebuild/reorganize frequency. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on all Indexes for all Tables and Views and "Compact Large Objects" is checked; and then it UPDATE. set @SQL = 'alter index ' + QuoteName ( @IndexName ) + ' on ' + QuoteName ( @TableName ) + ' rebuild';. Resolution: Reorganize and rebuild indexes. index_type_desc AS IndexType, indexstats. Hi, The widely used thresholds are: Reorganize if fragmentation is between 10-30%. For maintenance i create a procedure that: -Shrink Database file (if is enabled) -Shrink Database log file. 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. Here is the image for additional clarity. Basically every hour I query the dm_db_index_physical_stats dynamic management view and if an index is between 5% and 30% fragmented I. The query causing the block is in the format: ALTER INDEX [indexName] ON tableName REORGANIZE. The below T-SQL statement can be used to retrieve these wait types: 1. -- Compute fragmentation information for all full-text indexes on the database SELECT c. Basically, an index rebuild copies the index to another place. The length of time the rebuild takes is related to the size of the index, not the amount of fragmentation in it. This is especially true when you have less than 4 pages. USE AdventureWorks; GO ALTER INDEX ALL ON Production. H. Over here it will display all the indexes of the table and you can just click OK. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. He has the best practices coded into his scripts, so it should serve you well. Rebuilds are generally faster. First of all you need to find the fragmentation percentage for the indexes in a database or table. As visible in the above image, REBUILD and REORGANIZE are the two available choices to play out the trim operation over the page. Right-click the table on which the full-text index is defined, select Full-Text index, and on the Full-Text index context menu, select Properties. In both these scenarios they are blocked by session 54 – the index rebuild. SQL Fool (Michelle Ufford) has a great script to do this for you - all done and well tested by many users. First, let’s look at the index in this tutorial with sample code to create a non-clustered SQL Server index on a sales table. The fully qualified name or alias in the form: schema. It checks the SQL Server licences, if Enterprise it will rebuild the index online else it. Lock durations are short and will cause minimal blocking of queries. The maintenance plan. The following index operations require no additional disk space: ALTER INDEX REORGANIZE; however, log space is required. You can drop and create indexes at will. x), REORGANIZE is only used to compress CLOSED rowgroups into the columnstore. It's a great piece of work - it allows you to define fragmentation levels for which you. 1. REORGANIZE Tells SQL Server to perform a master merge, which involves merging the smaller indexes created in the process of indexing into one large index. On large tables, that may be a while and not frequent enough. From a transaction log standpoint, reorganize index generates a lot more small transactions and can lead to substantially more resource-consumption than a rebuild. Microsoft recommends Index Rebuild operation to defrag indexes if the fragmentation level of your index is greater. This happens approx. WHY ? Rebuilding is massive time resource consuming, reindexing more fast than rebuildIn SQL Server Management Studio, in Object Explorer, expand the server. In the SQL Management Studio Console, expand Management > Right Click Maintenance Plan and select Maintenance Plan Wizard. Is this possible to do? In earlier versions of Microsoft SQL Server it could cause system slowdown to reorganize or rebuild a large index. The length of time the rebuild takes is related to the size of the index, not the amount of fragmentation in it. So it can remove some fragmentation - but only on a limited scale. This requires the DBA to create such a maintenance job. SQL: Procedure for rebuild and reorganize indexes like Microsoft says. Indexes with small number of pages (<1000) will usually. The more the fragmentation you need to rebuild if its less you can reorganize. After finding out almost all databases on my SQL Server had fragmentation over 40%, I decided to do an index rebuild on all tables using a fill factor of 80. DROP INDEX when you are dropping a nonclustered index. Reorganizing queue indexes. GO. Click OK. ;WITH cte AS ( SELECT object_id, index_id, partition_number, rows, ROW_NUMBER () OVER (PARTITION BY object_id, index_id, partition_number. You have to do some research and tailoring of how and when to do it based on your environment, though. Reindex the WSUS database. REORG INDEXES/INDEXES command reorganizes indexes. We leave default values here as well. You can also set a threshold so that it only considers indexes over a certain size so you're not doing unnecessary rebuilds on tiny indexes. Obtain the script from the source DB (SMO or MSSQLTips ), change the FILLFACTOR and PAD_INDEX then execute against the new DB. I'm just using the reorg index task that is in the maintenance plan designer GUI. [FactInternetSales] REBUILD PARTITION = ALL WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics. There are some good reasons to Rebuild an index, like updating statistics. is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation AND ss. Specifies the table to reorganize. Default SQL Server value is 0 or 100%, which means that no free space should be left on each page. It may also take longer to REORGANIZE a highly fragmented index than to REBUILD it. Start the Microsoft SQL Server Management Studio client, and then log in to it. The simple reason which comes to my mind is rebuild should be done only when index is fragmented. We have decided to use the following code to compute a fragmentation % for each full-text index. To create a new job, right click on SQL Server Agent, select New and then Job. If you look into the sys. The sys. _in_percent > 50 AND ss. index_id > 0 -- Indexes. Rename. However, reorganizing can be a "more online" operation and is sometimes preferred, even for. The select statement is blocking on the clustered index (object id 446624634) whereas the object_name() is blocking on the table (object id 462624691). Test all three and see which is fastest and which gives the least index fragmentation. On a data partitioned table, you can reorganize a specific nonpartitioned index on a partitioned table, or you can reorganize all the partitioned. Right-click on the index and select Rebuild. It's better to do reorg on Nightly. Rebuild if > 30%. I don't think Windows SQL Server Maintenance has this option yet. You can still run the index reorg/rebuild as part of your maintenance scripts. Reorganizing an index uses minimal system resources. No, there is no auto-magical defragging of indexes. For instance, in the Maintenance Plans of SQL Server 2017, it is possible to rebuild only those indexes where fragmentation level is higher than a specific percentage and has more pages than specified in "Page Count". As well as maintenance plans, SQL Server Agent jobs are also a handy way to automate and schedule index defragmentation jobs in SQL Server. On a data partitioned table, you can reorganize a specific nonpartitioned index on a partitioned table, or you can reorganize all the partitioned indexes on a. Large tables should be in their own full-text catalog. ALTER INDEX ALL ON [table_name] REBUILD; Additionally, please note that index reorganization is an online operation and index rebuilding is an offline operation unless explicitly specified as an online. In this article. m. August 21, 2010 at 11:54 pm. To work around this, try the following methods: Method 1 ( recommended ): Limit the number of full-text indexes in the same catalog. This article introduces the concept of fragmentation and discusses two ways of managing index fragmentation - reorganizing and rebuilding. Using SQL Server Managed Lock Priority for Online Index Rebuilds. If the index. The query result is matched against the full-text index. For example, one of the indexes with a page_count of 967 has a fragmentation percentage. For a dedicated SQL pool table with an ordered CCI, ALTER INDEX REBUILD will re-sort the data using tempdb. SQL Server - Reorganize and Rebuild Indexes :.