Fragmentation size

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Fragmentation size

14 posts, Page 2 of 2

««1 2

Fragmentation size

Rate Topic

Display Mode

Topic Options

Author

Message

GilaMonster

Posted Sunday, December 30, 2012 12:28 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 37,725, Visits: 29,984

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 2008, MVP
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

Post #1401265

Jeff Moden

Posted Sunday, December 30, 2012 11:26 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1401292

HildaJ

Posted Monday, December 31, 2012 9:56 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 89, Visits: 262

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)

Post #1401426

HildaJ

Posted Monday, December 31, 2012 9:59 AM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 10:36 AM
Points: 89, Visits: 262

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

Post #1401427

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions