Using Policy Based Management to check that SQL Server indexes are created in the correct filegroup

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".

We want to exclude system tables from these checks as moving indexes for system tables is not supported by Microsoft (any modification to the system tables is not supported). Similar to the condition above create the condition that will be used to check only indexes on non-MS-shipped tables (non-system tables):

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

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

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips