Reindex GDB Tables

[CodyPatterson]

Hey All,

Enterprise 11.2 Geodatabase.

I have been reindexing our Enterprise Geodatabase every day, as we write quite often to this DB, and have noticed using a fragmentation query, that we have a few tables that don't seem to reindex, these include GDB_ITEMS and SDE_column_registry. These are unable to be selected via ArcGIS Pro, so I was curious if these should be reindexed, or if these should just be disregarded, they are quite large.

Thanks in advance!

Cody

[KimberleeHermann1 · Accepted solution]

Hello...if you are using SQL Server it is super easy to use the Maintenance Plan wizard and do a reindex of everything on a database. Usually doesn't take long at all. I have it scheduled for all of my databases each weekend and that seems to keep thing "all good".

[CodyPatterson]

Hey @KimberleeHermann1 

Thank you for that tip, I was not sure if the SQL way would be preferable, or the ArcGIS Pro way, but it seems that the SQL way is more preferred! I will give that a shot and go through with it that way, my initial assumption was that since they are ArcGIS Pro provided indexes through GDB functionality, then they could only be rebuilt within Pro, but we learn as we go!

Cody

[MarceloMarques · Accepted solution]

@CodyPatterson - I usually avoid using the ArcGIS Pro GP Tools to gather new statistics and to rebuild indexes (attribute indexes and spatial indexes ) in my production geodatabases because these tools to not offer advanced options that you find with the SQL Server SQL Commands to do the same. I suggest you use the SQL Server Tools to do this maintenance. 

You can find my maintenance scripts for SQL Server in my community.esri.com blog below, download the database template scripts for SQL Server.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

Your question about the GDB_ITEMS table and any other ArcSDE Repository Tables. Yes, those tables must be reindexed as well, probably not every day, as you will not be creating new featureclasses, etc. in the geodatabase, but you should reorganize indexes weekly and rebuild indexes monthly at least for the ArcSDE Repository Tables, and right after that gather new statistics.

For reference to others about this discussion follow below the links for the ArcGIS Pro GP Tools.

Enterprise geodatabase maintenance tasks—ArcGIS Pro | Documentation
Modify a spatial index—ArcGIS Pro | Documentation
Rebuild Indexes (Data Management)—ArcGIS Pro | Documentation
Update database statistics—ArcGIS Pro | Documentation

I hope this helps.

[CodyPatterson]

Hey @MarceloMarques 

Thank you for the tip! I will check that out to see what I can manage with that! I spoke with another commenter and said that I was not confident on which method to use, since my assumption was that since it's a Pro index, I must do it in Pro, but it looks like that's incorrect.

I appreciate the documentation too!

Cody

[ModyBuchbinder]

Hello all

I am running in similar program.

I use Maintenance Plan to check and index my tables.

When it try to reindex GDB_ITEMS I get this error:

ALTER INDEX [R6_pk] ON [sde].[GDB_ITEMS] REORGANIZ..." failed with the following error: "The index "R6_pk" on table "GDB_ITEMS" cannot be reorganized because
page level locking is disabled."

We did not changed anything in the system tables.

Any idea?

[MarceloMarques]

This is well known, and the change to the indexes was implemented many releases ago when Geodatabase Branch Versioning was introduced. Refer to my earlier reply above for links to my database maintenance scripts.

Examples:

- execute every month - rebuild indexes

CREATE PROCEDURE [dbo].[mp_rebuild_indexes_gisgdb] 
  @fillfactor INT = 75
