Statistics and Indexes rebuild of the DataStore database in SQL.
DataStoreDSX Database performance
DataStore DSX supplies stored procedures that are designed to rebuild or reorganise SQL table indexes and rebuild the statistics of the tables in the DataStore database. The stored procedures are named 'uspDSNET_Utility_RebuildIndexes' and 'uspDSNET_Utility_RebuildStatistics'. A third stored procedure named 'uspDSNET_Utility_TableIndexInfo' is supplied to display the statistics of all the objects in the DataStore database when executed.
Setting up the jobs.
Both stored procedures can be added to existing jobs if needed, they can also be run as stand alone tasks without any parameters. If a new job is required then the following is one possibility.
- In SQL Server Management Studio, go to SQL Server Agent and Jobs, right click and select New Job. All the pages except Steps can be set up as desired as long as the users executing the job has the appropriate permissions to execute SQL Server Agent Jobs and has the permissions to execute the SQL commands against the DataStore database.
- In the Steps page, two steps need to be added, the first should be to run the Rebuild Indexes stored procedure, the second should be to run the Rebuild Statistics stored procedure. The Rebuild Indexes stored procedure should be run first followed by the Rebuild Statistics store procedure. Below is one way this could be done. Each stored procedure is scripted separately and the SQL script produced is copied into there respective steps.
Note that the GO command is optional, it must not appear more then once in the command.
3. The schedule should be set at a time when DataStore is not processing data or servicing user requests. Depending on the quantity of documents being processed the scheduled job should not be run more than one a day.
Results and Errors
When either run manually or as a scheduled task in SQL, the jobs are run using the SQLServerAgent credentials, typically this would be NT Service\SQLSERVERAGENT but could be any user depending on how the SQL Server Agent service was set up.
If the Job was successfully run then the following is displayed if run manually
If the task runs as a scheduled task then right clicking on the job in SQL Server Agent and selecting 'View History' will display the results of all execution attempts. The results of all manually runs are also displayed.
If either running the job manually or as part of a schedule fails, the View History dialog will display the nature of the failure, the failure is also logged in the servers application log. Running the job manually will cause the job to fail the same way and a message appears in the message column describing the nature of the failure. Any errors in processing will be related to SQL Server Agent or SQL and will not be directly related to DataStoreDSX. Any amendments or corrections should be to the job itself or to the account setup to run SQL Server Agent.
Addendum
The Rebuild Indexes stored procedure will either rebuild a table index if it has a fragmentation of more than 30%, if the fragmentation is between 10% and 30 % the index will be reorganized.
If either the 'uspDSNET_Utility_RebuildIndexes' or 'uspDSNET_Utility_RebuildStatistics' stored procedures are manually executed, one of the two parameters @force can be set to 1, the result of setting @force to 1 is to force the stored procedure to rebuild all indexes regardless of the current fragmentation. This should be used in the event that the database is extremely fragmented, for example if neither of the stored procedures had ever been run against the DSX database. The parameter should not be used in the job.