Windows Azure VM with In-Memory OLTP

Boost your Windows Azure VMs

With the latest SQL Server 2014 CTP2, you can now drastically boost OLTP workloads performance in Windows Azure VMs. Transition to In-Memory OLTP is not as simple as tossing a coin. Carefully evaluate application scenario and see whether it is required or not. This blog post is a guide on how to create a Windows Azure VM with In-Memory OLTP.

Recommendations

For SQL Server 2014 CTP 2 with In-Memory OLTP on a Windows Azure Virtual Machine the following scenarios are recommended:

• Easily gain full access to In-Memory OLTP functionalities without a large capital investment since Windows Azure VMs comes with on-demand provisioning at low cost. You can deploy In-Memory OLTP on your local server once you understand it together with its limitations or keep using the VM if it fits your business needs.
• SQL Server In-Memory OLTP offers non-durable tables, which are perfect for transient data irrespective of the transaction read/write mix. Non-durable tables could provide remarkable boosts to performance for non-persistent workload since it completely eliminate I/O operations. If required use Delayed Durability which commits transactions but do not immediately write the log records to disk. This helps lightening log I/O pressure by letting larger and less frequent log writes to disk. Due to new algorithm procedures and optimized data structures for memory-resident data In-Memory OLTP can deliver substantial performance scale in read-mostly and read-only scenarios. Also, native compilation for stored procedures can significantly boost CPU efficiency and throughput.
• In-Memory OLTP can provide grate assistances for assignments or jobs with full durability requirements, which are not under pressure from, log I/O latency or throughput. Since In-Memory OLTP uses an optimistic concurrency model that removes page contention, it can provide substantial improvements to concurrency for the workload if it has contention problems. Furthermore, native compilation can boost the speed of a stored procedure with comprehensive business logic processing.

Note: If your workload is under pressure from log I/O throughput or have long log I/O latency, and require strict durability of data at the same time, In-Memory OLTP on Windows Azure Virtual Machine will not ease or serve to these problems.

Choosing the Right Windows Azure VM Size

The selection of VM sizes is critical for any workload running in the IaaS space. Windows Azure provides a selection of virtual machine sizes for a variety of purposes. In-Memory OLTP is devised to aid extreme high session concurrency and the nature of memory-resident data requires plenty memory space, Microsoft recommend the following Windows Azure Virtual Machine sizes for adopting In-Memory OLTP:

The exact instance size that you shall choose will depend on:

1. the case you wish to run and
2. the size of data you wish to make memory-resident in SQL Server In-Memory OLTP.

We suggest to provision 100% more memory than the projected size of data in memory and leave necessary space for the buffer pool used by disk-based workloads on the same machine.

The considerations

To configure a Windows Azure VM for best performance in In-Memory OLTP, these are the key considerations:

• Employ a single storage account for all disks attached to the same VM.
• Put log files and data files on separate drives.
• Choose locate your memory-optimized file group on a different drive.
• Turn off disk host caching for the data and log drives incase it’s on. By default it is off in Windows Azure.
• Do not use the Temporary Disk (D: Drive) for log storage.
• Use a persistent Windows Azure Storage disk for your log storage needs.
• Do not use native OS striping for storing the log of an In-Memory OLTP-enabled database.

Hope this article was helpful and now you have better understanding of Windows Azure Virtual Machines with In-Memory OLTP. Feel free to comment below.

Start Azure Information Protection Plan 1 Free Trial

Check out our infrastructure management services