How To

Backup and Restore Databases in SQL Server

In this article, we will talk about the benefits of backing up SQL Server databases, different backup types, and backup strategies.

The immediate purpose of creating database backups is the ability to recover a damaged database and minimize the risk of catastrophic data loss. Thus, a reliable use of backup and restore for recovery requires a backup and restore strategy. (more…)

Enabling AUTO_CLOSE is a bad idea?

From a personal perspective, allowing a production database to run with AUTO_CLOSE option is not the best practice. Let me explain why you should not enable AUTO_CLOSE and the consequences of using this option.

(more…)

What is faster inside SUM & AVG: 0 or NULL?

There are three aggregate functions that are most often used in practice: COUNT, SUM and AVG. The first one has already been discussed previously, while the other two have interesting performance nuances. But first, some theory…

(more…)

Find invalid objects in your databases

DBA has a number of duties that are primarily targeted at supporting of database performance capabilities and data consistency. The administrator can use the CHECKDB command to easily verify the data consistency; however, in case they need to find an invalid object in a database schema, some difficulties may occur.

(more…)

How to Build a Database from Source Control

There is no doubt that database developers can and should benefit from using source control systems. Regardless of the type of source control system, developers also must think over the development model. They must also consider how they wish to build and deploy databases from source control.

In this article, we will discuss several approaches of how to build databases form source control. (more…)

How to reduce MSDB size from 42Gb to 200Mb

Recently I’ve got a spare minute to see why an old test server was running too slow… I had nothing to do with this, but I was very anxious to find out what was wrong with the server.

First thing, I opened Resource Monitor and looked at the overall load. The sqlserv.exe process took up 100% of CPU and generated a large disk queue exceeding 300… whereas the number greater than 1 is considered problematic. (more…)

Increasing SQLite Performance

One the major issues a developer encounters when using the SQLite DBMS in his applications is its performance issue.

Perhaps, a classic case everyone gets into when using SQLite for the first time is very slow execution of multiple INSERT/UPDATE/DELETE operations. Indeed, sequential executions of not even thousands, but hundreds of INSERTs into a table may take too long. (more…)

XML, XQuery & Performance Issues

In this article, we will discuss some pitfalls related to XML and XQuery that may lead to performance issues.

The following code will generate a test XML file:
(more…)

What is the fastest way to calculate the record COUNT?

I have always liked simple questions with lots of pitfalls. Here is the one: how do you count the total number of records in a table? At first sight, it’s a snap, but if you dig a little deeper, you can reveal lots of peculiar nuances.

So, let’s start from a simple thing. Do the following queries differ in terms of the end result?

(more…)

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…)