Rebuild & Reorg Indexes in SQL Server

Hi All,
 I tried to defrag my indexes on a particular table by trying  REBUILD(DBREBUILD) & REORG(INDEXDEFRAG).
Don't know the reason it is taking more time to execute and I tried to see how much timeit may take, I Observer even after like 2 hrs of start time, it keep on executing.
What might be the reason and how can I get rid of this and defrag my Indexes?
I have to Defrag those Indexes, as we have user reporting about the reports taking more time to execute.

Will it work if I try to schecule a REORGNIZE the Indexes using the Maintanence Paln?
saratcmAsked:
Who is Participating?
 
mdagisCommented:
I would recommend to first run a DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS

After that try to run the following code for the defrag:
Set NoCount On

DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @indid <> 255 DBCC INDEXDEFRAG (0, @TableName, @indid)
  FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
  FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

Open in new window

0
 
chapmandewCommented:
Which operation are you running?
0
 
saratcmAuthor Commented:
I tried first running REBUILD & waited to see until 1hr,
After I tried REORG and still it is keep on executing form 2hrs.
So stopped that too & found only 39% completed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
chapmandewCommented:
Rebuild is an offline operation, so it will block all table operations. Don't run during busy times. How large is your db?
0
 
saratcmAuthor Commented:
Yes, I runnned it in the night during our window timings only and since it is taking a while, I thought of giving a try with Reorg as it is a online operation.
0
 
chapmandewCommented:
How large is your db?
0
 
saratcmAuthor Commented:
60 GB/ 10GBis free in that
0
 
lcohanDatabase AnalystCommented:
Actualy REBUILD WITH(ONLINE=ON) may be longer but is not OFFLINE and works much better than REORGANIZE or INDEXDEFRAG at the table/index level. SQL Books on line has examples how to determine which index needs REBUILD/REORG (I usualy do only rebuild) and pay attention to the treshhold levels as a 10% of a 100 million rows table is way different than 10% of a 100,000 rows table. Good luck.
0
 
saratcmAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.