All

Date

18 Nov 2011

Summary

Slow SQL Server

Symptoms: Long delays in running Relators and firing alerts

Technical Background

There are a large number of possible causes of poor SQL Server performance

This KBI lists some of the more common causes

Resolution

Check recovery model: Argent recommends the Simple Recovery Model.

Also check Auto-Growth settings to ensure that growth isn’t being frequently triggered.

Growing the database size is an expensive disk operation and should be tuned to accommodate the growth rate of the database

See: http://help.Argent.com/#kbi_310215

When a SQL Maintenance Jobs are running, it could be slowing down the database — check the schedule and frequency of these maintenance plans including backup of SQL. Make sure these activities are not scheduled during busy hours.

Don’t run the defragger when Automatic Reports are being distributed by Argent

At least 20% free space should be available in the database

Check how quickly does the Transaction Log grow? How often is the Transaction Log truncated via “BACKUP LOG…”?

See: http://help.Argent.com/#Q669

Reduce the report LOOK-BACK date range in automatic report generation; this will reduce the time report query takes

Increase the schedule times for automatic reports — it is not unknown for an report scheduled every 15 minutes to take 20 minutes to generate — this is obviously a loop