Link to home
Start Free TrialLog in
Avatar of TRocex
TRocex

asked on

rebuilding indexes

I would like to rebuild indexes on the some tables of a database then rebuild indexes where fragmentation is higher than %30 so I tried to do it by using
USE [AdventureWorks]
GO
ALTER INDEX [AK_SalesOrderDetail_rowguid] 
 ON [Sales].[SalesOrderDetail] 
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = 
 OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
 SORT_IN_TEMPDB = ON, ONLINE = OFF )
GO 

Open in new window

but it is very hard to do it for every index (where fragmention>30) on some tables for a special database step by step and it takes too long time so how can I rebuild the indexes where fragmentation>30 on tables for a special database by simple way with the index syntax like this above?

I dont know it may be script and sp then I will make a daily job for these indexes to rebuilt but first of all, I need to simplify it..I am rebuilding indexes in the list resulting where fragmentation>30 after executing like below with step by step..
Use BTN_DB_Labordaten
 
GO
 
SELECT
 
      ps.object_id,
 
      i.name as IndexName,
 
      OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
 
      OBJECT_NAME (ps.object_id) as ObjectName,
 
      ps.avg_fragmentation_in_percent
 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ps
 
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
 
WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
 
ORDER BY avg_fragmentation_in_percent desc

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TRocex
TRocex

ASKER

thanks I saw it but it is very complex so I could not understand anything
Avatar of TRocex

ASKER

and also indexes is always changing in the list so I am reducing the indexes for example  there is 25 indexes to be rebuilt  in fragmention index list,so after rebuilding indexes, there is remaining 15 indexes but ten minutes later again, fragmention list is increasing and they are becoming 18 or 19 or 20..why are they increasing? how will I zero them in the fragmention index  list?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks I saw it but it is very complex so I could not understand anything
What is so complex about it?  You create some Stored Procedures with the given script and from then on you just run the Stored Procedure with the appropriate parameter.  All the hard work has been done for you and you are just reduced to picking the correct threshhold and whether you want to do it online or not or whether you want to rebuild or reorganize, but the defaults should be more than adequate.