Post reply

REBUILD THE INDEXES BY FILE GROUP

  • March 20, 2009 at 8:25 am

    #202541

    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!

  • March 20, 2009 at 8:35 am

    #963563

    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... :-D.

    Greets

    Flo

  • March 20, 2009 at 8:40 am

    #963568

    Thanks for the script, but it did not point to the current month. How do you only run on current month?

  • March 20, 2009 at 8:48 am

    #963575

    How to identify the current month? By index creation date? Is it part of the file group name?

  • March 20, 2009 at 8:55 am

    #963579

    Part of the filegroup name.

  • March 20, 2009 at 9:03 am

    #963586

    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

  • March 20, 2009 at 9:05 am

    #963591

    Thanks a bunch! I will try this and let you know how it works-

  • March 20, 2009 at 9:10 am

    #963593

    Glad to help you! 🙂

  • March 20, 2009 at 9:21 am

    #963604

    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

  • March 20, 2009 at 10:02 am

    #963635

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 20, 2009 at 10:14 am

    #963645

    I like Florians script...added it to my snippets.

    my only changes are I added the filegroup name and removed the WHERE statement...i want a script that generates all the DBCC and Statistics commands by filegroup....since it's going to output 2000 rows or so in my example database, by sinply ordering the list, i can save the scripts for later:

    SELECT

    sd.name AS FileGroupName,

    st.name AS TableName,

    si.name As IndexName,

    sd.name data_space_name,

    'DBCC DBREINDEX("' + SCHEMA_NAME(st.schema_id) + '.' + st.name + '", ' + QUOTENAME(si.name) + ')' AS ReIndexCommand,

    'UPDATE STATISTICS ' + SCHEMA_NAME(st.schema_id) + '.' + st.name As StatisticsCommand

    FROM sys.indexes si

    INNER JOIN sys.tables st ON si.object_id = st.object_id

    INNER JOIN sys.data_spaces sd ON si.data_space_id = sd.data_space_id

    order by sd.name,st.name

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 20, 2009 at 11:34 am

    #963710

    Thank you both!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply