Filter Schema In Index Optimize Script

Question

Is it possible to do index optimization with Ola's scripts and have it filter out a specific schema?

Answer 1

Yes, if you look at the documentation, you see that you can use the @indexes parameter to specify which indexes to defragment. You can use % wildcards in your object names and you can prefix them with - to exclude indexes.

Using this information, the syntax you want to use is @indexes = 'ALL_INDEXES, -databasename.excludedschema.%', or @indexes = 'ALL_INDEXES, -%.excludedschema.%' if you want to exclude the schema in all databases.

Below you can find a repro documenting this:

First create a database with 2 fragmented tables, one in the dbo schema and one in a schema I called Exclusion. Both should be around 99% fragmented.

USE [master];
GO

CREATE DATABASE [196090];
GO
USE [196090];
GO

SET NOCOUNT ON;
GO

-- Create a table in dbo schema
CREATE TABLE [ProdTable] (
    [c1] INT,
    [c2] CHAR (5000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]);
GO

-- Fill with random integers to create fragmentation
INSERT INTO [ProdTable] (c1, c2) VALUES  (CRYPT_GEN_RANDOM(8000), 'filler');
GO 12800

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'196090'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO

-- Create a schema to exclude from the rebuild
CREATE SCHEMA Exclusion;
GO

-- Fill with random integers to create fragmentation
CREATE TABLE [Exclusion].[ProdTableExclude] (
    [c1] INT,
    [c2] CHAR (5000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [Exclusion].[ProdTableExclude] ([c1]);
GO

INSERT INTO [Exclusion].[ProdTableExclude] VALUES  (CRYPT_GEN_RANDOM(8000), 'filler');
GO 12800

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'196090'), OBJECT_ID (N'Exclusion.ProdTableExclude'), 1, NULL, 'LIMITED');
GO

Then run Ola's index maintenance this way:

EXECUTE dbo.IndexOptimize
@Databases = '196090',
@indexes = 'ALL_INDEXES, -196090.Exclusion.%' 

And you will notice the script will skip the schema, leaving one of the indexes fragmented.