Fragmentation size

Question

Fragmentation size

zi

SSCrazy

Points: 2387
More actions
December 26, 2012 at 12:42 pm

#272061

Dear Friends
1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.
2-what is the relationship between re-build or re-organise index by log file.
Thanks lot

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

zi (12/26/2012)
Dear Friends
1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.
2-what is the relationship between re-build or re-organise index by log file.
Thanks lot

1) Fragmentation for the database as whole doesn't make sense in this context. You need to look at each index individually.

2) I don't understand your question here. What do you mean?

You should probably do a google search for "sql server rebuild vs reorganize index" and be prepared to spend some time reading.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

zi (12/26/2012)
1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.

Databases don't have fragmentation. Indexes do. Talking about the fragmentation level of an entire DB is completely meaningless.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zi SSCrazy Points: 2387 More actions · Answer 3

Thanks for replying

- what if I have a huge database = 'Tables' , how to know that I need to re-organise or to re-build indexes

-why when re-organise or re-build indexes make log file bigger.

Thanks lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

zi (12/26/2012)
- what if I have a huge database = 'Tables' , how to know that I need to re-organise or to re-build indexes

Then you look at the fragmentation of the indexes and decide which to rebuild. I recommend you use something like Ola's scripts

http://ola.hallengren.com/Versions.html

-why when re-organise or re-build indexes make log file bigger.

Because all modifications to the DB are logged and both index rebuilds and reorganise operations do a substantial amount of modifications

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

zi SSCrazy Points: 2387 More actions · Answer 5

zi

SSCrazy

Points: 2387

December 26, 2012 at 1:42 pm

#1571666

Thank you very much

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

zi (12/26/2012)
Dear Friends
1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.
2-what is the relationship between re-build or re-organise index by log file.
Thanks lot

These are actually interview questions.... important interview questions that every DBA should know by heart. The fact that you're posting these questions means you really need to spend some time practicing being a DBA before you answer another ad for the position. And, no... I'm not trying to be mean here. Call it "tough love" for someone looking for a job as a DBA. Buy yourself a copy of the SQL Server Develop Edition, install it on your laptop or home computer, and hit the books.

You might also want to learn how to Google for such answers instead of relying on the kindness of others who might not actually know the answers to your questions. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peterdru401 Mr or Mrs. 500 Points: 535 More actions · Answer 7

Here is an excellent difference between rebuilding the indexes & reorganising the indexes

Eg.

Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks;

GO

ALTER INDEX ALL ON Production.Product REBUILD

GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE AdventureWorks;

GO

ALTER INDEX ALL ON Production.Product REORGANIZE

GO

Note: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

zi SSCrazy Points: 2387 More actions · Answer 8

Thanks for replying

Do you have a script to know the fragmentation size for the whole database not only table by table.

Thanks lot

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

zi (12/30/2012)
Thanks for replying
Do you have a script to know the fragmentation size for the whole database not only table by table.
Thanks lot

To repeat:

GilaMonster (12/26/2012)
zi (12/26/2012)
1-1-How to know the fragmentation size for whole database not only for certain table, to decide if it exexceeds 30% to re-build index or re-organise.
Databases don't have fragmentation. Indexes do. Talking about the fragmentation level of an entire DB is completely meaningless.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

zi (12/30/2012)
Do you have a script to know the fragmentation size for the whole database not only table by table.

That's a completely nonsensical request as the concept of 'fragmentation size for the whole database' is meaningless. As I mentioned earlier, databases don't have fragmentation. Indexes do.

Then you look at the fragmentation of the indexes and decide which to rebuild. I recommend you use something like Ola's scripts
http://ola.hallengren.com/Versions.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 11

zi (12/30/2012)
Thanks for replying
Do you have a script to know the fragmentation size for the whole database not only table by table.
Thanks lot

You don't need one. Just check the file fragmentation report of your favorite disk defragmentation tool for the file name(s) of the given database.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 12

Try the script below to find out the fragmentation information. Hope it's a good start. Modified the values to fit the specs in your environment.

/*

Display statistics for index fragmentation for a selected database

the 'DETAILED' option gives a more accurate results and

slows some the results use NULL instead

See http://msdn.microsoft.com/en-us/library/ms188917.aspx for further information

*/

SELECTOBJECT_NAME(OBJECT_ID) TableName

, ssi.name

, index_type_desc

, avg_fragmentation_in_percent

--, fragment_count

--, avg_fragment_size_in_pages

, page_count

FROM sys.dm_db_index_physical_stats(db_id('ULDB'),NULL,NULL,NULL, NULL)

INNER JOIN sys.sysindexes SSI

ON OBJECT_ID = SSI.id

AND index_id = SSI.indid

WHERE avg_fragmentation_in_percent > 15

AND index_type_desc <> 'HEAP'

AND page_count > 30

ORDER BY avg_fragmentation_in_percent DESC, OBJECT_NAME(OBJECT_ID)

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 13

This is the script I use to rebuild/reorganized my indexes. Again modify when needed.

/*

Script copied from http://msdn.microsoft.com/en-us/library/ms188917.aspx

and modified to fit our environment

CHECK FOLLOWING TO MAKE SURE THE SCRIPT RUNS WITH THE RIGHT CONFIGURATIONS

1) MAKE SURE YOU SPECIFY THE DATABASE TO REBUIL/REORGANIZED INDEXES FOR

2) CHECK WHAT INDEXES ARE NEEDED TO BE REBUILD/REORGANIZE (HEAP, CLUSTERED, NONCLUSTERED)

3) avg_fragmentation_in_percent VALUE

4) page_count VALUE

5) THE FRAGMENTATION RANGE SETTING WHICH DETERMINES EITHER TO REORGANIZED OR REBUILD THE INDEX

6) CHECK THE MESSAGES WINDOW TO SEE THE ALTERED INDEX LIST

*/

USE ULDB

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

INNER JOIN sys.sysindexes SSI

ON OBJECT_ID = SSI.id

AND index_id = SSI.indid

WHERE avg_fragmentation_in_percent > 15.0

AND index_id > 0

-- COMMENT THIS LINE IF YOU NEED TO REBUILD/REORGANIZED ALL INDEXES INCLUDING HEAP INDEXES

AND index_type_desc <> 'HEAP'

AND page_count > 30

ORDER BY avg_fragmentation_in_percent DESC, OBJECT_NAME(OBJECT_ID)

-- display temporary table before the changes

SELECT * FROM #work_to_do

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 15 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 15.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 15.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- display temporary table after the changes

SELECT * FROM #work_to_do

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO