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.
| Author |
Topic |
|
hannah00
Starting Member
31 Posts |
 Posted - 2008-04-09 : 15:09:36
|
| I am an Oracle Database, in Oracle, we can calculate the percentage of defragmentaion of the table. Once we determined, we can rebuild the table and or course, rebuild the index afterward.1. does SQL work the same way (you can rebuild the table and rebuild the index afterward)? 2 does anyone have some scripts to calculate the percentage of fragmentation of the DBs?thank |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-09 : 15:14:28
|
| 1)Yes you can. you can check properties of index and find fragmentation level.OrDBCC SHOWCONTIG 'tablename'This will give you internal and external fragmentation . |
 |
|
|
hannah00
Starting Member
31 Posts |
Posted - 2008-04-09 : 16:38:39
|
| does anyone have scripts to determine the percentage of the fragmentation on each objects of the DBs?thanks |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
Posted - 2008-04-09 : 16:41:51
|
| In SQL Server 2005 you can check the fragmentation level using the dynamic management view sys.dm_db_index_physical_stats.Microsoft has some recommendations about fragmentation levels and when to do a reorganize and when to do a rebuild.http://technet.microsoft.com/en-us/library/ms189858.aspxI have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.htmlOla Hallengrenhttp://ola.hallengren.com |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-09 : 16:43:36
|
| USE databasenameGOEXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"GO |
|
|
|
|
|
|
Defragmente database