Frequently asked question

Should I rebuild indexes on my enterprise geodatabase datasets within SQL Server?

Last Published: April 25, 2020

Answer

Rebuilding indexes on data that resides in an enterprise database can help to improve the performance, as indexes can become fragmented due to frequent editing and compressing of the data. However, how does one know when the indexes where last updated on all of the enterprise geodatabase tables?

Below are two scripts that can be executed within the SQL Server Management Studio Query Window in which the statistics listed for all the table indexes within the enterprise geodatabase can be seen; ordered alphabetically (Script 1), or by the date when the indexes where last updated (Script 2).
SQL Server 2005 / SQL Server 2008

Code:
Script 1: List all table Index Statistics in current enterprise geodatabase.

USE [Database Name]
GO

SELECT
o.name AS TableName
, i.name AS IndexName
, i.type_desc AS IndexType
, STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
FROM
sys.indexes i
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE
o.type = 'U' --Only get indexes for User Created Tables
AND i.name IS NOT NULL
ORDER BY
o.name, i.type


SQL Server 2005 / SQL Server 2008

Code:
Script 2: When were Index Statistics last updated?

USE [ArcSDE Database Name]
GO

SELECT
o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created
FROM
sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
WHERE
o.[type] = 'U'
ORDER BY
STATS_DATE(i.[object_id], i.index_id) ASC;

Article ID:000011149

Software:
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Related Information

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options