Defragmentation - Reindex - Shrink

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Defragmentation - Reindex - Shrink

Author

Topic

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-31 : 00:23:55

Hi All,

There is an evidence of Fragementation on Databases for which I have Defragmented later reindexed them. After that I have shrunk the Data files.

But still there is no evidence of Defragmentation, however, unlike during the first instance there is no slowness in data accessibility and storage.

Can Anyone tell me what I should do here.

Many Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-08-31 : 04:01:15

By shrinking the data file, you're re-fragmenting the indexes, probably way worse than they were before the shrink. Why are you shrinking the database? Databases tend to grow as more data gets put in them. It's in their nature.

See - [url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]

--
Gail Shaw
SQL Server MVP

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-08-31 : 19:54:00

Thanks Gail, This time I have Defragmented and later Reindexed but NOT Shrunk. But still the same result. No Change The fragementation still holds the same way.

Any suggestions

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-01 : 04:33:26

How big are those indexes? How many pages?

--
Gail Shaw
SQL Server MVP

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-09-01 : 21:29:40

This is the sample but I haven't got it changed, even after Defragmentation and there after reindexing them.

Any further solution please.

TNameTIndexNameavg_frag_% rows
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590
TRANS4 SOE_TRANS4_IDX3 100 2005590

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-02 : 04:12:14

How many pages in the index? Not interested in rows or % fragmentation. Need the number of pages and the type of index (clustered/nonclustered). Both can be got from sys.dm_db_index_physical_stats.

Also, I have never seen 100% fragmentation and in fact it's not possible considering how SQL calculates fragmentation. How are you getting that value?

--
Gail Shaw
SQL Server MVP

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-09-02 : 20:15:05

Thanks Gila for following up my concern so far.

object_id index_id avg_fragmentation % page_count

25103180 1 85.71428571 7
121819546 0 50 3
152387612 0 62.12590299 5810
164247690 3 50 2
246291937 2 87.5 8
246291937 3 77.77777778 9
246291937 4 85.71428571 7
417436561 1 80 5
417436561 2 50 2
555865047 3 30.43478261 23
583725182 1 50 2
598293191 1 35 20
598293191 2 77.77777778 9
612197231 2 87.5 8
612197231 3 83.33333333 6
612249286 1 66.66666667 3
791009899 1 87.5 8
791009899 2 50 2
865438157 1 83.33333333 6
865438157 2 50 2
1125579048 2 66.66666667 3
1402488075 1 50 2
1490104349 1 50 2
1511728488 1 77.77777778 9
1511728488 2 75 4
1575728716 1 50 2
1589580701 2 63.63636364 11
1589580701 3 43.75 16
1639728944 1 87.5 8
1678681078 1 38.88888889 18
1678681078 2 50 2
1851153640 2 80 5
1851153640 3 80 5
1851153640 4 87.5 8
1851153640 5 70 10
1851153640 6 83.33333333 6
1851153640 7 87.5 8
1863013718 1 50 14
1863013718 2 50 2
1941581955 2 63.63636364 11
1962490070 1 50 2
1965250056 2 50 2
1965250056 3 50 2
1965250056 4 50 2
1965250056 5 50 2
1965250056 6 66.66666667 3
1965250056 7 50 2
1981302168 1 38.88888889 18
2023730312 1 50 2
2044586372 1 66.66666667 3
2073058421 1 50 2

Here in this Page Count is real concerned but in previous post it was 100 Percent but number of pages were around 10 - 20.

Can you suggest me is this phenomenon like Percent is inversely proportional to Page Count behaviour (Means More Percent then less pages and Less Percent then More page Count)is good or bad?

Thanks for your time.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-03 : 06:33:02

Very small indexes don't defragment well and there's no real point in trying. Fragmentation really becomes an issue around 1000 pages. It's not really worth bothering with tiny indexes.

The one that you highlighted above is not an index. It's a heap (indicated by index id 0). A heap cannot be rebuilt in any version below SQL 2008. Also, the % is not the same thing as for indexes.

The % fragmentation is logical fragmentation for an index, extent fragmentation for a heap

--
Gail Shaw
SQL Server MVP

dbalearner
Constraint Violating Yak Guru

272 Posts

Posted - 2009-09-03 : 20:02:55

Yep I Knew what you have said. However, I would like to provide you the COMPLETE Output which includes Heap along with Indexes.

However, thanks for your patient replies which now I am confirmed that the small fragmentation need not be bothered.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources