Question about maintaining indexes

Question

Question about maintaining indexes

Ryan-209402

Hall of Fame

Points: 3707
More actions
July 2, 2007 at 1:05 pm

#179676

I'm wondering how often it is necessary to reindex in SQL 2005. This could be scheduled during downtime. For example, is it recommended to reindex all indexes for all user DBs once a week? If so, are there any well-written scripts out there to do this? Any downside to this? Thanks for any advice you can offer. I'm just looking for any basic things that I can automate to improve performance.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 1

BTW, if anyone knows of any guidelines on how to configure built in scheduled job tasks for (Rebuild Index Task, Update Statistics Task, Check Database Integrity Task, etc) please let me know. Is it alright to put these all in one scheduled job or is it better to place each in its own scheduled job? Thanks.

David.Poole SSC Guru Points: 76035 More actions · Answer 2

This a question to which the answer is "it depends".

The only way of telling is by using DBCC SHOWCONTIG to see how fragmented the tables are.

If you have tables on which heavy delete activity takes place then it is worth having a regular reindex job.

I have some databases in which tables are reindexed every night, some where it is done once a month and others where they are hardly every reindexed.

Kalen Delaney gave a warning about defragging the indexes too often. She gave an example where an index was only 1% fragmented but in order to correct the 1% the other 99% had to be reorganised to allow the fragmented 1% to be positioned

LinkedIn Profile

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

in sql 2005 there dynamic management view which give info about state of a index. you cud use them in ur script and check whthr index(es) require defragging or reindexing. Then you can reindex or defrag the index.

"Keep Trying"

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 4

Thanks for the advice. I found this article on using dynamic management views to maintain indexes:

http://msdn2.microsoft.com/en-us/library/ms188917.aspx

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

dbcc showcontig is a piece of junk and the system view is a lot better and a lot more accurate

we run maintenance every night where only the indexes needing to be rebuilt get rebuilt. run the system view and dump data to a table. run a script to grab indexes past a certain frag level and rebuild them.

one db we rebuild anything over 5% just because sometime it fails and if we really busy we might not fix it for a few days. gives us breathing room.

others we rebuild over 20% since when you have over 150 million rows in a table it takes like 30 minutes per index. on a clustered index it took like 4 hours to reorganize one time

Simon Facer SSCertifiable Points: 5427 More actions · Answer 6

Eric Peterson posted a nice stored procedure to handle defragging / rebuilding indexes in this months SQL Server mag: http://www.sqlmag.com/Article/ArticleID/96059/sql_server_96059.html. I won't publish his code here, it's not mine to publish.

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 7

Thanks for the link to Eric's article, will definitely check it out...

As mentioned, I am looking into Dynamic Management views and there is a script on http://msdn2.microsoft.com/en-us/library/ms188917.aspx that will reorganize/rebuild all indexes based on whether they are less than or greater than 30% fragmented. When I run this script it will out a list of indexes in the current db which were reorganized/rebuilt. However, if I immediately run the script again it will give the same output for the same tables and if I run the following script to check the fragmentation level it still shows the indexes as being fragmented.

SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

Any idea as to why the indexes are not being reorganized/rebuilt? Thanks.

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 8

I'm still puzzled as to why the indexes are not being reorganized/rebuilt. After performing a rebuild of all indexes on a table (either with TSQL or in SSMS) I still see the same % fragmentation on the index. Is there anything special that needs to be done? We are running SQL 2005 Standard Edition so we can't perform online indexes. Any ideas? Thanks!

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 9

I am experiencing the same issue as Ryan. I have run the DMV

sys.dm_db_index_physical_stats and got the information that my index was 80% fragmented. I rebuilt the indexes and it showed success but when I rerun the query, I still get the same fragmentation values. I don't understand either why the index is not being rebuilt?

Thanks!

Thanks!
Bea Isabelle

rubes SSCrazy Points: 2058 More actions · Answer 10

Not to beat a dead horse, but I have had the same issue as well. For that reason I've stuck to a reindexing script using DBCC SHOWCONTIG.

Regards,
Rubes

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 11

how big are these tables? small tables will almost always stay fragmented. i have tables with tens of millions of rows and alter index along with the system view works like a charm. i keep detailed records of what is fragmented, when and which indexes are defraged every night.

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 12

Hi SQL Noob and Rubes,

Could you provide examples of scripts that you use successfully? Thanks.

Ryan

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 13

are you're fragmented tables small tables? do u have a fill factor set?

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

rubes SSCrazy Points: 2058 More actions · Answer 14

This reindexes anything with fragmentation over 10%.

set

nocount on

declare

@command varchar(1000)

declare

@ID int

CREATE

TABLE #SHOWCONTIG (

ObjectName

CHAR (255),

ObjectId

INT,

IndexName

CHAR (255),

IndexId

INT,

Lvl

INT,

CountPages

INT,

CountRows

INT,

MinRecSize

INT,

MaxRecSize

INT,

AvgRecSize

INT,

ForRecCount

INT,

Extents

INT,

ExtentSwitches

INT,

AvgFreeBytes

INT,

AvgPageDensity

INT,

ScanDensity

DECIMAL,

BestCount

INT,

ActualCount

INT,

LogicalFrag

DECIMAL,

ExtentFrag

DECIMAL)

INSERT

#ShowContig

EXEC

('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_Indexes')

-- Remove system tables

Delete

from

#ShowContig

Where

ObjectName LIKE 'dt%'

OR ObjectName LIKE 'sys%'

OR IndexID not between 1 and 254

-- Determine tables to optimize

select

RowID = identity(int, 1, 1), command = 'DBCC DBREINDEX (''' + rtrim(ObjectName) + ''', ''' + rtrim(IndexName) + ''', 100) WITH NO_INFOMSGS ' + '-- ' + cast(CountRows as varchar(100))

into

#ReindexList

from

#ShowContig

where

LogicalFrag > 10

or ExtentFrag > 10

while

1=1

begin

select top 1 @command = command, @ID = RowID from #ReindexList

if @@rowcount = 0 break

exec (@command)

delete from #ReindexList where RowID = @ID

end

drop

table #showcontig

drop

table #ReindexList

Regards,
Rubes