maintainence plan drop the index | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

maintainence plan drop the index

I have read that the maintainence plan drop the index and create new indexes on each table.
I am wondering how it will know about the user defined indexes once it dropped these user created indexes.
DBCC DBREINDEX is the process which runs in the same fashion, and when you specify under the MP then it will take care of the reindexing. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I really don’t know how SQL does it behind the scenes, but I have written scripts to do exactly that. The way I accomplished it was to write a script which performs the following (in this order):
1. Build the Create Index statements to re-create the indexes after they have been dropped, and output those via a print statement.
2. Build the Drop Index statements to delete the indexes and output those via a print statement. The script can then execute the drop statements, or they can be executed manually. The indexes are still "known" in the Create script that was produced prior to deleting the indexes. Steve
If "create index" will have the same information as existing indexes,what is the use of deopping and reindexing by running this script containing create index statements.Will it be helpful .Pls explain.
The SQL Server manages itself in managing the CREATE/DROP indexes when the DBCC DBREINDEX is executed. And if you want to manage this part manually then better to drop and recreate the indexes using DROP and CREATE statements. Refer to thishttp://www.sql-server-performance.com/optimizing_indexes.asp about managing and tuning indexes effectively. If you do not mention to drop the index… such as IF EXISTS (SELECT name FROM sysindexes
WHERE name = ’employeeID_ind’)
DROP INDEX emp_pay.employeeID_ind
GO
.. then you will get error while exeucting the CREATE INDEX statement and in order avoid such explicit information, DBCC DBREINDEX is the best option to use.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>