Can You Stop People From Creating Indexes With Low Fill Factors?

Yeah nah


So like, presented without much comment, this server level trigger will, in my limited testing, “work”.

Just make sure you understand something: this doesn’t stop the index from being created, it only rolls creation back afterwards.

If someone creates a gigantic index on an equally gigantic table, you’re in for a hell of a ride. I’d probably only deploy this on local dev boxes, and only if I really needed to prove a point.

CREATE OR ALTER TRIGGER CheckFillFactor
ON ALL SERVER
FOR CREATE_INDEX, ALTER_INDEX
AS
DECLARE @FillFactor NVARCHAR(4000);
DECLARE @Percent INT;
SELECT @FillFactor = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(4000)');
IF UPPER(@FillFactor) LIKE '%FILLFACTOR%'
    BEGIN
        SET @FillFactor = REPLACE(@FillFactor, ' ', '');
        PRINT @FillFactor;
        SELECT @FillFactor = SUBSTRING(@FillFactor, CHARINDEX(N'FILLFACTOR=', @FillFactor) + LEN(N'FILLFACTOR='), PATINDEX('%[^0-9]%', @FillFactor) + 2);
        
		IF TRY_CONVERT(INT, @FillFactor) IS NULL
		    BEGIN
			    SET @Percent = LEFT(@FillFactor, 2);
			END;
		ELSE
		    BEGIN
			    SET @Percent = @FillFactor;
			END;

		IF @Percent < 80
            BEGIN
                RAISERROR('WHY YOU DO THAT?', 0, 1) WITH NOWAIT;
                ROLLBACK;
            END;
    END;
GO

 

It’ll work for create or alter index commands, i.e.

--Fails, under 80
CREATE INDEX whatever ON dbo.Users (Reputation) WHERE Reputation > 100000 WITH (FILLFACTOR = 70);

--Works, over 80
CREATE INDEX whatever ON dbo.Users (Reputation) WHERE Reputation > 100000 WITH (FILLFACTOR = 90);

--Fails, under 80
ALTER INDEX whatever ON dbo.Users REBUILD WITH (FILLFACTOR = 70);

--Works, uses default
CREATE INDEX whatever ON dbo.Users (Reputation) WHERE Reputation > 100000;

Pink Blood


Is it perfect? Probably not, but I threw it together quickly as a POC.

For instance, my first stab broke when fill factor wasn’t specified in the command.

My second stab broke when I changed the spacing around the “=”.

Let me know in the comments if you can get around it or break it, other than by changing server settings — I can’t go that far here.

Thanks for reading!



5 thoughts on “Can You Stop People From Creating Indexes With Low Fill Factors?

      1. 10/10 patch. My next test was going to be playing around with RESUMABLE = ON and MAX_DURATION to see if it could get around the trigger, but I don’t have 2019 installed yet… So I’m declaring the updated trigger invincible.

Leave a Reply

Your email address will not be published. Required fields are marked *