Sergey Syrovatchenko posts

SQL Server 2016 Stretch Database

On June 1, SQL Server 2016 was released. It brought a large number of novelties into normal development, including the long-announced Stretch Database technology that allows migrating data from SQL Server dynamically to Azure. (more…)

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

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

SQL Server 2016 CTP3.1 – What’s New for Developer?

Not that long ago, I remember Microsoft’s CEO Satya Nadella has been announcing a new version of SQL Server 2016. And all of a sudden fresh Community Technology Preview versions started appearing one by one (currently the latest version is CTP3.1). Having tested the new version, I would like to share my impressions.

I will now review the new T-SQL syntax features of SQL Server 2016: JSON, GZIP, DROP IF EXISTS, TRUNCATE TABLE by partitions… (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…)