By: Svetlana Golovko | Comments | Related: > Policy Based Management
Problem
There are several recommendations for creating multiple filegroups. One of the recommendations is to put nonclustered indexes in a separate filegroup. Indexes by default are created in the same filegroup where the base table is created (table which index is created). To improve query performance you can store nonclustered indexes on separate filegroups that are created on different disks. In this scenario, sometimes indexes are created on the same filegroup where the base table is located. In this tip we will show you how to use Policy Based Management (PBM) as a way to find these indexes that were created in the wrong filegroup.
If you are new to the PBM you should start by reading this tip: "Using Policy Based Management in SQL Server 2008".
Solution
The tables in our examples reside on the PRIMARY filegroup. If you keep your tables on a different filegroup you will need to make adjustments to the provided solution.
Solution using sp_msforeachdb
An old method that can be used on SQL Server 2005 (which does not have Policy Based Management) is to run the following query on every SQL Server instance or to use a Registered Servers query to query many instances as once:
CREATE TABLE #indexesStats ([dbid] int, dbname nvarchar(50), objName nvarchar(100), indname nvarchar(100)) EXEC dbo.sp_msforeachdb 'INSERT INTO #indexesStats SELECT db_id(''?''), ''?'' as dbname, o.name as objName, i.name as indName FROM [?].sys.indexes i JOIN [?].sys.objects o ON i.object_id = o.object_id WHERE i.index_id > 1 -- nonclustered indexes only AND i.data_space_id = 1 -- PRIMARY filegroup AND o.is_ms_shipped = 0 -- MS tables AND db_id(''?'') > 4' -- not system databases SELECT [dbid], dbname, objName, indName FROM #indexesStats DROP TABLE #indexesStats
The disadvantage of this method is that it uses the sp_msforeachdb undocumented stored procedure that might be removed in future versions of SQL Server. Read more about this procedure here.
Here is the result of the query:
Solution using Policy Based Management
Create Conditions
Create the condition that will be used to check only non-clustered indexes:- In SQL Server Management Studio (SSMS) go to the Management->Policy Management->Conditions
- Right click "Conditions", then click "New Condition…":
:
- Enter the name for the condition: "_Demo_cond: Non-clustered Indexes"
- Select "Index" facet
- Enter the following expressions:
- @IsClustered = FALSE
- @Is@IsXmlIndex = FALSE
- Click "OK".
- In SSMS go to the >Management->Policy Management->Conditions
- Right click "Conditions", then click "New Condition…"
- Enter the name for the condition: "_Demo_cond: Not MS Shipped Table"
- Select "Table" facet
- Enter the following expression:
- @Is@IsSystemObject = FALSE
- @Is@IsSystemObject = FALSE
Now create the condition that will be used to check the indexes filegroup name. We want to make sure that indexes are not on the "PRIMARY" filegroup, but you can add conditions to exclude other filegroups or, for example, make sure that only the "INDEXES" filegroup is used (@FileGroup = 'INDEXES'):
- In SSMS go to the >Management->Policy Management->Conditions
- Right click "Conditions", then click "New Condition…"
- Enter the name for the condition: "_Demo_cond: Secondary Index FileGroup"
- Select "Index" facet
- Select @FileGroup as the "Field"
- Add the expression to make sure that the name is not "PRIMARY":
If you want to monitor all of the databases you do not need the condition below. We can monitor all databases, but in practice you want to exclude from the monitoring at least system databases. We also, as an example, had in the past a vendor's application that did not support multiple data files in the database.
Create the condition to limit the number of the monitored databases:
- In SSMS go to the >Management->Policy Management->Conditions
- Right click "Conditions", then click "New Condition…"
- Enter the name for the condition: "_Demo_cond: In-house Developed Databases"
- Select "Database" facet
- Select @Name as the "Field"
- Add the expressions to filter the databases checked:
- If we want to check all user databases then we can change it to:
Exclude other databases by adding them to the "Array" function.
Create Policy
- In SSMS go to the >Management->Policy Management->Policies:
- Right click "Policies", then click "New Policy…": :
- Enter the name for the policy:>"Index Filegroup Check"
- Select condition "_Demo_cond: Secondary Index FileGroup" under "Check Condition"
- Replace under targets:
- "Every" Index with "_Demo_cond: Non-clustered Indexes"
- in "Every" Table with "_Demo_cond: Not MS Shipped Table"
- in "Every" Database with "_Demo_cond: In-house Developed Databases":
- If you want to check all databases then just leave "Every" (database)
selected:
- Click "OK" to save the policy.
Evaluate the policies
There are several ways to evaluate the policies:
- You can use the Registered Servers in SQL Server Management Studio (SSMS) to evaluate the policy. Refer to this tip for more information.
- Policies could be evaluated using the Central Management Server as well. Refer to this tip for the details.
- Another option is to schedule policies evaluation by setting the Execution Mode to "On Schedule". Refer to this tip for an example.
- There is also an option to evaluate policies using PowerShell and schedule it as a job as well.
Evaluate the policy and review the results:
Click "View" on a failed item to see the details:
Click "View" on a succeeded item to see the details:
Next Steps
- Learn more about different scenarios when designing a filegroup configuration in this tip.
- Make sure that an application supports multiple database files.
- Add a secondary filegroup to store your indexes if you have performance issues and can benefit from the multiple filegroups.
- Move non-clustered indexes to the new filegroup.
- Use Policy Based Management to make sure that the new indexes created on the new filegroup.
- If you started using multiple files and filegroups make sure that you reviewed your backup/restore strategy. Read here about benefits and disadvantages of file backups.
- Read more tips on Policy Based Management.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips