In-Memory OLTP

SQL Server TempDB database and Latch Contention

May 13, 2020 by Esat Erkec

In this article, we will learn latch contention issues that we might experience in the SQL Server TempDB database. We will also discuss reasons and the solution method of these latch contention issues. Especially, we will mention the Memory-Optimized TempDB Metadata feature that was introduced with the SQL Server 2019.

Memory-Optimized Table Variables in SQL Server

May 6, 2020 by Esat Erkec

This article will cover the usage details and performance advantages of the memory-optimized table variables.

Table-Valued Parameters in SQL Server

April 6, 2020 by Esat Erkec

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.

A quick overview of In-Memory OLTP in SQL Server

February 12, 2020 by Prashanth Jayaram

This is in continuation of the previous articles How to monitor internal data structures of SQL Server In-Memory database objects and SQL Server In-Memory database internal memory structure monitoring.

How to monitor internal data structures of SQL Server In-Memory database objects

February 25, 2019 by Prashanth Jayaram

This is the continuation of the previous article SQL Server In-Memory database internal memory structure monitoring. In this article we are going to dissect the details of other components that are available to validate the SQL Server In-Memory objects.

SQL Server In-Memory database internal memory structure monitoring

January 30, 2019 by Prashanth Jayaram

We can use a variety of available options to keep systems performing at their very best. The SQL Server In-Memory OLTP technological advancement is a great innovation to relieve tempdb contentions. This feature is available with an additional advantage to monitor systems without incurring stress on the database servers.

In-Memory OLTP Enhancements in SQL Server 2016

August 22, 2017 by Ahmad Yaseen

SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures into memory and compile the stored procedures into native x86 code. As a result, you can easily query these database objects directly from memory with the best performance and the least possible data access latency. In addition to that, the SQL Server Engine will no longer use the old latching and locking mechanism to control the data access concurrency. Instead, a high performance row versioning mechanism will be used to control the concurrency. This optimistic concurrency mechanism is 5 times to 20 times faster than the normal disk-based processing, due to reading the data from the memory directly.

$E:\Desktop\Drawing1.jpg$

Hybrid Cloud and Hekaton Features in SQL Server 2014

February 28, 2017 by Mustafa EL-Masry

Introduction

Microsoft SQL Server 2014 is considered to be the first version that supports Hybrid Cloud by adding a lot of exciting new features.

In this article, I will cover some of the top new features in these main points including Hekaton and Hybrid Cloud enhancements:

Using Memory-Optimized Tables to Replace SQL Temp Tables and Table Variables

January 24, 2017 by Ahmad Yaseen

TempDB usage can be considered as a performance bottleneck for workloads that use SQL temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance. SCHEMA_ONLY Memory-Optimized table and the Memory-optimized Table Variable are stored only in the memory with no related component in the disk. It involves no IO activity or TempDB utilization. It can also participate in the transactions without the need to log the transactions.

Deep dive into Nonclustered indexes for In-Memory OLTP tables

December 2, 2016 by Kaloyan Kosev

With the introduction of Microsoft’s new In-Memory OLTP engine* (code name Hekaton) a new type of nonclustered index was introduced to help you search the in-memory tables based on a range of values. Although the name is similar it does differ from the one we have in the traditional disk tables.

Deep dive into Hash indexes for In-Memory OLTP tables

November 30, 2016 by Kaloyan Kosev

With the introduction of Microsoft’s new In-Memory OLTP engine (code name Hekaton) the familiar B-Tree indexes were not always the optimal solution. The target of the Hekaton project was to achieve 100 (hundred) times faster OLTP processing, and to help this a new index was introduced – the hash index.

In-Memory OLTP Series – Data migration guideline process on SQL Server 2016

January 29, 2016 by Luan Moreno M. Maciel

On the last article about the best modes to move the disk-based tables to using the In-Memory feature we covered all the aspects and styles available on SQL Server 2014. Continuing on the migration process now we’re going to look at some of the new enhancements of makes SQL Server 2016

In-Memory OLTP Series – Data migration guideline process on SQL Server 2014

January 28, 2016 by Luan Moreno M. Maciel

In this article we will review migration from disk-based tables to in-memory optimized tables. This article assumes that you already understand the pros and cons about In-Memory Technology, for more articles about this, please refer here.

There are some options available on SQL Server 2014 and SQL Server 2016 that will help you to identity, discover and track the tables and stored procedures that can be good candidates to be ported to memory. Furthermore it’s possible to check if the table meets to all the pre-requisites established. Here is the list of the features that we will take a look into.

AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4

October 13, 2015 by Murilo Miranda

Here we are with the last part of this series of articles! In this article we are going to see two curiosities:

How does in-memory OLTP work with Availability Group?
Can I migrate from Log Shipping or Database Mirroring to Availability Groups?

So, let’s start!

In-Memory OLTP – The faster is now simpler!

May 29, 2015 by Murilo Miranda

In-memory OLTP is a revolutionary tool introduced on SQL Server 2014. On SQL Server 2016 this will be even better, with a broader surface of supported tools. Check this article to know the news.

In-Memory OLTP Series – Table Creation & Types

March 13, 2015 by Luan Moreno M. Maciel

In sequence of the first article of the In-Memory OLTP Series that explained the main pillars, database creation and a briefly overview of the checkpoint files, now we will take a look into the table creation and data types allowed in the In-Memory OLTP feature in SQL Server 2014.

In-Memory OLTP – Three key points to entertain your watchdog – Bucket Count

February 24, 2015 by Murilo Miranda

When creating a hash index in a memory optimized table we need to define a value for the bucket count. Think twice before set a random value, you may pay the price later… On this article we will explain the effects of a bad defined bucket count and how to monitor it.

In-Memory OLTP – Three key points to entertain your watchdog – Checkpoint Files

February 5, 2015 by Murilo Miranda

In sequence of the first article about the server memory importance, we will check another In-Memory OLTP key point: The Checkpoint files. Check what wrong can happen, and how to avoid this!

In-Memory OLTP – Three key points to entertain your watchdog

February 4, 2015 by Murilo Miranda

With the introduction of the in-memory technology, we need to think about what are the new challenges to keep the service up and running. In this article I will explore one of the three key points to monitor in order to maintain your SQL Server healthy.

Last week I spoke at my local SQL Server user group, in Lisbon. The session was nice and the good feedback received encouraged me to write about this and share information that is not too spread over the internet (so far…): Monitor environments using In-Memory OLTP.

In-Memory OLTP Series – Introduction

January 30, 2015 by Luan Moreno M. Maciel

Introduced on SQL Server 2014, the new brand feature In-Memory OLTP a.k.a “Hekaton” is a Main-Memory Database Engine. Developed by Microsoft Research & Paul Larson (Database Research Group at MSFT) this feature have the ability to manage and held tables entirely In-Memory. In this Series we will pass through for all the new concepts and components that makes this New Engine the most excited acquisition of the new SQL Server Version (120).

Buffer Pool Extension (BPE) – In-Memory OLTP: The Memory Challenge

December 24, 2014 by Murilo Miranda

In continuation of the previous post “Buffer Pool Extension (BPE) – Introduction to the Buffer Pool”, we will keep diving into the Buffer Pool Extension, this time, talking about another important factor behind this feature – the memory. No, we will not review how the memory works literally, we are talking about the great SQL Server 2014 new feature: The In-Memory OLTP, or the known HEKATON.