This is the first article in a series of articles about In-Memory OLTP. It helps you to understand how the new Hekaton engine works internally. We will focus on details of in-memory optimized tables and indexes. This is the entry-level article, which means you do not need to be an SQL Server expert, however, you do need to have some basic knowledge about the traditional SQL Server engine. (more…)
These days, development teams start new projects from choosing a version control system. There are many benefits of source control systems in coordinating the efforts of the development team. It ensures a complete audit trail of all changes to the code and allows the team to reproduce any specific revision or build. Along with the programming code, databases can and should be placed under source control. In this article, we will have a look at how to deploy a database that is in the remote repository. (more…)
In this post, I’d like to take a brief look at the Query Performance Insight — SQL Azure tool which will help you to identify the most expensive queries in your database.
Query Performance Insights was announced in early October 2015. To understand what it is, let’s think about how do you usually learn that the database performance got down? Probably, you are receiving emails from your clients or it takes an hour to create a weekly report instead of a several minutes, or maybe, your application starts throwing exceptions. (more…)
To manage the data security that has been backed up by the file system as a database backup files, SQL Server provides the backup encryption feature. In this article, we will talk about encryption options available in SQL Server for database backups. We will have a closer look at the usage details, benefits, and recommended practices for encrypting SQL Server database backup during the backup process. (more…)
In this article, I would like to describe several new T-SQL features available in SQL Server 2016: DROP IF EXISTS, SESSION_CONTEXT, MAXDOP, FORMATMESSAGE, COMPRESS and DECOMPARESS, DATEDIFF_BIG, AT TIME ZONE, FOR JSON and other. (more…)
In this article, we will talk about SQL Server Checkpoints.
To enhance performance, SQL Server applies modifications to database pages in memory. Often, this memory is called the buffer cache or buffer pool. SQL Server does not flush these pages to disk after every change. Instead, the database engine does checkpoint operation on each database from time to time. The CHECKPOINT operation writes the dirty pages (current in-memory modified pages) and also writes details about the transaction log. (more…)
T-SQL provides two keywords that allow you to delete data from a table. They are DELETE and TRUNCATE. These are alike in what they are doing, however, they use vastly different methods. In this article, we discuss underlying mechanisms for these keywords. (more…)