• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

create and dropping indexes

Hi Folks,

is it necessary or even good practice to keep everyone off your database while you are create or dropping indexes?

0
david_32
Asked:
david_32
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
rowansmithCommented:
Depends on your database load and weather droping an index will send your CPU through the roof and effect user performance.

Creating is the same but less critical, e.g., create indexes during times when user load is low.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>is it necessary?
no, normally not.

>good practice?
it is good practise to do it while low load of the database (as mentioned)

if the index is important for user performance, you might consider creating a "replacement" index first, then drop the old one.

note: dropping an index by itself is not a problem, but if the index is important for user queries, the missing index will result in full table scans (and as mentioned, send the CPU and IO activity to higher levels). such an index should not be dropped, anyhow...
0
 
david_32Author Commented:
I believe when you rebuild and index ALTER INDEX <indexname> REBUILD.  everyone need to be off the system - why would that be? (as far as I know a REBUILD recreates the index).

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
rowansmithCommented:
When you rebuild a primary XML index, the underlying user table is unavailable for the duration of the index operation.

So you may effectively want to ensure that all users are off the system.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Why are you trying to drop and create indexes instead you have the direct option of DBCC REINDEX.

DBCC DBREINDEX can rebuild all the indexes for a table in one statement. This is easier than coding multiple DROP INDEX and CREATE INDEX statements. Because the work is performed by one statement, DBCC DBREINDEX is automatically atomic, whereas individual DROP INDEX and CREATE INDEX statements must be included in a transaction to be atomic. Also, DBCC DBREINDEX offers more optimizations than individual DROP INDEX and CREATE INDEX statements.

DBCC DBREINDEX is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table. If the clustered index is being rebuilt, an exclusive table lock is held. This prevents any table access, therefore effectively making the table offline.

To perform an index rebuild online, or to control the degree of parallelism during the index rebuild operation, use the ALTER INDEX REBUILD statement with the ONLINE option.

So you have the option to do it both ONLINE as well as OFFLINE.
The decision in your hands now..

The below code will help you to apply REINDEX for all tables in the Database which would make your work more simpler. Replace the DatabaseName and FIllFactor value '90' of your choice before running it.
USE DatabaseName --Enter the name of the database you want to reindex
 
DECLARE @TableName varchar(255)
 
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
 
OPEN TableCursor
 
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
 
CLOSE TableCursor
 
DEALLOCATE TableCursor

Open in new window

0
 
david_32Author Commented:
hi rregan,

I was just making the point that index rebuilding drop an index and when you do this you need to be off the system. Therefore it would make sense you need to have everyone off the system when you drop an index by itself. Thanks for the script though it may be handy :)
 
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes.. Index rebuild can be done only when the tables involved are not used by anyone and hence you need a minimum timegap for it.

But not necessarily, you can rebuild indexes online if you have provided the option while creating the index ONLINE = ON. This will work when users are in the system
0
 
david_32Author Commented:
I think everything is cleared up then.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now