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

Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
EugeneZ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
TRocex

ASKER
thanks I saw it but it is very complex so I could not understand anything
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
EugeneZ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anthony Perkins

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck