SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

94 thoughts on “SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

  1. Hey dave,
    i wanted to find out that in my databases( tables, all) which indexed is being often used, and by which queries.
    some hints to that,
    i know i can run execution plan for queries and find what indexed are being used, but since then i have to run for all the queries.

  2. Hi,
    thank’s for your very helpfull site.
    just one remark : it seems to me that you have reversed the 2 sql ‘alter index’ statements in relation to paragraphs.

    best regards,
    JM

  3. Pingback: SQL SERVER - Index Reorganize or Index Rebuild Journey to SQL Authority with Pinal Dave

  4. In the live server, Is it recommendable to do it.

    Should we make two databases and do it on one and then take a backup and restore to another.

    What you suggest in the ideal situation.

    Please advise.

    Rana

  5. I think the T-SQL statement was switched between REORGANIZE and REBUILD in (SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script). Can you update them to avoid confusion.
    Thanks.

  6. Pingback: SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance Journey to SQL Authority with Pinal Dave

  7. Hi and thank you for your so useful and well organized site!

    I have a question regarding indexes rebuild/reorganization: if I modify one of the table columns (just increasing its length), should I rebuild/reorganize index based on this column?

    Thanks!

  8. Job Name Reorganize Index task MaintenancePlan for User DB
    Step Name Subplan_1
    Duration 00:00:15
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: MICROSOFT\SQLAdmin. …n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:25:38 PM Progress: 2008-08-21 16:25:47.04 Source: {F2EF9F9E-923D-4BE6-B6A1-376E9171553A} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp”.: 100% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “USE [xxxx] “.: 5% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “ALTER INDEX [PK__ENUM_LIST_DEF__07F6335A] ON [dbo]“.: 11% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “USE [xxxx] “.: 16% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “ALTER INDEX [PK__ENUM_LIST_ENTRY__0BC6C43E] ON [db”.: 22% complete End Progress Progress… The package execution fa… The step failed.

  9. HI dhaka,

    please post the error message that was displayed in the particular step which failed. only that will give a clear picture.

  10. HI, i have a index with 93% fragmented, then rebuild it, and now this same index has 73%, why ?
    What can i do to have percentage more low?
    Drop and Create?
    When rebuild were made with ONLINE=ON, affect that?

    Thanks for your help!!!

  11. when i was rebuilding my database i had this error number -1073548784..
    Executing the query “ALTER INDEX [KBA_ODPV_PR] ON [dbo].[@BA_ODPV] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
    ” failed with the following error: “The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
    The statement has been terminated.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

  12. @Joshua

    I strongly suggest, you take a look at BOL about Indexes.

    A clustered index (leaf level page) contains real data of that object.

    In simple words, when you Rebuild Clustered Index it will put a lock on the table, because Index has to be rebuild meaning data was stored in one fashion earlier but by issuing Rebuild Index (for Clustered Index) command, you are asking SQL Server to again rebuild whole index i.e. store data in another fashion that is easily accessible . When data is being organized in another fashion, no user can access that object (Tables) until rebuild index process completes.

    Long Story short, you cannot rebuild Clustered indexes when users are connected to database and accessing that particular table/ object.

    Error message you posted is self explanatory, explanation is, Rebuild Index command is trying to put a lock on that table, but that table is being used, that is why sql server is not able to put a lock on that table. Until SQL Server dont put a lock on that table, it cannot actually rebuild clustered Index.

    For Non-Clustered Index, there is no such restriction. You can rebuild Non-Clustered Indexes when users are connected to the database. Reason behind this is, Clustered Index ( Leaf level Pages) do not real data, but contains pointer to Clustered Index.

    Solution:
    1. Either Reorganize index instead of Rebuilding Index.
    2. If you have SQL Server 2005 Enterprise Edition, then you can rebuild index on a table when user is accessing that object, all you have to do is, set ONLINE = ON ( in your rebuild command, right now it is ONLINE = is set to OFF). This would increase Rebuild Index time significantly ( Which is not good). Make sure you test this in Dev/ Test Environment before implementing this in PROD.
    3. Try rebuilding indexes when no or very less users are accessing database. If not, rebuild indexes in maintenance window.

    ~ IM.

  13. @ Imran Mohammed

    sir thanks for your reply actually here’s what i did,
    i had the maintenance last May 2 @7PM up to May 3 @midnight, the error occured at around 5PM on May 3, i had 5 Maintenance,

    Check Database Integrity Done 8sec
    Shrink Database Error 27sec
    Reorganize Index Done 15:14:27
    Rebuild Index Error 0sec
    Update Statistics Done 6:23:46

    “Long Story short, you cannot rebuild Clustered indexes when users are connected to database and accessing that particular table/ object. ”
    “Try rebuilding indexes when no or very less users are accessing database. If not, rebuild indexes in maintenance window.”
    - NO USERS CONNECTED DURING THE MAINTENANCE. all users are locked and cannot access the database.

    “If you have SQL Server 2005 Enterprise Edition, then you can rebuild index on a table when user is accessing that object, all you have to do is, set ONLINE = ON ( in your rebuild command, right now it is ONLINE = is set to OFF). This would increase Rebuild Index time significantly ( Which is not good). Make sure you test this in Dev/ Test Environment before implementing this in PROD.”
    - I ALSO SET ONLINE = ON, but with different error message, and still was not able to rebuild the index.

    here are the actual errors for SHRINK DATABASE AND REBUILD INDEX:

    REBUILD INDEX ERROR
    ERROR NUMBER: -1073548784
    ERROR MESSAGE:
    Executing the query “ALTER INDEX [KBA_CCLSDPA_PR] ON [dbo].[@BA_CCLSDPA] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON ) ” failed with the following error: “Online index operation cannot be performed for index ‘KBA_CCLSDPA_PR’ because the index contains column ‘U_DprTypID’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

    SHRINK DATABASE ERROR
    ERROR NUMBER: -1073548784
    ERROR MESSAGE:
    Executing the query “DBCC SHRINKDATABASE(N’ALC’, 10, TRUNCATEONLY) ” failed with the following error: “A severe error occurred on the current command. The results, if any, should be discarded.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

    i appreciate the solution sir, but i already did it but still it didn’t work.

    thank you so much again…

  14. @Joshua

    As per BOL,while rebuilding index ,option

    ONLINE=ON will fai if there is XML index or index consist of Large object data type columns inclyding image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml.

    Check your indexes to figure out if you really need ONLINE=ON option

  15. @Imran Mohammed
    @Sandy

    Thanks for your responses, i did everything you said but unfortunately, it didn’t, do u think there is an issue with MS SQL Server 2005? do i need to upgrade to SP3 since i am only using SP2?

    thanks…

  16. I clearly read the dis-advantages of REBUILD – and MS says Reorganize if between 5-40% and rebuild if higher.

    1. What are the disadvantage(s) of REORGANIZE
    2. Why wouldn’t you REORGANIZE if the fragmentation was over 50% (I assume the answer is poor use of resources, but I don’t see that written anywhere)
    3. Wouldn’t a REORGANIZE of a Clustered Index also take a table lock?

    Other index question – we have a (PK) Clustered Index on table that is 24×7 – we start from scratch (empty) every quarter – and by week 4 the fragmentation is over 80% (yes, i know its a poorly designed PK and Clustered Key – its a vendor product). The kicker is they don’t want me take the app offline for a short maint window – is there any way to define the index with 4 leaf levels – since thats how many it will grow to?

  17. Hello,

    My question is as follows: I have created a script to list all indexes with a fragmentation percent greater than 10. Next, this script will rebuild or reorganize these indexes based upon the fragmentation percent. However, the same indexes keep showing up in this report. So, I tried to manually rebuild with the same results, the framentation percent remain the same. These indexes are not clustered indexes. Would you be able to explain why this may be happening?

    Herb

  18. Need help with regards to this REBUILD ISSUE AGAIN…
    @Imran Mohammed
    @Sandy

    Executing the query “ALTER INDEX [KBA_CCLSDPA_PR] ON [dbo].[@BA_CCLSDPA] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
    ” failed with the following error: “Online index operation cannot be performed for index ‘KBA_CCLSDPA_PR’ because the index contains column ‘U_DprTypID’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

    Note:

    I tried to do the maintenance in our:
    TEST SERVER – NO ERROR FOUND
    PRODUCTION SERVER – and i found the error above

    TEST SERVER – NO ONE IS CONNECTED ON IT
    PRODUCTION SERVER – there are 3-5 users connected but less transaction because i did it at night…

    hope you can help me with this problem…

    i been experiencing this problem since i started posting here…

    my last option is: TO DISCONNECT ALL USERS AND RUN THE REBUILD_INDEX for 3 hours…

    SHRINK_DATABASE ISSUE HAS BEEN RESOLVED…

    thanks…

  19. I’ve got a new variation on this particular error (on SQL2008)

    Executing the query “ALTER INDEX [PageUrlID_FK] ON [dbo].[WebParts] REO…” failed with the following error: “A severe error occurred on the current command. The results, if any, should be discarded.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

    It’s a SharepointDB, and this is about 1/3 way down the T-SQL that the job generates automatically.

    ALLOW_PAGE_LOCKS is on.

  20. @jane

    good day, if i am not mistaken this kind of error usually happens when the table is locked or being used by certain user or users… i’ll try to disconnect all the users tonight and test the rebuild index without any users accessing the database… if it works i’ll post the result here as soon as possible…

    to: Pinal, Imran Mohammed, Sandy,
    please correct me if i am wrong…

    thanks…

  21. @joshua

    Any joy? It failed again this week. That said, it succeeded once after I’d rebuilt the index…

    It’s a reorganize index that’s failing, which shouldn’t be affected by users being logged in.

    Thanks

  22. Hi Penal

    i am getting the following error at one of my sql server databases

    “[298] SQLServer Error: 1204, The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. [SQLSTATE HY000]”

    while finding out the solutions on internet I found that by changing the locks configuration through sp_configure locks this problem can be resolved. but in one of the MS notes it is said that it should be set to 0. and I found it is already set to 0. what should I do in this case

  23. is this rebuilding or reorganising indexes affect sizes of differential backups?? because i read some where if database if too huge (like 7gb) and if we donot consider rebuilding/reorganising indexes into consideration while taking diff bkps, resultant diff bkps are huge in size?? is that correct?

  24. Simple, Straightforward and brilliant! Thanks for not only explaining the difference but giving recommendations as to when each option should be used.

    God bless.

  25. Hi Dave.

    How does SQL 2005 use ram when performing a DBCC DBREINDEX? I have a 300GB database that I moved over from SQL 2000 to SQL 2005 SP3 (64 bit) on a test server. Nearly all 12GB on the test server was consumed by SQL 2005 during the reindex. It takes 22 hours to completely reindex the entire database. I have split the trans log off to a seperate array than the db file is on and that did improve performance some. I am now curious if extra memory will speed up the process. Thanks in advance.

  26. Hello Dave,

    I have a 90 Gb database. The rebuild index is run as a weekly job. Whenever the Indexes are rebuilt the database size goes from 90 Gb to 170 GB, leaving with very less free disk space and leaving more free space in the database. Is there any option where I can avoid the increase in size of the mdf file ? I use the option sort_in_tempdb ON, but that doesnt help much either.

    Thanks,
    Deepti

  27. Hi Derek,

    To find which are all the Indexes are used and unused guess this might help you out.

    Please check it out and let me know your comments.

    USE [YOURDATABASENAME]
    GO
    SELECT O.NAME AS OBJECT_NAME, I.NAME AS INDEX_NAME,
    I.TYPE_DESC, U.USER_SEEKS, U.USER_SCANS,
    U.USER_LOOKUPS, U.USER_UPDATES
    FROM SYS.INDEXES I
    JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
    LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS U ON I.OBJECT_ID = U.OBJECT_ID
    AND I.INDEX_ID = U.INDEX_ID
    AND U.DATABASE_ID = DB_ID()
    WHERE O.TYPE ‘S’ — NO SYSTEM TABLES!
    ORDER BY (ISNULL(U.USER_SEEKS, 0) + ISNULL(U.USER_SCANS, 0) + ISNULL(U.USER_LOOKUPS, 0) + ISNULL(U.USER_UPDATES, 0)), O.NAME, I.NAME

    Thanks,
    Manjunath

  28. You didn’t really EXPLAIN the difference between the two.

    Other than being able to reorganize XML indexes online, is there any difference in the final result when you use Rebuild vs. Reorganize? Is Rebuild recommended for heavliy fragmented tables because it’s faster than reorganizing heavliy fragmented tables?

    Sorry, but I don’t see much “explaining” done here.

    • Me too, I miss 2 very important things:

      1. rebuilding an index automatically also updates the statistics! If you reorganize, you should always update your statistics afterwards!

      2. some mention the option ONLINE of rebuilding, but be aware that some cursors will fail when they are launched when the index rebuilding is done “error: schema modification was detected”

  29. Hi all,
    why REBUILD index causing the DB to grow ?
    I rebuilt 1 off the indexes and DB grew from 270MB to 550MB .
    My understand is that REBUILD should shrink or at least no allocate more space .
    I was running REORGANIZE and the DB didn’t grow

  30. good day…

    I just want to know what to do when there are clustered index and non-clustered index in one table (i found out that the reason why i cant rebuild online is because of these – clustered and non-clustered in one table)

    some field says rebuild and the other reorganize.

    but when i tried to rebuild it i got an error because there are clustered index.

    should i use rebuild index (online or offline)
    or
    should i just reorganize it?

    thanks…

  31. why my log files become full ?, from 5GB to 50GB when rebuilding all the indexes ? and after it finished, my log file didn’t go small…
    I use FULL recovery model….
    is there any way to prevent this ?

    and I’ve wierd situation, sometimes, after I rebuild index the fragmentation of that index is still high, 70%
    but when I do backup first, and doing rebuild index after that, the fragmentated index become small

  32. Supposing my database is in Full Recovery mode. This means that doing an index rebuild will take some considerable size on the transaction log. (Index rebuild drops and recreates – using Create Index – all indexes). What about index reorganization? Is there also a considerable size impact on the transaction log?

    Thanks!
    George.

    • depends on the fragmentation and the size of the table. With reorganizzing you must be aware that this theoretical example will reorganize the whole table because it uses a kind of bubble-sort method: if the logical first page is physically the last.

  33. Hi every one. I’m not a dba specialist, but a .net developer.
    As i understand, Index Rebuild drops current index values and recreates new ones. In this case, what about the joined and related tables where the value is used as FK in another table ? This also needs to be updated to the new values?
    Appreciate any explanation.

    • A FK has nothing to do with indexes. It is however recommended to put an index on a FK, but the FK itself is not an index, only a constraint.

  34. I see a lot of people asking why thier databases and log files grow when they rebuild indexes. Mr. Pinal has explained that an index rebuild actually drops and then recreates the index. This means space is consumed in the process and if your database is set to full, this also means transactions are written to the log. As a part of your index rebuilding, you should backup both the database and the log in order to reclaim space used in the process.

  35. How did Microsoft came with these thresholds?

    if frag_level30% then REBUILD

    The idea is that I dont have Enterprise Edition’in production environment and I want to be able to do index maintenance without blocking the tables. And so my only option is to ALTER INDEX … REORGANIZE.

  36. I do a very compreshensive index maintenance which allows for rebuilds and reorgs based upon certain parameters of fragmentation. I also give the option to do Online Rebuilds. My process logs all fragmentation before and after the rebuild or reorg. Of course a rebuild reduces the fragmentation greater than a reorg. But I have found one problem. When I choose to do an Online Rebuild, the fragmentation actually is higher after the rebuild than it is before. The version of SQL Server is 2008 10.0.2734. Is there an explanation as to why the Online Rebuild would cause increased fragmentation whereas a regular rebuild or reorg would not?

    MS

  37. This is very helpful but when I try to defrag my indexes, nothing happens…I drop and recreate them and they are still fragmented…and yes I have the update statistics command after rebuilding or reorganizing…if any of you guys know why this is happening, please let me know.

    • What size are the tables?

      Small tables (for me around 200 pages) will always have high fragmentation. For these tables you don’t really gain much by having an index on them anyway.

  38. I have set up maintenance Plan for Rebuilding Indexes on a nightly basis. It runs successfully, but does not de-fragment the indexes. I have had a discussion on this with Microsoft Engineers. and They informed me that Rebuilding Indexes with Maintenance Plan sometimes does not de-fragment the indexes. But I am not happy with this statement. What do you think of it?

  39. Pingback: SQL SERVER – Non-Clustered Index and Automatic Rebuild – Quiz – Puzzle – 11 of 31 « SQL Server Journey with SQL Authority

  40. Hi Pinal,

    Thank you.

    Can you please explain the conditions under which SQL Optimizer uses a wrong index than what it should logically? For example, using a clustered index scan instead of using a NC which was created for that particular column. For example, I am searching for VENDORS and there is a NC index on VENDORS but SQL is using a Clustered INdex that is on an identity column.

    Hemant

  41. i am confused, when i used penal command on adventurework Db its works fine.
    but when i used the following on sql2005 enviornment it always give me error, please advise

    USE DB;
    GO
    ALTER INDEX ALL ON dbo.mytable REBUILD
    GO

    error:

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘INDEX’.

  42. Hi Pinal,

    My Full-Text search takes a long time to perform the search operation. The table on which the Full Text is enabled, contains 97 Lac records and every 1 hour 3 to 4k records are imported to this table.

    During the FTS, CPU shows 100% performance on the database server with high CPU consumption of this process: MSFTESQL

    The rebuilt on the full text catalog is performed every night. I also tried to do a reorganize every-night but it did’nt helped me.

    Could you please guide me what should I do in this case and will Rebuild or Reorganize of FTI , boost performance.

  43. What benefits do I lose if I rebuild index on 20% fragmentation or reorganize index on 90% fragmentation? Except availability, because I do it at night, so availability is not important for me.

  44. Hi Pinal,

    I need clarification w.r.t rebuilding or reorganizing indexes. Based on avg_fragmentation_in_percent >5 and 30 (rebuild)

    SELECT B.name as TableName,c.name IndexName,A.OBJECT_ID,avg_fragmentation_in_percent
    FROM SYS.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
    JOIN SYS.objects B ON A.object_id=B.object_id
    JOIN SYS.indexes C ON B.object_id=C.object_id
    WHERE c.name is not null

    After rebuilding or reorganizing the indexes “avg_fragmentation_in_percent” remains same. This will change after rebuild or reorganizing the indexes or it will remain same?

  45. Hi Pinal,

    Thanks for the article.

    I reorganize index(composite cluster index) the table(around 200GB).
    After reorganize its size increased 400GB.
    Could you please help me with this.

    Thanks in Advance.
    Any suggestion are welcome

  46. I have a question here.. when rebuilding or reorganizing indexes.. is there a possibility of data loss?

    I’m planning on implementing this on our server but afraid because of the possible loss of data.

    Thanks in advance.

  47. Hello,

    please tell me why i got a lock with REORGANIZE?

    E.g.:

    – conn 1
    begin tran
    alter index idx_nci_orderid on Sales.OrderDetails reorganize

    – conn 2
    select orderid, productid, unitprice, qty, discount from Sales.OrderDetails where orderid = 10248
    – locked

    – conn 1
    commit tran

    – conn 2 – lock has been released

    Why it is said that REORGANIZE would not lock resources?

    Thanks!

  48. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  49. This is my first time to face indexing over table; so I want to know if I create non-clustered index over a table which is having some records; do I need to rebuild the index of the same?

    Thanks in Advance

  50. I run the “Rebuild Indexes” using Management Studio. After days, finally all the indexes are showing Status “Success”. However, the Progress still indicating as “executing”. The dialog has been there for days….Is this normal? Or should i kill the process?

  51. I’m running a rebuild index from management studio plan . The DB is about 700-800 GB and the job run for a couple of days . I’d like to know :
    - how can I check the % of completed job ?
    - how can I stop it if needed ( simply click on the red x on the window )
    - what happens if the rebuild is stopped ?

  52. Is there any difference between recreating an index and rebuilding and index.
    Wherever I searched online I only find difference between rebuilding and re-organising, but I want to know if recreating index and rebuilding index are different?and if yes which is better to perform after we drop indexes on a table.

  53. You have mentioned While Rebuilding index, the existing index will get dropped and it will get recreated again. I did Rebuild index for a column and i couldnt find any Drop Index query in my SQL Profiler. Can u plz explain, how it gets dropped and again recreated? In SQL Profiler it has “ALTER INDEX [TESTIDX] ON [dbo].[STUD] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )” only…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s