April 26, 2011 at 7:50 am
we have a huge table around 50 lakhs record. Recently we moved the database from one server to another. Thereafter the queries to the table are very slow. Rebuild indexes itself takes more time and could not be completed. I mistaken disabled two indexes and how to enable it? rebuild index just freeze other operations!!
Thanks for your time
regards
KRS
April 26, 2011 at 7:59 am
You need to rebuild the index.
Disabling an index drops the entire b-tree, just leaving the metadata behind. It takes a rebuild to recreate that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2011 at 8:03 am
Yes, that was a great mistake to disable it. but how to rebuild index without slow down other db actions
April 26, 2011 at 8:04 am
Can i use CREATE INDEX WITH DROP_EXISTING.
It is an non-clustered, non-unique index. When I try rebuild, the database itself is locked and no other actions can be performed.. Pls help me out and save my day
April 26, 2011 at 8:09 am
Enterprise edition? If so, you should be able to do the rebuild online. If not, you just have to accept the impact from your mistake. It won't lock the entire database, just blocks anything trying to modify that table.
Otherwise wait until a maintenance window and do the rebuild then
Create with drop existing will have the same effect as rebuild unless it is run online (Enterprise edition only)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2011 at 8:35 am
If you have LOBs, you won't be able to rebuild indexes online. Also keep in mind that online index rebuilds will lock the table at the beginning and end points of the rebuild (referenced from Paul Randall's blog: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%28830%29-online-index-operations-do-not-acquire-locks.aspx).
Hope that helps!
April 26, 2011 at 2:28 pm
5 million records (50 lakhs) is not a huge number. There may be other reasons the queries are slow. Create new index during non peak time.
May 2, 2011 at 11:42 pm
Try Updating statistics of all tables with full scan
May 2, 2011 at 11:48 pm
sgvv (5/2/2011)
Try Updating statistics of all tables with full scan
How is that going to help re-enable the index?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2011 at 12:18 am
Accepted that his is not going to reenable the indexes.
But since krishnaroopa has mentioned that he has disabled two indexes by mistake. This implies that othe indexes might exist, and update statistics might fix it. Moreover he has moved the DB to another server, which requires Update statistics be Run.
Once he gets the required downtime he can re-enable the indexes
May 3, 2011 at 12:32 am
sgvv (5/3/2011)
Moreover he has moved the DB to another server, which requires Update statistics be Run.
Moving a DB from one server to another does not require update stats, not unless he's moving from one version of SQL to another.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply