In-Memory OLTP (In-Memory Optimization) Frequently Asked Question and Answers (FAQ) in Interviews Part 1
In-Memory OLTP was introduced in SQL Server 2014 and the goal of it is significantly improving the OLTP database applications performance. In-Memory OLTP is a memory-optimized database engine code which has been integrated with the SQL Server engine code, thus making SQL Server competent to improve the OTLP applications performance. It is very common in interviews to ask questions about new and popular features of SQL Server like AlwaysON Availability Groups, In-Memory OLTP, etc. Proving strong knowledge on new features will showcase the interest you have in updating your skills and adapting quickly, thus provides better possibilities of clearing the interview. Not only from an interview perspective, it is important as a DBA to update our skills quickly, otherwise there wont be much market value to old skills, which become old very quickly. If a client asked a question, we should be able to atleast know the very basics, rather than proving ourselves outdated.
Below are some of the common questions which may be asked in interviews regarding In-Memory OLTP.
In which version of SQL Server was In-Memory OLTP introduced? or What is the top feature in SQL Server 2014
In-Memory OLTP introduced was introduced with SQL Server 2014 version.
Which SQL Server 2014 Edition include In-Memory OLTP?
In-Memory OLTP is available in SQL Server 2014 Enterprise Edition, Enterprise Evaluation and Developer Edition.
Does In-Memory OLTP requires applications to be modified?
Yes, the table schema has to be modified and changes to the application are required as well.
Can we create the In-Memory Tables in default FileGroup?
No, In-Memory Tables cannot be stored in default or regular filegroups, we need to create separate filegroup and files to store In-Memory Tables.
Can we create multiple in-memory filegroups and files?
We can create only one filegroup for storing In-Memory tables, but the filegroup can have multiple files and can be placed on different drives.
Can my database store regular tables as well as In-Memory tables?
Yes, we can have both regular and in-memory tables in same database.
Will my data persist even after restarting my SQL Server?
If we create In-Memory table using option DURABILITY = SCHEMA_AND_DATA, then we will have data retained even after restart of SQL Server. If SCHEMA_ONLY option is specified, then we will loose all the data.
Can we create clustered index on In-Memory Table
No, Clustered indexes are not supported on In-Memory Tables.
Can we create Non-Clustered Indexes on In-Memory Tables?
Yes, but you need to create the Non-Clustered Indexes during the time of creation of the In-Memory Table itself, we cannot add new indexes, alter existing indexes or drop any indexes on In-Memory Tables, so need to plan the indexes well before hand.
Does Non-Clustered Indexes on In-Memory Tables take storage on the disk?
No, Non-Clustered Indexes on In-Memory Tables are only in memory, and they are not persisted on the disk. When SQL Server is stopped or Database is taken offline, the indexes are lost. Also, any operations on these indexes are not logged to transaction log file, as no recovery is required for the indexes. During startup of database, indexes are populated into the memory as part of recovery process.
Can we add covering indexes for In-Memory tables?
No, Memory-optimized indexes are inherently covering, meaning all columns are virtually included in the index itself, thus bookmark lookups are not required for memory-optimized tables.
If we cannot alter the indexes, then how do we reduce the fragmentation for In-Memory indexes?
Indexes do not persist on physical storage, they are recreated every time SQL Server restarts or databases is brought online, so there wont be any index fragmentation in in-memory indexes.
What are different kinds of Indexes supported for In-Memory tables?
We can create two kinds of in-memory indexes for In-Memory tables, which are Nonclustered Hash Index and Nonclustered Range Index.
Nonclustered Hash Index is used for queries which are going to perform equi-joins
Nonclustered Range Index is used for queries which performs range scans and ordered scans
What happens when we create in-memory table?
When we run the DDL for creating the In-Memory table, it will be converted into a in-memory table DDL and is loaded into SQL Server memory. We can find what all In-Memory tables are created and loaded in memory by running below query
Can I use regular T-SQL queries and Stored Procedures to access In-Memory tables?
Yes, we can use regular T-SQL queries and Stored Procedures to access In-Memory tables. Also, In-memory OLTP engine introduces a new type of stored procedure called natively compiled stored procedure, which can oly access In-Memory tables. When we create the natively compiled stored procedures, they query plan gets created during the compile time itself. These stored procedures are compiled by Microsoft’s Visual C/C++ compiler to native code and provides maximum performance benefits and is the preferred way of accessing In-Memory tables. At this time, there is limited T-SQL support for these stored procedures. When this SP gets compiled, DLL will be created and loaded into SQL Server Memory. We can verify the same by running above mentioned SQL query.
How does In-Memory tables provide high performance?
In-Memory tables reside completely in memory, so the access to the in-memory data is always very fast, as there is no requirement of physical IO from disk to memory. Using natively compiled stored procedures provide additional performance boost as they are per-compiled and no permission checks need to be performed later, as it is done during the time of compilation itself.
Can we create database to hold In-Memory tables using SSMS GUI?
Yes, SQL Server 2014 GUI supports creation of database with In-Memory filegroup and filestream data file for In-Memory usage. But we cannot create In-Memory table using GUI, we need to use T-SQL commands for that.
How do I know if a Table or Stored Procedure are candidates to be migrated as In-Memory OLTP?
Transaction performance reports tool tell us which tables in our database will benefit if ported into In-Memory OLTP. Transaction performance collector tool is available in SQL Server 2014 Management Studio, which helps us to evaluate, if In-Memory OLTP will improve our database application’s performance. Transaction performance analysis report also tells how much work we must have to do in order to enable In-Memory OLTP in your application.
How do you migrate regular table into In-Memory table?
We can use the memory optimization advisor tool which helps us in migrate the disk-based database table into In-Memory OLTP.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.