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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.