July 10, 2013 at 10:01 am
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
July 10, 2013 at 10:15 am
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
July 10, 2013 at 11:35 am
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.
July 10, 2013 at 11:58 am
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
July 14, 2013 at 3:12 pm
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
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 26, 2013 at 11:39 am
Thanks, it works on Partition Filegroups !!!:)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply