DBREINDEX or INDEXDEFRAG of a few indexes/window

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)

DBREINDEX or INDEXDEFRAG of a few indexes/window

Author

Topic

kriki
Starting Member

14 Posts

Posted - 2007-03-31 : 09:24:22

I have a quite big database (150GB) that is running 24/24 7/7 with each day 2 windows of about 2 hours in which people are working little.
So doing a complete indexrebuild during the weekend is not possible. The only option is the rebuild/defrag indexes is during those windows. Problem is:I can't do it on all tables because it takes too much time.
So I was thinking about creating several SQL scripts and each of those is doing some tables/indexes. Problem is that I have a few hundreds of tables and if I want to move an index from 1 window to another, I have to change those scripts manually. Quite a time costing and error-prone procedure.

So I was hoping there is some tool that shows me all existing indexes and give me the possibility to add them to a script and once I have done that, generate the scripts to be run.
I have launched some searches on the forum but I didn't find any mentioning of the existence of such a tool.
Does someone know of such a tool or have a better idea how to do this?

Thanks,
Alain Krikilion

IF Debugging = removing bugs from program THEN programming := putting bugs in program;

MohammedU
Posting Yak Master

145 Posts

Posted - 2007-03-31 : 11:29:46

create a two tables one based on fragmentation which requires to be indexed and other will be indexed tables....
Write a script with cursor or loop to run the index based on the following...

declare @startdate datetime, @enddate datetime
select @startdate = getdate()

select tablename from FragTables f where name not exists
(select name from indexedtables t where t.name = f.name)

begin

dbcc dbreindex....
insert into indexedtables...
select @enddate = getdate()
if datediff (hh, @startdate, @enddate) >= 2 break
end

.....

MohammedU

kriki
Starting Member

14 Posts

Posted - 2007-04-02 : 03:35:29

I was thinking to do that, but I hoped there was already some tool for it.

IF Debugging = removing bugs from program THEN programming := putting bugs in program;

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-04-02 : 10:29:22

SELECT OBJECT_NAME(dt.object_id), si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID('My_database'), NULL, NULL, NULL, 'DETAILED' )
WHERE index_id <> 0) as dt --does not return information about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
and avg_fragmentation_in_percent>30
order by avg_fragmentation_in_percent desc

=============
The above query helps you find the level of fragmentation in your sql server.
SQL 2005 Enterprise Edition lets you run defrag while keeping them online.
You may also want to consider putting all indexes on different file group.
Based on the use/abuse, you can split the indexes into multiple file groups.
Then you can take indexes on one filegroup at a time and re-index them, whereby the entire database will not be affected. I will try find some write up for you on that.

Regards
Paresh Motiwala
Boston, USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources