More about fragmentation - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

More about fragmentation

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 10:06:07

[code]CREATE TABLE tSample
(
i INT
)

CREATE UNIQUE CLUSTERED INDEX IX_Sample ON tSample (i)

DECLARE @i INT

SET @i = 0

WHILE @i < 10000
BEGIN
INSERT tSample
SELECT @i

SET @i = @i + 1
END

DBCC SHOWCONTIG(tSample) WITH FAST

ALTER INDEX IX_Sample ON tSample REBUILD

DBCC SHOWCONTIG(tSample) WITH FAST

DROP TABLE tSample[/code]Output from above code is[code]DBCC SHOWCONTIG scanning 'tSample' table...
Table: 'tSample' (1596584776); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 17
- Extent Switches..............................: 4
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation ..................: 23.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'tSample' table...
Table: 'tSample' (1596584776); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 17
- Extent Switches..............................: 7
- Scan Density [Best Count:Actual Count].......: 37.50% [3:8]
- Logical Scan Fragmentation ..................: 35.29%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.[/code]
How come that fragmentation exists when inserting one record after another in sequence according to clustered index?

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 10:18:13

You should try with Bigger sample.Rebuild or Reorganize is not needed or ignores small tables and indexes(Atleast 100 pages scanned).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 10:28:43

Aha!
But still... How come the table is worked on and left with less efficient Scan Density?

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 10:32:52

Keeping and maintaining indexes for smaller table is headache. Table scan performs better than with indexes.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 10:34:58

No wonder if REBUILD gives worse result than before

E 12°55'05.63"
N 56°04'39.26"

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 10:35:00

Here is extract from msdn:

For small tables, usually performance impact on fragmentation is undectable. The first 8 page allocation would be from mixed extents and mixed extents could be anywhere in database files. Rebuilding indexes would not change this nature.

If you have a small table, those mixed pages weight a lot during fragmentation calculation; therefore, rebuilding index may not reduce fragmentation. (As matter of fact, I could easily construct a case that fragmentation increases after rebuild.) Those fragmentation would not be a pain for your query performance; so basically you can ingore them.

When page counts of an index reaches to certain big size (for example, 1000 pages), then fragmentation may start to impact performance. Rebuilding index should reduce fragmentation.

Another thing to consider is that how high the fragmentation is. If it is < 10%, it is hard for rebuilding to reduce more (we never could 100% guarantee that you can reach 0% fragmentation; with mixed page allocation, it is a hard goal to achieve).

If you don't care about space utilization, you can use undocumented trace flag 1118 to disable mixed page allocation, but I am not recommend it (this trace flag usually is used in tempdb for reducing SGAM contention).

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 10:36:04

quote:
Originally posted by sodeep

Here is extract from msdn:

For small tables, usually performance impact on fragmentation is undectable. The first 8 page allocation would be from mixed extents and mixed extents could be anywhere in database files. Rebuilding indexes would not change this nature.

If you have a small table, those mixed pages weight a lot during fragmentation calculation; therefore, rebuilding index may not reduce fragmentation. (As matter of fact, I could easily construct a case that fragmentation increases after rebuild.) Those fragmentation would not be a pain for your query performance; so basically you can ingore them.

When page counts of an index reaches to certain big size (for example, 1000 pages), then fragmentation may start to impact performance. Rebuilding index should reduce fragmentation.

Another thing to consider is that how high the fragmentation is. If it is < 10%, it is hard for rebuilding to reduce more (we never could 100% guarantee that you can reach 0% fragmentation; with mixed page allocation, it is a hard goal to achieve).

If you don't care about space utilization, you can use undocumented trace flag 1118 to disable mixed page allocation, but I am not recommend it (this trace flag usually is used in tempdb for reducing SGAM contention).

But I would start with 100 pages or more.

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-01-28 : 11:01:42

Sodeep- I had seen this "http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=244214" reply from MS but I don't buy this answer. According to this if I have an object with 16 pages (2 extents) I should not see fragmentation on small objects, that is not the case. I completely uderstand that small objects don't need re-indexing/reorganizing. Its not about performance but why this happens? What do you say?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 11:09:13

Here is what Booksonline says:

Rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it. For more information about mixed extents See Pages and Extents

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 11:12:33

Also it depends on whether you have clustered index or not. If you are looking for more deep explanation,you gotta dig in here:

http://msdn.microsoft.com/en-us/library/ms190969.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 11:17:39

Now what you say Saurabh?

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-01-30 : 15:08:50

ahhaa!!!! Let me test this scenario and come back with stellar results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources