3

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

7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.