May 25, 2016 at 1:28 pm
Hi all
We're in the process of moving our SQL MDF\NDFs to new disk and we want to reorganize the data into fewer (but larger) NDFs. So far we figured out that if I put the new disk into a secondary filegroup, rebuild the clustered index and point it to the secondary filegroup, that will move the data from the old disk into the new disk in the secondary filegroup. The drawback to this is that I have to do this table by table, and I have about 19,000
My question is this:
Does anyone know of a way to run Ola's script so we can rebuild the indexes on the second filegroup?
Thank you in advance
The Gug
May 25, 2016 at 1:43 pm
Ola's stuff is open source. Just go to the relevant part and grab the code and modify to suit. Or script your own using system tables. Very doable. I don't believe Ola's stuff has this feature, but RTFM on his website and you can find out in a matter of a few minutes.
You might want to check out SQL Fools indexing stuff, and Minionware's too to see if either does what you need.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 25, 2016 at 1:43 pm
here's something i recently built to move specific clustered indexes to a new filegroup, and also enable page compression on some specific tables;
you'll see some commented artifacts referencing a table variable @ItemsToMigrate.
in my case, for a handful of specific tables, i created filegroups with a naming convention of db_name() + '_' + [object_name], ie "Production_CommandLog"
you can simply modify that one line to be a hardcoded name of your other group.
sample results
GO
CREATE UNIQUE CLUSTERED INDEX [PK_CommandLog]
ON [dbo].[CommandLog] (ID asc)
WITH (DROP_EXISTING = ON , DATA_COMPRESSION = PAGE)
ON Production_CommandLog;
the code:
DECLARE @Results TABLE (
[schema_id] INT,
[schema_name] VARCHAR(255),
[object_id] INT,
[object_name] VARCHAR(255),
[index_id] INT,
[index_name] VARCHAR(255),
[Rows] INT,
[SizeMB] DECIMAL(19,3),
[IndexDepth] INT,
[type] INT,
[type_desc] VARCHAR(30),
[fill_factor] INT,
[is_unique] INT,
[is_primary_key] INT ,
[is_unique_constraint] INT,
[index_columns_key] VARCHAR(max),
[index_columns_include] VARCHAR(max),
[index_where_statement] VARCHAR(max),
[currentFilegroupName] varchar(128),
[CurrentCompression] varchar(128) )
INSERT INTO @Results
SELECT
SCH.schema_id, SCH.[name] AS schema_name,
OBJS.[object_id], OBJS.[name] AS object_name,
IDX.index_id, ISNULL(IDX.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type, IDX.type_desc, IDX.fill_factor,
IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include,
ISNULL(' WHERE (' + IDX.filter_definition + ')','') AS index_where_statement,
filz.name,
ISNULL(p.data_compression_desc,'')
FROM sys.objects OBJS
INNER JOIN sys.schemas SCH ON OBJS.schema_id=SCH.schema_id
-- INNER JOIN @ItemsToMigrate itm ON SCH.[name] = itm.SchemaName AND OBJS.[name] = itm.TableName
INNER JOIN sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]
INNER JOIN sys.filegroups filz ON idx.data_space_id = filz.data_space_id
INNER JOIN sys.partitions p ON IDX.object_id = p.object_id AND IDX.index_id = p.index_id
INNER JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats STATS
GROUP BY [object_id], index_id
) AS partitions
ON IDX.[object_id]=partitions.[object_id]
AND IDX.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include,
LEFT(index_columns_where, LEN(index_columns_where)-1) AS index_columns_where
FROM
(
SELECT
(
SELECT COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 0
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT COLS.[name] + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include,
(SELECT COLS.[name] + ',' + ' '
FROM sys.index_columns IXCOLS
INNER JOIN sys.columns COLS
ON IXCOLS.column_id = COLS.column_id
AND IXCOLS.[object_id] = COLS.[object_id]
WHERE IXCOLS.is_included_column = 1
AND IDX.[object_id] = IXCOLS.[object_id]
AND IDX.index_id = IXCOLS.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_where
) AS Index_Columns
) AS Index_Columns
WHERE filz.name = 'PRIMARY'
ORDER BY
SCH.[name],
OBJS.[name],
IDX.[name];
DECLARE @ForceCompression int = 1;
SELECT [schema_name],[object_name], 'GO
CREATE '+ CASE WHEN [is_unique]=1 THEN 'UNIQUE ' ELSE '' END + ' CLUSTERED INDEX ' + quotename(index_name) + '
ON ' + quotename([schema_name]) + '.' + quotename([object_name]) + ' (' + index_columns_key + ') ' + index_where_statement + '
WITH (DROP_EXISTING = ON ' + CASE
WHEN @ForceCompression = 1
THEN ', DATA_COMPRESSION = PAGE'
WHEN [CurrentCompression] <> 'NONE'
THEN ''
ELSE ', DATA_COMPRESSION = ' + [CurrentCompression]
END + ')
ON ' + db_name() + '_' + [object_name] + ';'
FROM
@Results WHERE 1=1
--AND [object_name] IN(SELECT TableName FROM @ItemsToMigrate)
AND index_id = 1
GO
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply