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 |
|
dbist
Yak Posting Veteran
52 Posts |
 Posted - 2008-06-13 : 09:44:28
|
| Hello,I'm new to rebuilding and reorg indexes. I used the standard report feature in 2005 to look up Index Physical Statistics on one db. I found recommendation to rebuild a few indexes that contain a number of fragments. I created a maint. task, I know maint. tasks are not a cure-all but so far I am just testing the waters. I created two tasks, one to rebuild and another to reorg. After I ran the job, I looked at the report and it still showed recommendation to rebuild the indexes. What is a better solution for my case? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-13 : 10:42:20
|
| You don't have to reorg if already you rebuild it. Usually only if the fragmentation is above 40-50% you should rebuild indexes and it should be done in offline hours (Rebuild). |
 |
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-06-13 : 11:10:04
|
| thanks, this is a copy of my prod db so time I do it doesn't matter. Fragmentation shows 75%. There are four fragments on this particular nonclustered index. I ran this:ALTER INDEX CareProvVisitRoleAlt3IdxON dbo.CV3ActiveCareProvVisitRoleREBUILD;GOI also ran this:ALTER INDEX [CareProvVisitRoleAlt3Idx] ON [dbo].[CV3ActiveCareProvVisitRole] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF )GOI refreshed the management studio databases view and looked up the report. It still showed 4 fragments, 75% fragmentation and recommendation to rebuild. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-13 : 11:12:41
|
| What does DBCC showcontig on those indexes show? |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-06-13 : 12:07:34
|
| DBCC SHOWCONTIG scanning 'CV3ActiveCareProvVisitRole' table...Table: 'CV3ActiveCareProvVisitRole' (519060985); index ID: 1, database ID: 10TABLE level scan performed.- Pages Scanned................................: 17- Extents Scanned..............................: 9- Extent Switches..............................: 8- Avg. Pages per Extent........................: 1.9- Scan Density [Best Count:Actual Count].......: 33.33% [3:9]- Logical Scan Fragmentation ..................: 41.18%- Extent Scan Fragmentation ...................: 77.78%- Avg. Bytes Free per Page.....................: 1715.0- Avg. Page Density (full).....................: 78.81%DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-13 : 12:42:46
|
| Your fragmentation shows you need to rebuild.Kick the rebuild job and see how it affects |
|
|
|
Ola Hallengren
Starting Member
33 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Rebuilding Indexes