sql server

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

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

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

How to generate SQL Linked List Data with Python Generator

In this post, we would like to show how to quickly generate table values that are based on the values of another column of same table. For this purpose, we will use the Python Generator being a part of dbForge Data Generator for SQL Server. (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 Index Fragmentation In-depth

There is no way to avoid index fragmentation in any SQL Server environment. It does not depend on your SQL Server version or I/O subsystem you have, or your hardware. In this article, we will drill down into SQL Server index fragmentation issue. We will figure out why index fragmentation is a problem and how it affect on overall performance, discuss how to detect and avoid it. (more…)