Author: 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:

Hekaton

Hekaton is the code name of the new feature of In-Memory OLTP. It is a new database engine, fully integrated with SQL server and designed to enhance memory resident data and OLTP workloads. In simple words, with Hekaton we can store the entire table in memory.

Let’s list some of the benefits of this new feature:

• Memory-Optimized-Tables can be accessed using T-SQL like Disk-Based-Tables.
• Both of Memory-Optimized-Tables and Disk-Based-Tables can reference in the same query, and also we can update both types of tables by one transaction.
• Stored procedures that only reference Memory-Optimized-Tables can natively compile into machine code which results in improving performance.
• This new engine designed for a high level of session concurrency for OLTP transactions.

There are still some limitations for Memory-Optimized-Tables in SQL server 2014 which are:

• ALTER TABLE statement, SP_RENAME stored procedure, ALTER BUCKET_COUNT statement, and add\remove index outside statement of CREATE TABLE, all of these not supported by In-Memory table
• Some constraints not supported like (CHECK, FOREIGN KEY, UNIQUE)
• RANGE INDEXES and TRIGGERS not supported by In-Memory table
• REPLICATION, MIRRORING, and LINKED SERVERS are incompatible with Memory-Optimized-Tables.

To know more information, you can check SQL Server Support for In-Memory OLTP.

Memory-Optimized-Tables are appropriate for the following scenarios:

• A table has a high insertion rate of data from multiple concurrent sources
• A table cannot meet scale-up requirements for high performance of reading operations especially with periodic batch inserts and updates
• Intensive logic processing inside a stored procedure
• A database solution cannot achieve low latency business transaction

Let’s now go through the steps to create a Memory-Optimized-Table

 

For more information check the source article from HERE

introduction

To improve performance, it is common for DBAs to search in each aspect except analyzing storage subsystem performance even though in many times, issues are, in fact, caused by poor storage subsystem performance. Therefore, I want to give you some tools and recommendation that you can use it to prevent your storage subsystem from being a performance issue for you.

In this article, I will cover how to measure and analyze your storage subsystem performance and how to test your storage subsystem including

1. Main metrics for storage performance
2. Operating System Tools to measure storage performance
3. SQL Server Tools to measure storage performance
4. Using SQL Server to test storage performance

Main metrics for storage performance:

In this section I will introduce the three main metrics for the most storage performance issues as follows:

1. Latency
   Each IO request will take some time to complete this latency is measured in milliseconds (ms) and should be as low as possible
2. IOPS
   IOPS means IO operations per second, which means the amount of reading or write operations that could be done in one second. A certain amount of IO operations will also give a certain throughput of Megabytes each second, so these two are related
3. Throughputs
   The most common value from a disk manufacturer is how much throughput a certain disk can deliver. This number usually expressed in Megabytes / Second (MB/s), and it is simple to believe that this would be the most important factor

For More information please check the source article from HERE