Overview of Memory-Optimized Table.

We all know, retrieving data from physical memory (AKA RAM) is faster than the data saved in physical disk (HDD). Memory-optimized tables save & retrieve data into/from physical memory rather than Hard-disk. This could be excellent feature to solve lot of use cases such as online game where only end results matters.

This feature was introduced with SQL version 2014 and continuous improvement are being done in consecutive releases. In lot of places, you would see this feature named as “Hekaton” OR “In-Memory optimization”.

Following a short demo may help to develop your understanding regarding Memory table & performance comparison with disk based table.

-- Create InMemoryTableDemo database. 
USE [master]
CREATE DATABASE [InMemoryTableDemo]
GO
-- Create Memory optimized File Group.
USE [master]
ALTER DATABASE [InMemoryTableDemo]
ADD FILEGROUP MemoryGroupFG CONTAINS MEMORY_OPTIMIZED_DATA
-- Add the container for file group. 
ALTER DATABASE [InMemoryTableDemo]
ADD FILE (NAME = AWMemFile, FILENAME = N'F:\FGData\FG1') TO FILEGROUP MemoryGroupFG
GO

In my following example, I have set ‘Durability’ to ‘schema_only’ which means only the schema will be recoverable in case of crash as the rows will remain in memory. The other option is ‘Schema_and_Data’ that protect table schema as well rows, the copy of data will also save in HDD.

-- Create Memory Based Table.
USE [InMemoryTableDemo]
CREATE TABLE TableInMemory
(
       ID INT CONSTRAINT [pk_TableInMemory_ID] PRIMARY KEY NONCLUSTERED IDENTITY (1,1), 
       EMPID INT,
       CharData VARCHAR(100)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )
GO

Let’s create a disk based table with same columns.

-- Create Disk Based Table.
USE [InMemoryTableDemo]
CREATE TABLE TableInDisk
(      
       ID INT CONSTRAINT [pk_TableInDisk_ID] PRIMARY KEY NONCLUSTERED IDENTITY (1,1), 
       EMPID INT, 
       CharData VARCHAR(100)
)
GO

Now we are inserting 100k rows and it takes only (approximately) 4 seconds to insert in memory based table whereas disk based table takes (approximately) 200 seconds.

-- Test 100,000 inserts into a Memory Based Table 
DECLARE @start DATETIME = GETDATE();
DECLARE @count INT = 1;
WHILE @count < 100000
BEGIN
  INSERT INTO TableInMemory VALUES 
       (@count, 'stuff');
        SET @count = @count + 1;
END
SELECT DATEDIFF(s, @start, GETDATE()) AS [Memory Insert]
GO

-- Test 100,000 inserts into a Disk Based Table
DECLARE @start DATETIME = GETDATE();
DECLARE @count INT = 1;
WHILE @count < 100000
BEGIN
  INSERT INTO TableInDisk
VALUES 
       (@count, 'stuff');
       SET @count = @count + 1;
END
SELECT DATEDIFF(s, @start, GETDATE()) AS [Memory Insert]
GO

first

Let’s test the retrieval of rows. Run following 3 queries together while enabling ‘Include actual execution plan’ OR Live query statistics‘.

-- Run following 3 queries together while enabling 'include actual execution plan' OR 'Live querystatistics' 
USE [InMemoryTableDemo]
SELECT * FROM TableInDisk
SELECT * FROM TableInMemory
GO

Comparsion.jpg

You would notice Memory table takes (approximately) 16 % resources as compare to disk based table that takes 84 % of resources. Additionally storage & I/O Cost columns will give the details of they type of query.

More details on Memory optimized table.

Advertisements

#hekaton, #in-memory-optimization, #sql2016, #storage

How to create ISCSI shared disk in windows server for ESX VSphere environment.

This my first blog for ESX/VSphere where the requirement was to create ISCSI data store for HA & DRS (High Availability & Distributed Resource Scheduler).  We created storage from windows server instead of having a dedicated storage appliance/VM. The purpose of such configuration is only for learning & testing the HA/DRS activity.

This configuration is designed on windows 2012 R2 with ESX 6.0.

On windows server, install ‘File and ISCSI services’ and make sure ‘ISCSI Target server’ is selected.

1.jpg

Once the role is installed then select ‘File and storage services’ feature and then ‘ISCSI’.

Right click on empty space and select ‘New ISCSI Virtual Disk’ as shown in above example. In my screenshot one of the volume is created and I am creating a new one.

2.jpg

Now select the partition where you wish to place the Virtual Disk.

3.jpg

Give the appropriate Name and Description and confirm the path of SCSI target.

4.jpg

Give the appropriate size & select type of disk as mentioned below. In my example I have selected ‘Dynamically expending’ as I don’t need to worry about space.

5.jpg

You need to create target that will be the list of ESX servers participating in HA/DRS and access VHD using ISCSI protocol.

6.jpg

In my example, I have used DNS name of ESX servers as initiator but it can also be access using IQN, IP address OR MAC address.

16.jpg

In following screenshot, there are two ESX servers are added but you have even more.

8.jpg

Select type of authentication, I am leaving it blank to avoid confusion.

9.jpg

In next page, you can confirm the setting you have selected and the result of different section will be available for you.10.jpg

Now the shared ISCSI disk is ready, you can add this in ESX server using VSphere console. Select ESX server \ Manage \Storage \ Storage Adaptor \Target \ Add Target.

11.jpg

Rescan storage so all newly attache drives are visible.

12.jpg

Now you should be able to see the path of all available SCSI share disk.

13.jpg

SCSI disk will also be available as storage devices.

14.jpg

In your windows server, you would notice the target status as ‘connected’.

15.jpg

#disaster-recovery, #esx, #scsi, #storage, #vmware

RAID concepts and configuration in simple words for Windows Admin.

Continue reading

#raid, #sql, #storage, #windows