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

How to defrag tables by filegroup Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:19 AM
Points: 7, Visits: 49
I want to be able to run the defrag indexes on tables by filegroup one at a time.

Example: Defrag indexes which are on XXX_2012 filegroup only.

Scenario:
Database size: 12 TB
Archive historical tables : Partitioned by yearly (Processdate)
Filegroup: XXX_2001, XXX_2002.....XXX_2012

Post #1472230
Posted Wednesday, July 10, 2013 10:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 12,910, Visits: 32,013
well, i've got this saved in my snippets to find tables by filegroup:
SELECT 
objz.[name] As TableName,
objz.[type],
idxz.[name] As IndexName,
idxz.[index_id],
CASE idxz.[index_id]
WHEN 0 THEN 'HEAP'
WHEN 1 THEN 'CLUSTERED'
ELSE 'NON-CLUSTERED'
END AS index_type,
filz.[name] As FileGroupName
FROM sys.indexes idxz
INNER JOIN sys.filegroups filz
ON idxz.data_space_id = filz.data_space_id
INNER JOIN sys.all_objects objz
ON idxz.[object_id] = objz.[object_id]
WHERE idxz.data_space_id = filz.data_space_id
AND objz.type_desc IN( 'USER_TABLE') -- User Created Tables
ORDER BY
CASE
WHEN filz.[name] = 'PRIMARY'
THEN 2
ELSE 1
END,
FileGroupName

beyond that, i'd modify a cursor that looks link of like the one in books online, so that the did it in a specific order, or was limited to a specific filegroup?
http://msdn.microsoft.com/en-us/library/ms177571.aspx


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472235
Posted Wednesday, July 10, 2013 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:19 AM
Points: 7, Visits: 49
Thanks for the script to check the filegroup on partition. But what I wanted to know is can we just defrag all tables which are only on filegroup XXX_2012.

Yes, we can do the defrag manually using
DBCC Indexdefrag (Database,"tablename", index) but I want a script which runs defrag on XXX_2012 filegroup only for all indexes.
Post #1472264
Posted Wednesday, July 10, 2013 11:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:28 AM
Points: 12,910, Visits: 32,013
i thought it was pretty straight forward from there;

you would simply modify my example with a WHERE statement...
WHERE filz.[name]='XXX_2012'

if you need a fulls cript, i'm pretty sure if you search teh script submissions here on SSC, you can find one that does it,a nd modify it with my example so that it limits it to a specific file group;


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472279
Posted Sunday, July 14, 2013 3:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:04 PM
Points: 801, Visits: 715
Here is a query that generates the ALTER INDEX commands for you. Making this into a cursor is left as an exercise to the reader.

Note: I don't have multi-filegroup database to test on, so you need to test carefully! I've tried to take in regard that that the tables may be partitioned
SELECT 'ALTER INDEX ' + quotename(i.name) + ' ON ' + 
quotename(s.name) + '.' + quotename(o.name) +
' REORGANIZE PARTITION = ' + ltrim(str(p.partition_number))
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.allocation_units au ON p.hobt_id = au.container_id
JOIN sys.data_spaces ds ON au.data_space_id = ds.data_space_id
WHERE ds.name = 'PRIMARY'
AND o.type = 'U'
AND i.index_id > 0




Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1473394
Posted Friday, July 26, 2013 11:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:19 AM
Points: 7, Visits: 49
Thanks, it works on Partition Filegroups !!!:)
Post #1478128
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse