there is a performance.pdf that comes with Service Desk documentation. amongst other things there are a number of recommendations for the database platform. we have a reorganise index/rebuild index/update statistics database plan that we run once a week. which did give us some improvement back in the day. we also delete user sessions nightly as this table can grow quite large (table is tps_user_session). see excerpt from the pdf from page 12/13 below
Database maintenance planning
We recommend that a regular backup and maintenance plan is in operation against the Service Desk
The implementation of such plans is the responsibility of the customer’s Database Administrator.
SQL Server and Oracle: Rebuild Indexes
Regular re-indexing of tables or defragmenting of indexes helps to optimise database performance. Also,
monitor database statistics (especially after large data imports). We recommend that DBAs review index
fragmentation in the database and if necessary rebuild.
Microsoft SQL Server provides a standard maintenance plan to rebuild indexes on a database. We
recommend that you schedule this to run on a weekly basis.
SQL Server and Oracle: Update statistics
Microsoft SQL Server provides a stored procedure called sp_updatestatsistics that updates information
about the distribution of key values in the database. We recommend that you run this stored procedure
after importing data. Consider scheduling this stored procedure to run every overnight.
For Oracle you can configure an automated task in Oracle Database Control to gather optimizer statistics
during a defined maintenance window.
SQL Server and Oracle: Indexing the database
Indexing a database can significantly improve performance by providing faster searching. However, take
care with indexing, because it can affect data writing and deleting because the indexes also need to be
- updated. Indexing also makes the database larger.
For Microsoft SQL Server implementations, use this only after all other performance options have been
- explored. For Oracle implementations this is recommended initially and if many design changes are made.
Object and Query Designer changes affect the indexes.
Microsoft SQL Server provides tools that enable you to identify and implement database indexes that will
optimise the performance of procedures that are perceived to be slow.
On a test copy of the system, time the procedures, add the indexes using the procedure below, and then
time the same procedures again.
To create new indexes in SQL Server:
- Start SQL Profiler and create a new trace.
- Select the Tuning template.
- Start the trace and perform the non-performing steps that you previously decided on (for example,
logging and assigning an incident within Console), timing each stage.When you have finished the predefined steps stop the trace, and save the trace as a trc file.
4. When you have finished the predefined steps stop the trace, and save the trace as a trc file.
5. Start the Database Engine Tuning Advisor.
6. In the Workload group, select the File option button, then browse to your trace file.
7. Select the check box for the database you want to use for the workload analysis – this is just where
the tuning advisor does its work; it's best to stick with the same database throughout
8. Click Start Analysis.
The Database Engine Tuning Advisor analyses the database using the trace.
9. When the advisor returns its results, on the Action menu, click Save Recommendations
If there are no recommendations, then the performance issue isn't likely to do with the database itself
"Use, Go" statement can be deleted if you make sure you are running the script on the correct database.
- 10. After indexing the test database, run through the steps you decided on and take new timings to
compare with the original timings. If there is any significant improvement it is probably worth
running the script on the live system. If not, it's best NOT to create indexes for the sake of it
because of the aforementioned impact on writing data.
Similar tools are available for Oracle such as the SQL Tuning Advisor which can be used to recommend and
implement indexing changes.
WARNING: These tools can sometimes advise to remove indexes. Do not remove any existing indexes from the database.
Sometimes forget the guides can contain useful info.