AS
BEGIN
    DECLARE @DbId VARCHAR(255) 
	DECLARE @Database VARCHAR(255)   
	DECLARE @SchemaName VARCHAR(256)
    DECLARE @TableName VARCHAR(256)
    DECLARE @IndexName VARCHAR(256) 
	DECLARE @cmd NVARCHAR(500) 
	--DECLARE @fillfactor INT = 75

	DECLARE DatabaseCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR  
	SELECT dbid, name FROM master.dbo.sysdatabases   
	WHERE name NOT IN ('master','msdb','tempdb','model')
	AND name = 'gisgdb'
	ORDER BY 1  

	OPEN DatabaseCursor  

	FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database  
	WHILE @@FETCH_STATUS = 0  
	BEGIN  

	  PRINT ('DB: '+ @Database)
	  SET @cmd = 'USE ' + @Database + ';'
	  PRINT (@cmd)
	  EXEC (@cmd) 
	  PRINT ('Indexes allow_page_locks = 1')
	  --Indexes allow_page_locks = 1	
      SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId +') schema_name, t.name table_name,  i.name index_name
	  FROM ' + @Database + '.sys.indexes i
      INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id
      WHERE i.allow_page_locks = 1 AND t.Name IS NOT NULL AND i.name IS NOT NULL'

	  -- create index cursor  
	   PRINT (@cmd)
	   EXEC (@cmd)  
	   OPEN IndexCursor   

	   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   WHILE @@FETCH_STATUS = 0   
	   BEGIN   
           PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName)
		   BEGIN TRY
			   --SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD;'
			   PRINT @cmd
			   EXEC (@cmd) 
		   END TRY
		   BEGIN CATCH
			   PRINT 'This is the error: ' + error_message()
		   END CATCH
		   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   END   
	   CLOSE IndexCursor   
	   DEALLOCATE IndexCursor 
 
      PRINT ('Indexes allow_page_locks = 0')
	  --Indexes allow_page_locks = 0 	
      SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId + ') schema_name, t.name table_name,  i.name index_name
	  FROM ' + @Database + '.sys.indexes i
      INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id
      WHERE i.allow_page_locks = 0 AND t.Name IS NOT NULL AND i.name IS NOT NULL'

	   -- create index cursor  
	   PRINT (@cmd)
	   EXEC (@cmd)  
	   OPEN IndexCursor   

	   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   WHILE @@FETCH_STATUS = 0   
	   BEGIN   
           PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName)
		   BEGIN TRY
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = ON);'
			   PRINT @cmd
			   EXEC (@cmd)
			   --SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD;'
			   PRINT @cmd
			   EXEC (@cmd) 
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = OFF);'
			   PRINT @cmd
			   EXEC (@cmd)
		   END TRY
		   BEGIN CATCH
			   PRINT 'This is the error: ' + error_message()
		   END CATCH
		   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   END   
	   CLOSE IndexCursor   
	   DEALLOCATE IndexCursor  
	   FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database  
	END  
	CLOSE DatabaseCursor   
	DEALLOCATE DatabaseCursor
END
GO

- execute every week - reorganize indexes

CREATE PROCEDURE [dbo].[mp_reorganize_indexes]
AS
BEGIN
    DECLARE @DbId VARCHAR(255) 
	DECLARE @Database VARCHAR(255)   
	DECLARE @SchemaName VARCHAR(256)
    DECLARE @TableName VARCHAR(256)
    DECLARE @IndexName VARCHAR(256) 
	DECLARE @cmd NVARCHAR(500) 

	DECLARE DatabaseCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR  
	SELECT dbid, name FROM master.dbo.sysdatabases   
	WHERE name NOT IN ('master','msdb','tempdb','model')
	--AND name = 'topo2'
	ORDER BY 1  

	OPEN DatabaseCursor  

	FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database  
	WHILE @@FETCH_STATUS = 0  
	BEGIN  

	  PRINT ('DB: '+ @Database)
	  SET @cmd = 'USE ' + @Database + ';'
	  PRINT (@cmd)
	  EXEC (@cmd) 
	  PRINT ('Indexes allow_page_locks = 1')
	  --Indexes allow_page_locks = 1	
      SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId +') schema_name, t.name table_name,  i.name index_name
	  FROM ' + @Database + '.sys.indexes i
      INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id
      WHERE i.allow_page_locks = 1 AND t.Name IS NOT NULL AND i.name IS NOT NULL'

	  -- create index cursor  
	   PRINT (@cmd)
	   EXEC (@cmd)  
	   OPEN IndexCursor   

	   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   WHILE @@FETCH_STATUS = 0   
	   BEGIN   
           PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName)
		   BEGIN TRY
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE  WITH ( LOB_COMPACTION = ON );'
			   PRINT @cmd
			   EXEC (@cmd) 
		   END TRY
		   BEGIN CATCH
			   PRINT 'This is the error: ' + error_message()
		   END CATCH
		   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   END   
	   CLOSE IndexCursor   
	   DEALLOCATE IndexCursor 
 
      PRINT ('Indexes allow_page_locks = 0')
	  --Indexes allow_page_locks = 0 	
      SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId + ') schema_name, t.name table_name,  i.name index_name
	  FROM ' + @Database + '.sys.indexes i
      INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id
      WHERE i.allow_page_locks = 0 AND t.Name IS NOT NULL AND i.name IS NOT NULL'

	   -- create index cursor  
	   PRINT (@cmd)
	   EXEC (@cmd)  
	   OPEN IndexCursor   

	   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   WHILE @@FETCH_STATUS = 0   
	   BEGIN   
           PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName)
		   BEGIN TRY
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = ON);'
			   PRINT @cmd
			   EXEC (@cmd) 
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE  WITH ( LOB_COMPACTION = ON );'
			   PRINT @cmd
			   EXEC (@cmd) 
			   SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = OFF);'
			   PRINT @cmd
			   EXEC (@cmd)
		   END TRY
		   BEGIN CATCH
			   PRINT 'This is the error: ' + error_message()
		   END CATCH
		   FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName   
	   END   
	   CLOSE IndexCursor   
	   DEALLOCATE IndexCursor  
	   FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database  
	END  
	CLOSE DatabaseCursor   
	DEALLOCATE DatabaseCursor
END
GO

[KimberleeHermann1]

You are very welcome!! So glad it worked for you.