Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Fragmentation size Expand / Collapse
Author
Message
Posted Sunday, December 30, 2012 12:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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
Posted Sunday, December 30, 2012 11:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401292
Posted Monday, December 31, 2012 9:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 1:44 PM
Points: 164, Visits: 484
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
Posted Monday, December 31, 2012 9:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 1:44 PM
Points: 164, Visits: 484
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 »

Add to briefcase ««12

Permissions Expand / Collapse