REINDEX process - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

REINDEX process

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2010-12-11 : 15:50:27

I have the script which runs absolutely fine for Reindex process.

however, I believe I do have so many tables have clustered indexes built on them; and not all the tables were changed between reindex process (may be the weekly duration). Which means Not all tables have CHANGED in the weekly duration. But still all these tables were subjected to get into Reindexed irrespective.

May anyone advise as to how to do ONLY THOSE INDEXES which have changed during the week long time.

Thanks all.

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2010-12-11 : 19:56:56

I don't have much experience, but recently i did some research on indexes......Here is a script with which you can find out the latest statistics updates on the indexes of all the tabels in a database.

select
OBJECT_NAME(si.OBJECT_ID) as TableName
, si.name as IndexName
, stats_date(si.object_id,si.index_id) as statisticsUpdateDate
FROM SYS.INDEXES AS si WITH (NOLOCK)
LEFT JOIN SYS.OBJECTS AS so WITH (NOLOCK)
ON so.OBJECT_ID= si.OBJECT_ID
WHERE index_id>0 -- omit the default heap
AND OBJECTPROPERTY(si.OBJECT_ID,'IsMsShipped')=0 -- omit system --tables
AND NOT (SCHEMA_NAME(SCHEMA_ID)='dbo'
AND OBJECT_NAME(si.OBJECT_ID)='sysdiagrams') -- omit --sysdiagrams
ORDER BY statisticsUpdateDate

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-12 : 11:11:41

Rebuild indexes that are fragmented. I'll usually rebuild when they're over 25 or 30% fragmentation. But I won't bother with very small ones, for example less that 100 pages or index_depth < 3.

This will show you the fragmented indexes.


declare @db int
set   @db = db_id()

SELECT      object_name(s.object_id) tbl, i.name idx, p.rows,
      s.index_type_desc, index_depth, avg_fragmentation_in_percent,
      s.fragment_count, s.page_count
FROM  sys.dm_db_index_physical_stats (@db, null, NULL, NULL, NULL) s
join  sys.indexes i
on    i.object_id = s.object_id
And   i.index_id = s.index_id
JOIN  sys.partitions p
On    p.object_id = s.object_id
And   p.partition_number = s.partition_number
And   p.index_id = s.index_id
LEFT JOIN  Sys.dm_db_index_usage_stats us
On    us.object_id = s.object_id
And   us.index_id = s.index_id
WHERE avg_fragmentation_in_percent > 20
And   s.database_id = @db
And   s.index_type_desc <> 'HEAP'
Order By
      avg_fragmentation_in_percent desc;
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-12 : 11:18:25

I like this query too (though it will run a while on a large db) as it shows indexes usage stats too.

Occassionally I'll target indexes to drop based on the result


declare @db int
set   @db = db_id()

SELECT	object_name(s.object_id) tbl, i.name idx, p.rows,
	s.index_type_desc, index_depth, avg_fragmentation_in_percent,
	s.fragment_count, s.page_count,
	user_seeks, user_scans, user_lookups, user_updates,
	last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM	sys.dm_db_index_physical_stats (@db, null, NULL, NULL, NULL) s
join	sys.indexes i
on	i.object_id = s.object_id
And	i.index_id = s.index_id
JOIN	sys.partitions p
On	p.object_id = s.object_id
And	p.partition_number = s.partition_number
And	p.index_id = s.index_id
LEFT JOIN
	Sys.dm_db_index_usage_stats us
On	us.object_id = s.object_id
And	us.index_id = s.index_id
WHERE	avg_fragmentation_in_percent > 20
--And	page_count > 99
And	s.database_id = @db
And	s.index_type_desc <> 'HEAP'
Order By
	avg_fragmentation_in_percent desc;
GO

I have the page_count filter commented out here...again, I want to see not only fragmented indexes, but usage stats. If I find an index with a low number of pages (or rows) it is likely that it isn't used at all and I want to know that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-12 : 16:38:25

dbalearner, don't worry about what tables have changed. Only worry about fragmentation and number of pages. Paul Randal indicates to ignore indexes with fewer than 1000 pages, but he said that it actually may be higher (near 10,000 pages).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources