sp_apt_reorganizeSystemTables.sql

/****************************************************************************************************************************
 A procedure to reorganize (essentially, partial defragmentation) system base tables
****************************************************************************************************************************/

-- A procedure to allow non-sysadmin users to reorganize (defragment) base system tables.
--
-- Because we have an atypical (compared to the primary use case the SQL team may be used to) usage pattern
-- of creating and dropping many tables (user/run tables in particular) the system base tables (e.g.,
-- the internal table that has a row representing every column in every object we create) may become
-- very fragmented over time. SQL Server does not automatically manage it, and some of the formal
-- defragmentation options are not available for system base tables. However, it is possible to reorganize
-- the leaf-level of these tables, an online operation that does not interfere with usage of the system
-- (other than creating a modest amount of I/O on the server).
-- See http://dba.stackexchange.com/questions/132293/can-sql-server-system-tables-be-defragmented for more.
--
-- Executing this procedure requires a db_owner level of access on the database being targeted.
-- However, under our current deployment mechanisms, it is more straightforward to deploy and sign
-- a single procedure to master and allow that procedure to target any database on the server.
-- Reorganizing base system tables is a non-blocking operation that does not return any data to the caller.
CREATE PROC dbo.sp_apt_reorganizeSystemTables
	@dbName SYSNAME
WITH EXECUTE AS OWNER
AS
BEGIN
	-- As a security / sanity check, confirm, in a manner not vulnerable to SQL injection, that the input string corresponds to a valid database.
	DECLARE @dbId INT = DB_ID(@dbName)
	IF (@dbId IS NULL)
	BEGIN
		RAISERROR('Could not find database %s. Please ensure that the database name is valid.', 16, 1, @dbName)
		RETURN
	END
	-- If the database name is valid, build an escaped version of the database name
	DECLARE @sanitizedDbName NVARCHAR(130) = QUOTENAME(DB_NAME(@dbId))

	CREATE TABLE #systemTablesToReorgnize (tableName SYSNAME NOT NULL, indexName SYSNAME NOT NULL)

	DECLARE @findSystemTablesSqlString VARCHAR(MAX) = '
		INSERT INTO #systemTablesToReorgnize (tableName, indexName)
		SELECT o.name AS tableName, i.name AS indexName
		FROM ' + @sanitizedDbName + '.sys.objects o
		JOIN ' + @sanitizedDbName + '.sys.indexes i
			ON i.object_id = o.object_id
			AND i.index_id <> 0
		WHERE o.type = ''S'' -- "System base table" is the object type
			AND o.schema_id = SCHEMA_ID(''sys'') -- "sys" schema
			AND o.is_ms_shipped = 1 -- Object is a part of SQL Server (as of SQL 2016 and earlier, redundant with previous checks, but we leave as an extra sanity check)
	'
	EXEC(@findSystemTablesSqlString)

	DECLARE @alterIndexReorganizeSqlString VARCHAR(MAX) = ''
	DECLARE systemTableCursor CURSOR LOCAL FAST_FORWARD FOR
	SELECT 'ALTER INDEX ' + QUOTENAME(indexName) + ' ON ' + @sanitizedDbName + '.sys.' + QUOTENAME(tableName) + ' REORGANIZE' AS alterIndexReorganizeSqlString
	FROM #systemTablesToReorgnize

	OPEN systemTableCursor
	FETCH NEXT FROM systemTableCursor INTO @alterIndexReorganizeSqlString

	WHILE @@FETCH_STATUS = 0
	BEGIN
		RAISERROR('Executing the following SQL: %s', 0, 1, @alterIndexReorganizeSqlString) WITH NOWAIT
		EXEC(@alterIndexReorganizeSqlString)
		FETCH NEXT FROM systemTableCursor INTO @alterIndexReorganizeSqlString
	END
	
	CLOSE systemTableCursor
	DEALLOCATE systemTableCursor
END
GO