English

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

Question

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

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;

Related Information