Sergey Syrovatchenko posts

SQL Server Typical Maintenance Plans: Automated Database Backups

There are a lot of posts appealing to one simple truth – need perform backups on a regular basis. People will always be divided into two categories: those who do backup, and those who don’t. The first category, which ignores this advice, can often be found on relevant forums with much the same questions:

– My discs failed/someone has deleted my db… How can I restore my data?
– Do you have the latest backup?
– No.

In order to avoid this unpleasant situation, minimum effort is required. First thing you should do is select a disk array for storing backup copies. Since storing backups together with database files is not our choice, the second step will be to create a maintenance plan for the database backup. (more…)

SQL Server Typical Maintenance Plans: Automatic Statistics Update

Some time ago, we reviewed the automation of index defrag. Now, it is time to look at statistics.

First of all, what do we need statistics for?

During execution of any query, query optimizer is trying to build an optimal execution plan (within the limits of available information). The plan constitutes the operations sequence, by means of which you can get the result described in the query.

While selecting one or another operation, the query optimizer considers statistics, that describes value distribution for columns within a table or index, as one of the most significant input data resources.

(more…)

Instant File Initialization – Killer Feature for SQL Server

When SQL Server reserves new space on a disk, it initializes the space with zeros. This behavior can be switched off thereby reducing the execution time of some operations and the load on the disk subsystem.

Disk space reservation without initialization is called Instant File Initialization. This feature is not widely known, although its use has become possible starting with SQL Server 2005. (more…)

SQL Server Typical Maintenance Plans – Part 1: Automated Index Defragmentation

It’s incorrect to consider a database as a stable unit, since various kinds of undesirable situations can appear over time — database performance degradation, malfunctions, etc.

To minimize occurrence of such situations, maintenance plans are created, that guarantee stability and optimal performance of any databases.

Among maintenance plan tasks, the following can be pointed out:

  1. Index defragmentation
  2. Statistics update
  3. Backup and checking the logical and physical integrity of all the objects in database

Let’s consider automation of each task. (more…)