troubleshooting Question

rebuilding indexes

Avatar of TRocex
TRocex asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
5 Comments2 Solutions205 ViewsLast Modified:
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 
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
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros