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

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-02 : 00:02:30

Dear All,
while the developers entering the data continuously to different tables, the data might me written initially at different places of datafile, so that it is defenetly time consuming while selecting the data. i heard that table reorganization is a concept for these, and weekly once DBA should do this(in oracle). i've no idea how todo that in sql server....
any guidence?

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-03 : 23:30:19

Rebuild clustered index to defrag table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-04 : 00:15:03

And do so only if it's fragmented. No sense in wasting resources when the clustered index isn't fragmented and therefore doesn't need to be rebuilt.

Here's the script that I wrote and use on all of my SQL 2005 production databases:
http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspx

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

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-09 : 03:38:14

Dear Tara,
i'm getting error while executing the script....
incorrect syntax near )
the places is this one.
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-09 : 04:35:00

If you check BOL under DBCC SHOWCONTIG , there is a script to allow defrag given a certain level of fragmentation.
Make sure you run it at a quiet production time

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-09 : 20:08:38

quote:
Originally posted by sunsanvin

Dear Tara,
i'm getting error while executing the script....
incorrect syntax near )
the places is this one.
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)

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

Are you running it on a database with compatibility level of 90?

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

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-10 : 03:46:57

yes

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-10 : 15:58:14

I've only seen this error when the database compatibility level was set to 80.

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

Nazri
Starting Member

8 Posts

Posted - 2008-01-10 : 23:45:51

Thank you TARA.....
i've checked it for compatibility level 90 it is working and 80 it is giving that error.
actually for some table alterations, i'm generating script from sql server 2000, and kept in version control. if i made the compatibility level for sql server 2005, will it give error?

tara....one complement for you......in our native language(telugu), the meaning of TARA is star.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-11 : 18:01:24

You can not run my script on SQL Server 2000 or on a database with compatibility level of 80. These commands were not available until 2005/90.

You also can't change a database's compatibility level to 90 when you are on SQL Server 2000. 90 is not available until SQL Server 2005.

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