Version

All Products

Date

30 Aug 2010

Summary

If performance issues persist even after various Argent tuning methods have been attempted, the database settings of the SQL Server itself may need investigation.

This KBI lists some of the areas to look into.

Technical Background

Simple vs. Full Recovery Model

The Recovery Model is a per-database setting, accessed by right-clicking on a database, selecting Properties, and clicking on Options:

Under Simple Recovery Model, the transaction log is not backed up. The disadvantage is that recovery is only available through the most recent full or differential backup. The transaction logs are truncated automatically at checkpoints.

Under Full Recovery Model, the transaction log is fully preserved to allow point-in-time recovery of the database. The disadvantage is the large size of the log files, increased maintenance and management and significant performance costs (disk I/O, CPU utilization) caused by frequent checkpoints of writing into the transaction logs.

To understand which Recovery Model the customer needs to use, we can think about these questions:

   * Is it vital to keep all performance data, or would restoring to the latest daily backup suffice?

   * Is it vital to keep all event data, or would restoring to the latest daily backup suffice?

   * If SQL Mirroring or Transaction Log Shipping in use? If so, the customer will always need to use the Full Recovery Model.

Auto-Growth Settings

Auto-growth is a setting that allows the database files and the transaction log files to automatically grow in size to accomodate data.

Auto-growth can be set to grow at a certain percentage, or a fixed amount of megabytes.

When an auto-growth is triggered, SQL allocates the space and creates a database lock on the database. This freezes the database for a certain amount of time, based on the size allocated, causing slow response times and ODBC timeouts.

If auto-growth is frequently occurring, this causes fragmentation in the data, which causes a longer-term slowdown.

For customers that are doing a large amount of monitoring work and complaining about intermittent ODBC timeouts and a Slow GUI — the auto-growth setting should be investigated to see if auto-growth is occurring too frequently and locking up the database each time.

There are several ways to check for auto-growth events — some involve doing a trace with the SQL Server Profiler, others involve fancy stored procedures.

However, the simplest approach is just to take a look at the database and transaction log sizes at one point, then perhaps a few hours later, look at the updated sizes, calculate the total growth, and then determine a ballpark figure of how many auto-growths took placed based on the growth setting in SQL Server.

If the file growth is excessive, try changing the auto-growth to a large value to see if the intermittent slowdowns no longer persist.

SQL Maintenance Jobs

When a SQL Maintenance Job is running, it could be slowing down the database — check the schedule and frequency of these maintenance plans.

Resolution

N/A