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

General SQL Server Forums

New to SQL Server Programming

table reindexing

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-15 : 00:20:05

Hi All,
is it really improve performance that making table reindexing?

what i mean to say is i've one script, which will automatically drops all the indexes in a database, and reconstruct them with the same name.

is it really worth doing that?.....

thankyou very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 00:23:37

Yes.

Poor planning on your part does not constitute an emergency on my part.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-15 : 00:41:58

Dropping and recreating the indexes is not necessary. This will be more time consuming than reindexing (via 2000 or 2005 methods) the fragmented indexes only.

So you should reindex, however not using the approach you mention.

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

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-15 : 00:48:45

OKOK
actually i'm not dropping just reindexing with dbcc dbreindex(@tablename, ' ',90)
is that worth doing?

Vinod
Even you learn 1%, Learn it with 100% confidence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-15 : 01:02:56

Yes but only if the index is fragmented. To figure this out, you have find the information in DBCC SHOWCONTIG.

SQL Server 2005 makes this whole reindexing so much easier. We've now got a DMV that shows us fragmentation levels.

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

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-15 : 01:28:14

Dear Tara,
what is that DMV.....please provide me some tips for best performance

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-15 : 02:11:28

how to find the fragmentation levels

Vinod
Even you learn 1%, Learn it with 100% confidence.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 03:50:51

quote:

tkizer...

Yes but only if the index is fragmented. To figure this out, you have find the information in DBCC SHOWCONTIG.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-15 : 03:54:01

i've used the showcontig and pasted in a new thread. please suggest me regarding the performance

Vinod
Even you learn 1%, Learn it with 100% confidence.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-11-15 : 08:39:26

If you check DBCC SHOWCONTIG in SQL Books Online, there is a very useful script that checks which indices are fragmented at a cetain level , then does a defrag on those indices.

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-15 : 11:00:17

quote:
Originally posted by sunsanvin

Dear Tara,
what is that DMV.....please provide me some tips for best performance

DMVs are in SQL Server 2005 and you've posted in a 2000 forum. If you want to see how to defragment indexes in 2005, check out my blog. I have an isp_ALTER_INDEX stored procedure that does all of the work for you. It just has one small bug that I haven't had the time to fix yet. The bug just has to do with reindexing a clustered index when there is LOB data in the table. Most people won't have this scenario, so it won't impact them.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources