sql server

SQL Server 2016 RC0

The news about Microsoft’s intention to “chum” SQL Server with Linux keeps floating around. But I haven’t heard a single word about SQL Server 2016 Release Candidate, that recently became available for download. (more…)

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.

The other day, I had to look in Error Log on a test server. After a two-minute timeout, I saw a great number of messages stored in the log, and I decided to check the log size using xp_enumerrorlogs:

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

When using aggregate functions in the execution plan, there may be two operators: Stream Aggregate and Hash Match, depending on the input stream.

The first may require pre-sorted set of input values, while Stream Aggregate does not block the execution of subsequent operators.

In turn, Hash Match is a blocking operator (with rare exceptions) and does not require sorting of the input stream. Hash Match uses a hash table that is created in memory, and in the case of incorrect assessment of the expected number of rows, the operator can spill the results into tempdb.
(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.

ORACLE for instance, allows you to get a list of invalid objects:

SQL Server doesn’t allow to do that directly. In most cases, you need to execute a script to see that an object is invalid. This is very inconvenient…
So let’s create a script that will search invalid objects: (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.

When analyzing disk activity, I observed continuous IO operations in msdb:

I looked at the size of msdb:

and switch to the “facepalm” mode:

The data file takes up 42 GB… After a small break, I began to investigate the reason for such «unhealthy» size of msdb, and how to overcome the problems with server performance.
(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…)

IT NonStop 2015 – Kharkiv (Ukraine)

The international IT conference IT NONSTOP was held in Kharkiv (Ukraine) on November 28, 2015 and gathered a lot of enthusiastic participants.

A speaker of the conference, Syrovatchenko Sergey (SQL Server DBA / DB Consultant from Devart) talked to the participants of the conference about the «Mortal sins in the development on SQL Server». Here is a brief of his report:

“When people start to deal with something new, they pass though thorny path of trial and error. In my report, I tried to give a list of “traps” I got in many times when I’d just started working with SQL Server. Typical errors relating to filtering values, implicit type conversion, and the proper use of indexes were briefly discussed…”

Awarding dbForge Studio license during the event was a pleasant bonus.
(more…)