How To Maintain and Optimize the SQL Server Database
Note: This applies to DocuShare 6.5.x ONLY
For active sites it is recommended that the following maintenance/optimization is done to the database:
· Daily update statistics
· Check on fragmentation once a month
Consult your DBA before attempting to run these commands. The DBA will determine the appropriate settings and configurations for your environment. Only the DBA has the security permissions to run the database optimization.
Note: Depending on the size and usage of your DocuShare site, performance may be slow during database optimization. We recommend that you use Site Management/Access and set Site Access Authority to Administrator.
The following commands are recommendations for various database applications. Changes may be necessary to fit your specific environment. All commands in the examples run on the database server, which may or may not be the same server that is running your DocuShare site.
Note: This database optimization procedure assumes that the default installation of DocuShare is on a Windows server.
An SQL server Database Administrator may use the SQL Query Analyzer tool to update statistics for all DocuShare tables and indexes. The SQL Query Analyzer is part of the Microsoft SQL Server Enterprise Manager, and only members of sysadmin can access the database management tools. Optionally, the DBA may choose to use the SQL Query
Caution: Only an experienced Microsoft SQL Server DBA should use the analyzer. Creating unnecessary indexes can severely degrade database performance.
Note: sp_updatestats effectively executes UPDATE STATISTICS, by specifying the ALL keyword, against all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables. Statistics on disabled nonclustered indexes are also updated by sp_updatestats. sp_updatestats ignores tables with a disabled clustered index.
To optimize the database from the query analyzer:
UPDATE STATISTICS DSObject_table WITH FULLSCAN;
UPDATE STATISTICS ACL_table WITH FULLSCAN;
UPDATE STATISTICS Link_table WITH FULLSCAN;
UPDATE STATISTICS dsprop_table WITH FULLSCAN;
UPDATE STATISTICS userInfo_table WITH FULLSCAN;
UPDATE STATISTICS eventTable WITH FULLSCAN;
UPDATE STATISTICS eventObjectTable WITH FULLSCAN;
UPDATE STATISTICS CE_table WITH FULLSCAN;
UPDATE STATISTICS rootObjectTable WITH FULLSCAN;
To optimize the database from a Command Line:
1. Open a command prompt window.
2. Use the CD (change directory) to navigate to the Binn directory.
For example: CD C:\Xerox\Docushare\SQLExpress\MSSQL.2\MSSQL\Binn
3. From the Binn directory, run the osql.exe command by entering the following line, replacing all bracketed names with the correct values for your installation:
osql -S [servername]\[MSDE_InstanceName] -d [DocushareDatabaseName] -U
SA -P [SA_Password]
Note : The default Instance Name at the time of install is Docushare and the default Database Name is Docushare.
4. Press Enter. The command displays the OSQL prompt 1>.
Note: If the 1> prompt is not displayed then you will need to verify the information specified in step 3 is correct for you server.
5. At the 1> prompt, enter UPDATE STATISTICS DSObject_table WITH FULLSCAN; and press Enter.
6. At the 2> prompt, enter UPDATE STATISTICS ACL_table WITH FULLSCAN; and press Enter.
7. At the 3> prompt, enter UPDATE STATISTICS Link_table WITH FULLSCAN; and press Enter.
8. At the 4> prompt, enter UPDATE STATISTICS dsprop_table WITH FULLSCAN; and press Enter.
9. At the 5> prompt, type UPDATE STATISTICS userInfo_table WITH FULLSCAN; and press Enter.
10. At the 6> prompt, type UPDATE STATISTICS eventTable WITH FULLSCAN; and press Enter.
11. At the 7> prompt, type UPDATE STATISTICS eventObjectTable WITH FULLSCAN; and press Enter.
12. At the 8> prompt, type UPDATE STATISTICS CE_table WITH FULLSCAN; and press Enter.
13. At the 9> prompt, type UPDATE STATISTICS rootObjectTable WITH FULLSCAN; and press Enter.
14. At the 10> prompt type go and press Enter. When the optimization processing is complete, OSQL displays a 1> prompt.
Example:
C:\Xerox\Docushare\SQLExpress\MSSQL.2\MSSQL\Binn>osql -S MYSERVER\Docushare -d Docushare -U sa –P password01