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
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 [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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER