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»»

REBUILD THE INDEXES BY FILE GROUP Expand / Collapse
Author
Message
Posted Friday, March 20, 2009 8:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
gURU'S, Can someone please tell me how to defrag or rebuild the indexes by filegroup. I have about 37 filegroups by month and I want to rebuild only the current month indexes. Please HELP!
Post #680417
Posted Friday, March 20, 2009 8:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi

Do you mean something like this?

SELECT st.name table_name,
si.name index_name,
sd.name data_space_name,
'DBCC DBREINDEX("' + SCHEMA_NAME(st.schema_id) + '.' + st.name + '", ' + QUOTENAME(si.name) + ')'
FROM sys.indexes si
JOIN sys.tables st ON si.object_id = st.object_id
JOIN sys.data_spaces sd ON si.data_space_id = sd.data_space_id
WHERE sd.name = 'ATTACHMENTS'

37 file groups per month? I always thought file groups are master data... .

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #680433
Posted Friday, March 20, 2009 8:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
Thanks for the script, but it did not point to the current month. How do you only run on current month?
Post #680438
Posted Friday, March 20, 2009 8:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
How to identify the current month? By index creation date? Is it part of the file group name?


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #680446
Posted Friday, March 20, 2009 8:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
Part of the filegroup name.
Post #680452
Posted Friday, March 20, 2009 9:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi

An example for the names would be grate

Well, assumed the file group name would be "FILE_GROUP_YYYY_M" where "YYYY" identifies the year and "M" identifies the month.

DECLARE @date datetime
SET @date = GETDATE()
DECLARE @fg_name NVARCHAR(128)

SET @fg_name = 'FILE_GROUP_' + CONVERT(VARCHAR(4), DATEPART(YEAR, @date)) + '_' + CONVERT(VARCHAR(2), DATEPART(MONTH, @date))


SELECT st.name table_name,
si.name index_name,
sd.name data_space_name,
'DBCC DBREINDEX("' + SCHEMA_NAME(st.schema_id) + '.' + st.name + '", ' + QUOTENAME(si.name) + ')'
FROM sys.indexes si
JOIN sys.tables st ON si.object_id = st.object_id
JOIN sys.data_spaces sd ON si.data_space_id = sd.data_space_id
WHERE sd.name = @fg_name

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #680460
Posted Friday, March 20, 2009 9:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
Thanks a bunch! I will try this and let you know how it works-
Post #680465
Posted Friday, March 20, 2009 9:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Glad to help you!


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #680467
Posted Friday, March 20, 2009 9:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
I forgot to ask about the statistics- How should I update the current month filegroup ' FG_MSG' statistics? Do you have the script for that as well? Thanks
Post #680479
Posted Friday, March 20, 2009 10:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
the same script that Florian gave you, simply change the file group name and change the line that says DBCC REINDEX to this:
'UPDATE STATISTICS '   + SCHEMA_NAME(st.schema_id) + '.' + st.name 



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 #680520
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse