Default SQL Server value is 0 or 100%, which means that no free space should be left on each page. 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. 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. The first steps of configuring the Reorganize Index task is quite similar to the Rebuild Index task. However, my SQL Server license is Standard and I can't rebuild indexes online. When reorganizing an index, SQL Server acquires an Intent-Exclusive lock on the index B-tree. Release unused space. Applies to: SQL Server. Books Online for ALTER INDEX (Transact-SQL) Free index maintenance tool (and much more) Tags; index fragmentation;SQL Server 2016 allows customer to defragment this index using the familiar ALTER INDEX <index-name> REORGANIZE command instead of using a heavy hammer approach of rebuilding the index. Right-click the Maintenance Plans folder and select New Maintenance Plan. Update Statistics. I'm currently using the approach described here: sys. If the page_count is less than 1000 (or whatever you decide) then you should ignore the index. Remarks. My problem is that the reorg is running for a very long time. 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. You could also refer another query which may be helpful to you. It is compatible with all versions of SQL Server 2005 , 2008 , 2012 , 2014 and 2016. 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. Reorganize Index Task Forum – Learn more on SQLServerCentral. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Columnstore indexes are the standard for storing and querying large data warehousing fact tables. SQL Server 2005 Index Rebuild/Reorganize. However, imagine that you have seven years of data. SQL Server NULL Index. If you search for index rebuilding, you will find many complex scripts on the web, but this can also be done. You might ask why you should do this yourself, since this is what the tuple-mover. Yet, we do it at least every 2 weeks, and often times more often. For rows that contain LOB columns, the normal page will contain a reference to the LOB page. . Table locks alone would not make sense, the point with REORGANIZE is that the table is mainly online while the operation is running. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index. An index reorganize holds an intent-exclusive table lock throughout the operation, which will only block shared, exclusive, and schema-modification table locks. ALTER INDEX ALL ON [dbo]. avg_fragmentation_in_percent FROM sys. For the data (clustered index and heap) you'd have to export outside SQL Server, truncate the table, shrink and then import the data. name AS IndexName, indexstats. REORGANIZE seems to work as well as REBUILD all the time. 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. 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. Problem. Yes. I don't think Windows SQL Server Maintenance has this option yet. When investigating we found that the index reorganise for the clustered index of FooDetail takes between 90 minutes and 120 minutes. I disagree with the 30% rule. 3. SQL Server ALTER INDEX Syntax. One of the possibilities that we have in SQL Server to keep our indexes defragmented, is the index reorganize operation. REORGANIZE is an "online" operation, which means it doesn't take a big Sch-M lock at the beginning of the operation to have exclusive. I have many DBs that are currently used for insert and delete. The SQL Server Database. Also, in earlier versions the granularity of control was less refined. Ideally , microsoft suggests that reorganize should be used for index fragmentation between 5 and 30 % and rebuild for > 30%May 5, 2010 at 8:00 am. In SQL Server, you "might" run into issues with "updating primary key". Here is a sample script to reorganize any index in SQL Server. Or if it swapping places of two pages. In this one case, it happens to make the query. The page swapping can CRUSH you (and the tlog and data files with writes) when you have. In Object Explorer, Expand the database that contains the table on which you want to reorganize an index. the year and month columns. This job needs to be scheduled and ran on a weekly basis due to large data load. I suspect the reorganize is compacting the index after the rebuild and thus moving quite a bit of data around. Some operations that took ~90s now take ~6mins. In other words, zero percentage fragmentation:. 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. If you choose to compact large object data, the statement uses the. First it’ll try an index reorganize, which is an online operation. He has the best practices coded into his scripts, so it should serve you well. 2. 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”. Syntax ALTER INDEX index_name ON table_name. 1. Which is the best method to reorganize sql server database. Thanks for your. Reorganize Index. Thanks for your reply. Jan 11 at 14:24. And also, avg_fragmentation_in_percent value says percentage of logical fragmentation (This is. You need to do more research but basically, reorganize as often as needed to keep your fragmentation under 20-30% until you can rebuild it during off-hours. You can also see if a reorganize will help until you can do a full rebuild. . You should always do some form of intelligent rebuilding - i. Index Rebuild : This process drops the existing Index and Recreates the index. TEST REORGANIZE We get our open delta store again, which will cause locking. Expand the table on which we want to reorganize the indexes. If we have 10-30% fragmentation a REORGANIZE is performed, and a REBUILD is performed when we have greater than 30% fragmentation. Hi Team, Today when I go through some of the SQL Server performance videos, found one interesting video (Please find the link below), where he said that rebuilding indexes is a very expensive thing and it will clear the cache every time an index rebuild happens, (It means that we indirectly killing the SQL Server everything we. Please refer to SQL Server Maintenance Plan Reorganize Index and Update Statistics Tasks to get more information about how to design the maintenance plan. As a general (widely accepted) rule of thumb is . ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] ON [Sales]. We should reorganize indexed with. I used the sp_who2 procedure to see which queries were waiting, and which other query they were blocked by. DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table. 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. The query causing the block is in the format: ALTER INDEX [indexName] ON tableName REORGANIZE. x) and later. Unlike REORGANIZE on user tables, REORGANIZE on a queue is always performed as an offline operation because page level locks are explicitly disabled on queues. We have a database server (2016 SQL Server), that we have added a step of 'rebuilding indexes' to the deployment process. Designing the SQL Server Reorganize Index Task. On the SQL Server Maintenance Plan Wizard page, select Next. Suggest you investigate alternatives, such as [Ola Hallengren's SQL Server maintenance tools][1] - free to use. Recall the paper example from above: a rebuild would be like reprinting the document in the correct order and trashing the old ones. If not specified otherwise, the procedure uses the fill factor that is already set for each index. 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. Select Maximum degree of parallelism, and then enter some value between 1. First of all you need to find the fragmentation percentage for the indexes in a database or table. I am not sure I am picturing this correctly. This is because the Rebuild Index task will destroy all the indexes and build it again from scratch, without considering the fragmentation percentage. Connection. In it, enter the Job name, owner, optionally Category. 2. dm_db_index_physical_stats DMV). 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. 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. 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. OBJECT_ID) AS TableName, ind. As visible in the above image, REBUILD and REORGANIZE are the two available choices to play out the trim operation over the page. Backup Up Database (Full)4. The second set of options, in figure 8-5, rebuilds only a single partition of a clustered columnstore index and optionally changes the compression type. Just kidding – although the evidence does point to that. The first part of this tip focuses on SQL Server Enterprise Edition to reduce the duration for index maintenance for off-line rebuilds. The discussion of columnstore indexes thus far has focused on clustered columnstore indexes that target a primarily OLAP workload. Exclusive page locks are taken on individual pages only while those pages are being manipulated. 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. dm_db_index_physical_stats (under the Examples -> D section: Using sys. REORGANIZE [ WITH ( LOB_COMPACTION = { ON | OFF } ) ] Applies to: SQL Server 2016 (13. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK. In order to rebuild the index SQL Server still needs a copy of the data and must reorganize the data as the index is rebuilt. An index rebuild will always build a new index, even if there’s no fragmentation. I’m talking about the guidance which is: if an index has less than 1000 pages and is in memory, don’t bother removing fragmentation. •DatabaseBackup: SQL Server Backup •DatabaseIntegrityCheck: SQL Server Integrity Check •IndexOptimize: SQL Server Index and Statistics Maintenance. If you want to rebuild an index the syntax is below: ALTER INDEX. The discussion of columnstore indexes thus far has focused on clustered columnstore indexes that target a primarily OLAP workload. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance. This is larger than the minimum row size. 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. If the command that's running is a reorg, you can just kill it. You should intelligently do index reorg and rebuilds. The Reorganize Index task encapsulates the Transact-SQL ALTER INDEX statement. 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. You have two maintenance commands for indexes: Reorganize. 例えば、10 % 以上 30 % 未満であれば再構成 (Reorganize)、 30% 以上であれば再構築 (Rebuild) するとすれば、上記の. USE AdventureWorks; GO ALTER INDEX ALL ON Production. This is great if you want to release some space quickly. Optimize SQL Server non-clustered indexes and queries by considering index fields, compound indexes and SQL Server statistics. I still see questions about SQL Server 2000/2005 pop up on Stack Exchange. Okay, maybe not that. How can INDEX rebuilds be going parallel when MAXDOP is set to 1? They shouldn't be. it will reorganise it. Rebuilding your indexes will slow queries down. Index rebuilding and reorganizing are the two methods to maintain indexes and improve database performance. Highly fragmented, the application performs well. Well, I’d wager some of it is backwards compatibility. the reorganize index also facing some problem like it is successfull once in three runs and twice it is failing. 11. between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG. Answers. 7. Index should be reorganized when index fragmentation is between 10% to 40%. (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 Misconceptions training course on Pluralsight. Rebuild/Reorganize working fine. こんにちは。開発部基幹SREチームの廣瀬です。 弊社では、システムの一部にSQL Serverを使用しています。 本記事では、SQL Serverにおけるインデックスのメンテナンス方法である再構成と再構築について、それぞれを実行した場合のクエリ性能の比較結果をご紹介したいと思います。 比較を実施. In SQL Server 2017, however, there is a resumable-online index rebuild feature. You could also refer another query which may be helpful to you. The first and most popular method is to rebuild indexes. Thanks, I will try this next time. Yup, that is one perfectly valid way. SELECT * FROM sys. You can apply a new fillfactor when you rebuild an index. From Reorganize and Rebuild Indexes. How Fragmentation Hurts SQL Server Performance. December 8, 2009. It doesn’t work on the intermediate pages between the root and the leaf. Therefore you do not need to update statistics OR reorganize an index if it gets rebuilt. Reorganizing an Index should be done when you have elevated amounts of white space within your index (i. For more information, see sys. On large tables, that may be a while and not frequent enough. indexes. As a generally accepted good practice reorganize only when ragmentation of index is between 5 to 30 % for anything more than that rebuild the index. ”. Last night I killed the REORG on the clustered index after almost 6 hours of execution. Here’s how you can rebuild or reorganize fragmented indexes:-- Rebuild index to reduce fragmentation ALTER INDEX [YourIndexName] ON [YourTableName]. 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%. Last night I killed the REORG on the clustered index after almost 6 hours of execution. For now, I have just turned off the weekly index reorganize task which is not really a good long term solution. For more information, see Data Types (Transact-SQL). . Applies to: SQL Server. Backup Up Database (Full) 4. Last weekend the index maintenance took more than 5 hours and the full backup was running at the same time. This task uses the ALTER INDEX. This means that an index can be rebuilt without knowing the structure. #969726. By default, SQL Server uses a 100% fillfactor and tries to fill up all the pages in indexes as close to full as it can. Index Reorganize During Database Full Backup. WITH (ONLINE=ON). Note: You cannot reorganize an index (primary key, or unique key) that has an Allow Page Locks. Shrink File. Sorry No idea why this happened. This blog entry I came across a while back will explain it much better than I can. I want to reorganize or rebuild indexes. It is causing other queries to wait, even simple ones like: SELECT * FROM tableName WHERE indexedColumn = @value. Since we are asking SQL Server to rebuild the clustered index on a quite large table twice, it will take a few. In order to resolve the index reorganization issue, we will enable the row and page level locking by altering the index as shown below: USE MSSQLTipsDemo GO ALTER INDEX [PK_Product] ON [Production]. ApexSQL Defrag is a powerful application used for scheduled analyzing and defragmenting of SQL Server database indexes. Here's another script to add to the list. Because I was blamed for the slow SQL Server mainly because I. 2. Para índices columnstore em SQL Server (a partir de SQL Server 2016 (13. REORGANIZE cannot be specified for a disabled index or. Test all three and see which is fastest and which gives the least index fragmentation. Let’s move on to creating the Reorganize Index task via the Maintenance Plans and then Update Statistics task. It's not documented to have a particular deadlock priority, and it does make data changes. The syntax for rebuilding indexes is very simple, we just add the "WITH ONLINE=ON" clause to the ALTER INDEX command. Expand Management in the Object Explorer> Right click Maintenance Plans> Maintenance Wizard> Create Plan for Rebuilding Indexes. Yes -. It will have a parameter called @MaintenanceMode that can be set to either ‘INDEX’ or ‘CATALOG’ in order to choose the way we want to maintain our Full-Text indexes. indexstats. Sc (Comp Sci). GO. Quick explanation: An online Rebuild, rebuilds the indexes on the tempDB making it available to the queries while it is being rebuilt. Add a comment. Index rebuilding process uses more CPU and it locks the database resources. 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. The harder but much more. Creating or dropping the table’s clustered index causes all its nonclustered indexes to be rebuilt. CREATE PROCEDURE dbo. The Reorganize Index task encapsulates the Transact-SQL ALTER INDEX statement. An index rebuild operation cannot be interrupted without it rolling back everything it’s done so far – it’s atomic – all or nothing. 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. Usually, you should rebuild the index if it has a fragmentation greater than 30% and reorganize it if it has less than 30% fragmentation. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. There are two ways of doing that: (i) by using the ‘ DBCC SHOWCONTIG ‘ command, and (ii) by using the ‘ sys. INDEX_REORGANIZE Reorganizes the index. Merging the full-text index fragments can improve performance and free up disk and memory resources. > 5% and < = 30% ALTER INDEX REORGANIZE. The best practice recommendation is to reorganize your index if you have 5-30% of fragmentation, and only rebuild it if it has more than 30% fragmentation. We have experience performance issue if fragmentation is high and page count <1000. Regards. Clean Up History. Reorgs defragment the leaf level of clustered and. Index maintenance in my opinion is not one of them. Mohamad Mahmoud Darwish. For more information, see Data Types (Transact-SQL). Expand Databases, and then expand the database that contains the full-text index. Rebuilds automatically run statistics on your indexes. DROP INDEX when. You can also slow down fragmentation by inserting data in the order of the index. This method can help when Change_tracking is Auto. As a result of rebuilding an index, SQL Server also updates the statistics for the indexes that were rebuilt. But your index will also use more space. Reference to below article and discussion, rebuild only if page count >=1000If you want to try : I would like you to use READ_COMMITTED_SNAPSHOT isolation level for this operation and see if you succeed. Edit 2: Nikita added a good point on moving the data to a different filegroup as an option to exporting it outside SQL Server. Reorganize: Reorganize indexes with between 11-30 percent fragmentation. When to rebuild and when to reorganize indexes. You can still run the index reorg/rebuild as part of your maintenance scripts. Rebuild the NCIs. In the Name box, enter the name of the maintenance plan you're creating. Note that the time it takes to complete the operations depends on the size of the database and how fragmented the indexes. indexes. The documentation for alter index, for example, shows that it accepts an index_name, not an expression that might evaluate to an index name. The query causing the block is in the format: ALTER INDEX [indexName] ON tableName REORGANIZE. One of the most important return field with sys. Surly not, the Maintenance Plan was created in the current Server itself. ALTER INDEX REBUILD on an truncated and therefore empty table serves no purpose, so you need to amend your Plan A. The problem is that the size is getting out of control, I can see up to 99% fragmentation in the Primary Key clustered indexes. First, we will start with the "Maintenance Plan Wizard":The rebuild command will defragment all those intermediate pages. After month the same query took up to 20-30 s. When using columnstore indexes, the delta store may end up with multiple small row groups after inserting, updating, and. If the index you wish to reorganize is not listed in the “Indexes to be reorganized” section, ensure that it has been added to this section and then click the “OK” button. Rebuild Indexes. Yes -. dm_db_index_physical_stats dm function. Let’s move on to creating the Reorganize Index task via the Maintenance Plans and then Update Statistics task. 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. Rebuilds are generally faster. SQL Server index fragmentation is unavoidable, but you can minimize the negative effects of fragmentation on database performance. 000 rows. This means you can grant alter table on every table of interest that already exists. g. That can be found using the STATS_DATE function. This. To solve this, I want to use the REORGANIZE operation every time. Here is a procedure what I wrote. Over here it will display all the indexes of the table and you can just click OK. Expand Tables. There are two options to fix fragmentation. Larger indexes have more intermediate levels and pages. partition_number The number of an existing index partition to rebuild/reorganize. A page is a small section of a database, typically an 8kb chunk. > 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*. Clean Up History. 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. So you should explicitly set a deadlock priority, and perhaps a retry loop, to enforce the behavior and document it. Select the server connection to use when performing this task. " Disagree with this as I have a system in. 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. just like many things in RDBMS. File type (Data), group (PRIMARY) Name () Shrink Action = reorganize pages (=move to front), Shrink File *. EXEC msdb. Jonathan’s right in that fragmentation changes the query plan. Index Maintenance is one of the major task for a DBA. Mar 8, 2021, 2:47 PM. From stackoverflow page ( How Often should the indexes be re-build in sql-server DB?) i got this query : SELECT t. インデックスの断片化の状態がわかったら、それによってインデックス (index) を再構成 (Reorganize)するか、または再構築 (Rebuild) するか決定します。. A clustered index exists, along with 3 non-clustered indexes. MyTable REORGANIZE WITH(LOB_COMPACTION = ON). There is all reason to only rebuild index that are fragmented, and a good maintenance. #1637994. The tool allows you to quickly collect index fragmentation statistics and detect databases that require maintenance. However, recently this approach has. 5% to 30% -> ALTER INDEX REORGANIZE Over 30% -> ALTER INDEX REBUILD WITH (ONLINE = ON)* However, we have noticed that even with really high fragmentation (over 95%) on large and small tables, REORGANIZE works fine. The query processor uses statistics on your data to help determine how. 3) SQL Server locks acquired with SQL Server online index rebuilds. A developer coming onto a project with out of date tools is a common cause of this. The first rebuilds a single index on a table and the second rebuilds all the indexes on the table. . 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%. Select * from sys. Rebuild Index. index rebuild/reorganize frequency. Largest table has around 500. One of the major reasons I wrote DBCC INDEXDEFRAG for SQL Server 2000 was as an online alternative to DBCC DBREINDEX. indexes ind ON ind. 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%. Reorganize (SQL Server indexes, primary keys, and unique keys) The Reorganize Indexes dialog lets you reorganize an entire index, primary key, or unique key or a single partition of that object. USE AdventureWorks; GO ALTER INDEX ALL ON Production. Create a new server connection to use when performing this task. The process uses the existing pages only and does not allocate new ones, but it does compact the index pages. To create a new job, right click on SQL Server Agent, select New and then Job. Both create/rebuild of columnstore index is an OFFLINE operation at this time but you can defragment columnstore index ONLINE. Sc (Comp Sci). Obviously this isn't the same as index reorganize, so I'm still trying to think what does reorganize file technically mean. Similarly, removing fragmentation in a. When you reorganize an index, SQL Server sorts the index pages and releases the unused space within the index pages. It doesn't work the way you think it does. SORT_IN_TEMPDB means that SQL server will use tempdb to allocate the temporary space as opposed to allocating space in the user database whose index is being rebuild. " But following the Russ's reply the REORGANIZE is a more time expensive operation the REBUILD one. New. Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update. To fix the fragmentation either rebuild or reorganize the index on the table. set @SQL = 'alter index ' + QuoteName ( @IndexName ) + ' on ' + QuoteName ( @TableName ) + ' rebuild';. This article *recommends* REORGANIZE as the primary method to be used and then links to code that uses the same. Yes. Create jobs to automate maintenance – create a SQL Server Agent job that will automate SQL index maintenance. After rebuilding indexes, the application performs badly. Just a reminder that index reorganize is always online (all indexes are available during defrag) and index rebuild can be made also online (WITH. Check total_pages in sys. Either can allow maintaining a high average percent full in the data. The first and most popular method is to rebuild indexes. ALTER INDEX CCI_TEST on DBO. This will invoke the Tuple Mover , which will turn closed delta stores into compressed rowgroups. Indexes are created on columns in tables or views and provide a quick way to look up data based on the values of those columns. Sorted by: 5. in this instance you can avoid re-inventing the wheel by making use of sql-server-index-and-statistics-maintenance – Stu. Reorganizing tries to put the leaf level of the index back in logical order within the pages that are already allocated to the index. Reorganizing an index is a faster, lightweight version of rebuilding and the indexes remain online. " But following the Russ's reply the REORGANIZE is a more time expensive operation the REBUILD one. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. We can however get it back to a compressed segment by issuing the following command we saw previously. 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. I used the sp_who2 procedure to see which queries were waiting, and which other query they were blocked by. The.