Database Reference
In-Depth Information
4.
Calculate the time interval between two consecutive full backups as follows:
Time interval = (Total backup time window) / (Number of full backups).
5.
Schedule the full backups in order of the database priorities, with the first backup starting
at the start time of the backup window and subsequent backups spread uniformly at the
time intervals calculated in the preceding equation.
This uniform distribution of the full backups will ensure that the backup infrastructure is not flooded with
too many backup requests at the same time, thereby reducing the impact of the full backups on the database
performance.
Backup Compression
For relatively large databases, the backup durations and backup file sizes usually become an issue. Long backup
durations make it difficult to complete the backups within the administrative time windows and thus start affecting
the end user's experience. The large size of the backup files makes space management for the backup files quite
challenging, and it increases the pressure on the network when the backups are performed across the network to a
central backup infrastructure. Compression also acts to speed up the backup process since fewer writes to the disk
are needed.
The recommended way to optimize the backup duration, the backup file size, and the resultant network pressure
is to use
backup compression.
SQL Server 2008R2 SP1 and greater allows for backup compression for the Standard
edition and better.
Query Design
Here's a list of the performance-related best practices you should follow when designing the database queries:
SET NOCOUNT ON
.
•
Use the command
•
Explicitly define the owner of an object.
•
Avoid
nonsargable
search conditions.
WHERE
clause columns.
•
Avoid arithmetic operators and functions on
•
Avoid optimizer hints.
•
Stay away from nesting views.
•
Ensure there are no implicit data type conversions.
•
Minimize logging overhead.
•
Adopt best practices for reusing execution plans.
•
Adopt best practices for database transactions.
•
Eliminate or reduce the overhead of database cursors.
•
Natively compile stored procedures.
I further detail each best practice in the following sections.
