By: Aaron Bertrand | Comments (4) | Related: > Indexing
Problem
As databases grow, it often becomes necessary to add new I/O paths to accommodate the growing space. Even without growth that requires this scale, it can still be useful to utilize multiple I/O devices to spread out the load. One way that you can make optimal use of new drives is to add a filegroup to the database and move certain objects (say, all of your indexed views) to the new filegroup. (In fact, you could make a case that you should move all user objects from the PRIMARY
filegroup, but I will save that for another post.)
Solution
Moving a single indexed view to a new filegroup is quite simple. Well, really, you move the clustered index, which - for all intents and purposes - is the data that the view materializes. So we just need to take the definition of the view, and add the WITH DROP_EXISTING
and ON [FileGroup_Name]
options.
Quick example:
CREATE UNIQUE CLUSTERED INDEX UQ_ViewName_ColumnName ON dbo.View_Name(Column_Name); GO
Becomes:
CREATE UNIQUE CLUSTERED INDEX UQ_ViewName_ColumnName ON dbo.View_Name(Column_Name) WITH (DROP_EXISTING = ON) ON [FileGroup_Name]; GO
You can also optionally add ONLINE = ON
if you are running Developer or Enterprise Edition. But what if you have a large number of views? What if your indexed views also have multiple non-clustered indexes? What if you want to schedule this work for a maintenance window?
One way to do this would be very tedious:
- In Management Studio's Object Explorer, open up your database's Views node
- For each view, expand Indexes and check if the view has an index
- Right-click each index, choose Script View As > CREATE To > some location (as long as
Tools > Options > SQL Server Object Explorer > Script Indexes
is set to true, not the default - otherwise, repeat for each index) - Remove the
CREATE VIEW
and other code not relating specifically to the index(es) - Add the
DROP_EXISTING
andON [FileGroup_Name]
options - Execute the script or save it somewhere for later execution
- Goto 1
Another way would be similarly tedious. With Views highlighted in Object Explorer, open Object Explorer Details (F7). Unlike Object Explorer, Object Explorer Details allows you to select multiple objects, so that you can perform the same operation against many items at the same time. From here you can select all of the views (or a subset, if you know which views have indexes or not - unfortunately this view does not offer "Has Index" or "Number of Indexes" as an exposed property). Then you can right-click and perform steps 3-6 above just once, instead of once per object.
A Better Way
Management Studio doesn't invent all of this data that it outputs through the scripting options; it gets the information from the catalog views. Well, so can we, and we can generate the precise scripts we'll need to recreate these indexes dynamically . Let's say we have the following objects, and we've added a new filegroup:
USE master; GO CREATE DATABASE ivtest; GO USE ivtest; GO CREATE TABLE dbo.Sample1(a INT); GO CREATE VIEW dbo.Sample1_View WITH SCHEMABINDING AS SELECT a, c = COUNT_BIG(*) FROM dbo.Sample1 GROUP BY a; GO CREATE UNIQUE CLUSTERED INDEX PK_Sample1 ON dbo.Sample1_View(a); GO CREATE TABLE dbo.Sample2(a INT, b INT, c INT); GO CREATE VIEW dbo.Sample2_View WITH SCHEMABINDING AS SELECT a, b, c, d = COUNT_BIG(*) FROM dbo.Sample2 GROUP BY a, b, c; GO CREATE UNIQUE CLUSTERED INDEX PK_Sample2 ON dbo.Sample2_View(a); GO CREATE INDEX IX_Sample2_d__a ON dbo.Sample2_View(d,a) CREATE INDEX IX_Sample2_b_a__c ON dbo.Sample2_View(b,a) INCLUDE(c); CREATE INDEX IX_Sample2_d__c_a ON dbo.Sample2_View(d) INCLUDE(c,a); GO ALTER DATABASE ivtest ADD FILEGROUP Indexed_Views; GO ALTER DATABASE ivtest ADD FILE (name=N'Indexed_Views', filename=N'C:\temp\ivtest_iv.ndf') TO FILEGROUP [Indexed_Views]; GO
To find all of the views with at least one index, and each index that hasn't already been moved to the Indexed_Views
filegroup:
SELECT [view] = v.name, [index] = i.name FROM sys.views AS v INNER JOIN sys.indexes AS i ON v.[object_id] = i.[object_id] INNER JOIN sys.filegroups AS f ON f.data_space_id = i.data_space_id WHERE f.name <> N'Indexed_Views' ORDER BY v.name, i.index_id;
This returns:
view index ------------ ----------------- Sample1_View PK_Sample1 Sample2_View PK_Sample2 Sample2_View IX_Sample2_d__a Sample2_View IX_Sample2_b_a__c Sample2_View IX_Sample2_d__c_a
We also need to track down the columns in each index and, for non-clustered indexes, whether the column is a key or include column. The order of the key columns is also important. So we can add in a few columns to get the additional information:
SELECT [view] = v.name, [index] = i.name, [column] = c.name, [include] = ic.is_included_column FROM sys.views AS v INNER JOIN sys.indexes AS i ON v.[object_id] = i.[object_id] INNER JOIN sys.columns AS c ON c.[object_id] = v.[object_id] INNER JOIN sys.index_columns AS ic ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id AND ic.index_id = i.index_id INNER JOIN sys.filegroups AS f ON f.data_space_id = i.data_space_id WHERE f.name <> N'Indexed_Views' ORDER BY v.name, i.index_id, ic.index_column_id;
Results:
view index column include ------------ ----------------- ------ ------- Sample1_View PK_Sample1 a 0 Sample2_View PK_Sample2 a 0 Sample2_View IX_Sample2_d__a d 0 Sample2_View IX_Sample2_d__a a 0 Sample2_View IX_Sample2_b_a__c b 0 Sample2_View IX_Sample2_b_a__c a 0 Sample2_View IX_Sample2_b_a__c c 1 Sample2_View IX_Sample2_d__c_a d 0 Sample2_View IX_Sample2_d__c_a c 1 Sample2_View IX_Sample2_d__c_a a 1
This seems to be all of the information we need to re-create these indexes, assuming that the rest of the default options were in use at the time (and the defaults haven't been changed). You may want to add columns to this output in order to use in the dynamic output so that those settings are maintained. Now, in order to generate a script that you don't have to run manually, I use a dynamic SQL technique that I employ often. It just uses a variable to append each line of code I want to execute, drawn on the results of the join above. Since an index can have multiple key or include columns, I use a concatenation trick with FOR XML PATH
in a subquery in order to build the comma-separated list of columns.
The code looks like this:
SET NOCOUNT ON; DECLARE @opt NVARCHAR(13), @sql NVARCHAR(MAX), @fg NVARCHAR(128); SELECT @fg = N'Indexed_Views', -- you'll want to change this to your FG name @sql = N'', -- important to initialize this! @opt = CASE WHEN CONVERT(NCHAR(3), SERVERPROPERTY('Edition')) IN (N'Ent', N'Dev') THEN N', ONLINE = ON' ELSE N'' END; SELECT @sql = @sql + N' CREATE ' + CASE WHEN i.index_id = 1 THEN 'UNIQUE CLUSTERED' ELSE '' END + ' INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(SCHEMA_NAME(v.[schema_id])) + '.' + QUOTENAME(v.name) /* comma-separated list of key columns, ordered */ + '(' + STUFF((SELECT ',' + QUOTENAME(c.name) FROM sys.columns AS c INNER JOIN sys.index_columns AS ic ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id WHERE c.[object_id] = v.[object_id] AND ic.index_id = i.index_id AND ic.is_included_column = 0 ORDER BY ic.index_column_id FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'),1,1,'') + ') ' /* comma-separated list of include columns, if necessary (order is not important) */ + COALESCE(' INCLUDE (' + STUFF((SELECT ',' + QUOTENAME(c.name) FROM sys.columns AS c INNER JOIN sys.index_columns AS ic ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id WHERE c.[object_id] = v.[object_id] AND ic.index_id = i.index_id AND ic.is_included_column = 1 FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'),1,1,'') + ')', '') + ' WITH (DROP_EXISTING = ON' + @opt + ') ON ' + QUOTENAME(@fg) + ';' FROM sys.views AS v INNER JOIN sys.indexes AS i ON v.[object_id] = i.[object_id] INNER JOIN sys.filegroups AS f ON f.data_space_id = i.data_space_id WHERE f.name <> 'Indexed_Views' ORDER BY v.name, i.index_id; SELECT @sql; -- EXEC sp_executesql @sql;
Now, you can inspect the output using Results to Text in Management Studio, however keep in mind that PRINT
is limited to 8K, so if this ends up being a large script, the output will be truncated (so it may not look correct or complete). Unless you need to go to great lengths to validate statements beyond the first 8K, you'll just have to trust that it's all there.
My output is not all that pretty, but it does what I asked:
CREATE UNIQUE CLUSTERED INDEX [PK_Sample1] ON [dbo].[Sample1_View]([a]) WITH (DROP_EXISTING = ON, ONLINE = ON) ON [Indexed_Views]; CREATE UNIQUE CLUSTERED INDEX [PK_Sample2] ON [dbo].[Sample2_View]([a]) WITH (DROP_EXISTING = ON, ONLINE = ON) ON [Indexed_Views]; CREATE INDEX [IX_Sample2_d__a] ON [dbo].[Sample2_View]([d],[a]) WITH (DROP_EXISTING = ON, ONLINE = ON) ON [Indexed_Views]; CREATE INDEX [IX_Sample2_b_a__c] ON [dbo].[Sample2_View]([b],[a]) INCLUDE ([c]) WITH (DROP_EXISTING = ON, ONLINE = ON) ON [Indexed_Views]; CREATE INDEX [IX_Sample2_d__c_a] ON [dbo].[Sample2_View]([d]) INCLUDE ([c],[a]) WITH (DROP_EXISTING = ON, ONLINE = ON) ON [Indexed_Views];
You'll notice that it created the clustered index for each view first, then the non-clustered indexes in order, and everything is escaped with square brackets to protect you from most poorly-chosen identifiers. You can make certain changes here, for example you can keep certain indexed views where they are (for now, or permanently). You could also adjust the query so that the clustered indexes go on one filegroup and the non-clustered indexes go on another (which is a technique I see used from time to time). So next, you just have to uncomment the final line (`EXEC sp_executesql @sql;`), run it again, and it will move all of the indexed views. (But be sure to test this in a non-production environment first, and the final deployment should be during relative downtime or during a maintenance window - especially if you are not able to use ONLINE = ON
.)
If you want to move them back, you just have to change a couple of lines in the last section:
WITH (DROP_EXISTING = ON' + @opt + ') ON [PRIMARY] + ';' -- was ON ' + QUOTENAME(@fg) + ';' FROM sys.views AS v INNER JOIN sys.indexes AS i ON v.[object_id] = i.[object_id] INNER JOIN sys.filegroups AS f ON f.data_space_id = i.data_space_id WHERE f.name = 'Indexed_Views' -- was <> 'Indexed Views' ORDER BY v.name, i.index_id;
Conclusion
And there you have it, a single dynamic script that will move all of the indexed views to a specific filegroup. And you can adapt this technique to move indexes on base tables (however you will have to add conditional handling for filtered indexes, which aren't valid on indexed views, and perhaps other differences).
Next Steps
- Review the following tips and other resources:
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips