Is This Content Helpful?
We're glad to know this article was helpful.
Should I rebuild indexes on my enterprise geodatabase datasets within SQL Server?
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
Script 1: List all table Index Statistics in current enterprise geodatabase.
USE [Database Name]
o.name AS TableName
, i.name AS IndexName
, i.type_desc AS IndexType
, STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate
JOIN sys.objects o ON i.[object_id] = o.[object_id]
o.type = 'U' --Only get indexes for User Created Tables
AND i.name IS NOT NULL
Script 2: When were Index Statistics last updated?
USE [ArcSDE Database Name]
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
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
o.[type] = 'U'
STATS_DATE(i.[object_id], i.index_id) ASC;