Devart Blog

Enabling AUTO_CLOSE is a bad idea?

Posted by on February 12th, 2016

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:

EXEC sys.xp_enumerrorlogs

SELECT lod_id, last_log, size_mb = size / 1048576.
lod_id   last_log              size_mb
-------- --------------------- ---------------
0        2016-01-05 08:46:00   567.05288505
1        2015-12-31 12:53:00   1370.39249420
2        2015-12-18 11:32:00   768.46394729
3        2015-12-02 13:54:00   220.20050621
4        2015-12-02 13:16:00   24.04152870
5        2015-11-16 13:37:00   80.07946205
6        2015-10-22 12:13:00   109.33527946

Read the rest of this entry »

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

Posted by on January 18th, 2016

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.
Read the rest of this entry »

Find invalid objects in your databases

Posted by on January 4th, 2016

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:

SELECT owner, object_type, object_name
FROM all_objects
WHERE status = 'INVALID'

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: Read the rest of this entry »

How to Build a Database from Source Control

Posted by on December 24th, 2015

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. Read the rest of this entry »

How to reduce MSDB size from 42Gb to 200Mb

Posted by on December 22nd, 2015

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:

SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024
FROM sys.database_files

and switch to the “facepalm” mode:

name         size           space_used
------------ -------------- ---------------
MSDBData     42626.000000   42410.374395
MSDBLog      459.125000     6.859375

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.
Read the rest of this entry »

Increasing SQLite Performance

Posted by on December 21st, 2015

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.
The origin of the issue lies in the specificity of using transactions in SQLite. SQLite starts a transaction automatically every time before any DML statement execution and commits it after execution. Accordingly, when executing multiple consequent statements, a new transaction will be started and committed for each statement.

The solution of this problem is quite simple — the block of DML statements may be enclosed into BEGIN … END operators block ( ). In this case, each DML statement won’t be executed in a separate transaction, but a single transaction will be started before the whole block execution and committed after all modifications.

Such an approach increases SQLite data modification performance by times. See more details about it in the SQLite documentation ( ).

However, this approach is not the only way to increase performance in SQLite. Parameters of the DBMS may also be configured using so-called PRAGMA ( ). The fact is that SQLite parameters are oriented not to high performance by default, but to maximum data safety and integrity. Modification of these parameters may increase performance, however, note, that the data corruption risks increase too.

Let’s analyze the impact to inserts performance by different PRAGMAs using LiteDAC. Read the rest of this entry »

XML, XQuery & Performance Issues

Posted by on December 21st, 2015

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:
Read the rest of this entry »

Devart was the Sponsor of Ukrainian Data Community Kyiv

Posted by on December 16th, 2015

We were more than happy to take part in the New Year meeting of Ukrainian Data Community in Kyiv on 15 Dec 2015. It was the great event, where we had a great chance to talk to the community and present our products to the wide audience of professionals.

There were two outstanding speakers that shared their knowledge to the visitors:

Alexander Kalenik: Using Columnstore indexes in MS SQL Server (2012, 2014, 2016CTP3).

Alexander Kalenik is Microsoft CIS technology lead, Senior Premier Field Engineer for areas of SQL Server and Windows Cluster. He is working for Microsoft almost 9 years. He has a big experience in SQL Server from 1991. He is author of books about SQL Server and author of 2 blogs on TechNet for areas SQL Server and Windows Cluster. Alexander is PHD in area of Computer Science.

Vitalii BondarenkoDive into Hadoop (HDInsight): common Big Data analysis scenarios on Microsoft Azure

Vitalii is a DW/BI/ETL Architect and Technical Lead experienced in OLAP and OLTP systems design, performance tuning and administration. Total professional record includes about 15 years of experience in software applications and Database design and development which contains about 8 years of experience in MSSQL Server.

We would like to thank the organizer of the event — Denis Reznik. We are looking forward to the next meetings!

Devart supports User Groups and provides sponsorship for different events. We are looking forward to your sponsorship requests! E-mail us a brief summary of your event or User Group meetup.


What is the fastest way to calculate the record COUNT?

Posted by on December 15th, 2015

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?

SELECT COUNT(*) FROM Sales.SalesOrderDetail
SELECT COUNT_BIG(*) FROM Sales.SalesOrderDetail

Read the rest of this entry »

Devart was the Silver Sponsor of SQL Saturday #426 at Lviv

Posted by on December 9th, 2015

Devart was a Silver sponsor of SQLSaturday #426 that was held on 5 Dec 2015 at Lviv, Ukraine.

We would like to thank the organizers: Sergey LunyakinAndrey Zrobok, and all speakers for the outstanding event! Our special thanks to Eugene Polonichko for the assistance :)

We highly appreciate the opportunity to share thoughts and ideas, discuss challenges as well as to present our software products to the professional community.

Devart supports User Groups and provides sponsorship for different events. We are looking forward to your sponsorship requests! E-mail us a brief summary of your event or User Group meetup.

1234 5678