Skip to main content

SQL Server In Memory OLTP - Memory Optimized Table

The memory optimized tables were first introduced with SQL 2014. The basic concept behind the introduction of memory optimized tables was to equip the sql server with 100x processing capabilities for some specific requirements (tables).
Before recommending memory optimized table you must understand about the memory optimized tables and how the data is stored, what type of indexes can be created on IM (In Memory) tables. There are basically two types of IM tables i.e schema durable and schema and data durable.

To learn more about In Memory OLTP you can go to the below MSDN Link.

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables

I have done few test with the processing capabilities of the IM tables and compared the same with disk based tables which are mentioned below.

I had loaded around 18000000 rows and tried to query data against that. to return 10000 rows based on some parameter condition the disk based table took approximately 5520 millisecond and IM table took 853 millisecond.

Now lets come to pros and cons (SCHEMA_DATA durable table)
  • We see 10-100x performance improvement while retrieving data using natively compiled procedures.
  • Disk based table occupied around 8GB data on drive and for same amount of data around 27GB of RMA was used.
  • You can see the retrieval performance through natively optimized code only.
  • DDL operation is very time and memory intensive once data is added to the table, so carefully use it.
  • You might need to create resource pool in case if you face any out of memory exception while creating/loading a large table.
  • If you restart the SQL Server service then for sometime your DB might go to recovery mode till all the IM table data loaded back to RAM.

Please read through the query processing guideline


Also read about indexes and buckets



Comments

Post a Comment

Popular posts from this blog

DataZen Syllabus

INTRODUCTION TO DATAZEN PRODUCT ELEMENTS ARCHITECTURE DATAZEN ENTERPRISE SERVER INTRODUCTION SERVER ARCHITECTURE INSTALLATION SECURITY CONTROL PANEL WEB VIEWER SERVER ADMINISTRATION CREATING AND PUBLISHING DASHBOARDS CONNECTING TO DATASOURCES DESIGNER CONFIGURING NAVIGATOR CONFIGURING VISUALIZATION  PUBLISHING DASHBOARD WORKING WITH MAP  WORKING WITH DRILL THROUGH DASHBOARDS
67 comments
Read more

PowerBI Interview Questions and Answers

Power BI Interview Questions – General Questions 1). What is self-service business intelligence? Ans: Self-Service Business Intelligence (SSBI) is an approach to data analytics that enables business users to filter, segment, and, analyse their data, without the in-depth technical knowledge in statistical analysis, business intelligence (BI). SSBI has made it easier for end users to access their data and create various visuals to get better business insights. Anybody who has basic understanding of the data can create reports to build intuitive and shareable dashboards. 2). What are the parts of Microsoft self-service business intelligence solution? Ans: Microsoft has two parts for Self-Service BI  Excel BI Toolkit – It allows users to create interactive report by importing data from different sources and model data according to report requirement.  Power BI – It is the online solution that enables you to share the interactive reports and queries that you have created using ...
7 comments
Read more

MS BI Syllabus

Microsoft Business Intelligence Course Syllabus SSRS – SQL Server Reporting Services  Getting Started 1. Understanding Reporting (Authoring,Management,Delivery) 2. Installing Reporting (Native Mode, SharePoint Integration mode) 3. Building your first report  Authoring Reports 1. Developing Basic Reports (RDL,wizard,designer,datasource,dataset,formatting) 2. Working with expressions (expression to calculate value, Agg functions, exp for objects) 3. Organizing Data (Data Regions, Table, Matrix, Chart, List) 4. Advance Report (Parameter, drill down, drill through, links, 5. Report Model (Data Source, Data Source View, Model , Report Builder 3.0)  Managing Report ( Report Manager) 1. Managing Content (deploying report, folders, linked reports, datasources, value etc) 2. Managing Security (Item Level , Site navigation, localhost – sql) 3. Managing Server Config (Config Manager, Report Manager, Report Server DB)  Delivering Report 1. Accessing Report (Viewing...
30 comments
Read more