SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fragmentation size


Fragmentation size

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88429 Visits: 45284
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
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87418 Visits: 41113
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
HildaJ
HildaJ
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 571
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
*/
SELECT OBJECT_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
HildaJ
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 571
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search