SDE / SQL DB Tuning

Question

I've got a 'production' geodatabase on a 32-bit SQL Server 2008 R2 platform with ArcSDE 10 SP 3.  The database has about 4-5 concurrent editors at any given time working a normal 40-hour work week.

My question is:  How often do we need to 'tune' the database.  We're compressing daily as part of an automated reconcile / post process, but I'm more concerned about 'Updating Statistics' and 'Re-building Indexes'.

Any thoughts out there?

Thanks in advance...

jskinner · Answer

Hi Cameron,

Yes, that is a fair assessment.  The 10.1 help also provides examples on how you can create a python script to update your database statistics.  Updating database statistics can be an I/O-intensive operation, so you  might want to do this while most users are logged out of the database.  Having Windows Task Scheduler execute a script is the perfect solution for this.

CameronIrvine · Answer

Hi Kent,

Take a look at the following link about updating statistics.

You should update statistics before and after a compress operation, after you add or remove topology rules, and after you have finished importing, loading, or copying data into an ArcSDE geodatabase.

Here is a link about rebuilding indexes.  If you are using SQL Server Enterprise edition, refer to the following KB article on how to rebuild indexes.

Hi Jake,

I noticed that you referred to the KB article http://support.esri.com/en/knowledgebase/techarticles/detail/24518 regarding rebuilding and analysing data. Is it best to use the SQL script in this KB or use the Analyze tool in Toolbox? It looks like they do the same thing but the SQL script seems more user friendly and easier to administer as it automatically will analyze and index all tables associated with the data owner. If I were to use the analyze tool say through model builder then I would have to add each feature class in and then update it each time I add a new feature class to the geodatabase. Looking at 10.1 this tool seems to have been improved to automatically do all the feature classes and tables etc but we are still at 9.3. Is that a fair assessment? Stay with the SQL script until we move to 10.1?

Thanks
Cameron

jskinner · Answer

Hi Kent,

Take a look at the following link about updating statistics.

You should update statistics before and after a compress operation, after you add or remove topology rules, and after you have finished importing, loading, or copying data into an ArcSDE geodatabase.

Here is a link about rebuilding indexes.  If you are using SQL Server Enterprise edition, refer to the following KB article on how to rebuild indexes.