Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,706,032 hits
  • Select GETDATE()

    February 2014
    M T W T F S S
    « Jan   Mar »

A-Z of In-Memory OLTP : Enhancement in Catalog Views

Posted by blakhani on February 20, 2014

During my interaction with customers, catalog views and DMV words are used interchangeably. In reality, there is a difference between the two. Till SQL Server 2000, there were system table like sysdatabases, sysobjects etc. SQL 2005 and above the new concept of catalog view was introduced which is replacement of system tables. It is the way to see the catalog metadata of SQL Server. The data showed in the catalog view is physically stored on the disk. On the other hand, DMV is a dynamic management view. It’s similar to sysprocesses which we have used in SQL 2000. As soon as query is fired, the data is read from memory structures and showed in row-column format.

In this blog, I’ll try to show the changes to existing catalog view to support In-Memory OLTP (code name Hekaton)


There are few new columns introduced in sys.tables to get details about In-Memory Tables.

Column Name Values
durability  0 = SCHEMA_AND_DATA
durability_desc  Description of value in durability Column
is_memory_optimized  1 – Yes, 0 – No


In my database, I have created two tables; One disk based and one in-memory optimized.

FROM sys.tables




There is a new column is_memory_optimized is introduced. I would create a type using below T-SQL taken from Books online

Use SQLSeverHelp_IMO
CREATE TYPE dbo.SalesOrderDetailsType AS TABLE
       so_id int NOT NULL,
       lineitem_id int NOT NULL,
       product_id int NOT NULL,
       unitprice money NOT NULL,

       PRIMARY KEY NONCLUSTERED (so_id,lineitem_id)

Now, look at catalog view.

select  name, is_memory_optimized 
from    sys.table_types



With the introduction of Hash Indexes, there is new value for type_desc column as shown below.

 SELECT object_name(object_id) TableName
    ,name 'IndexName'
FROM sys.indexes
WHERE object_id IN (
        SELECT object_id
        FROM sys.tables
        WHERE is_memory_optimized = 1



No Change in the columns but it’s important to note that since there is no sorting order in HASH Indexes, the column is_descending_key is shown as 0 which is ignorable.


New values available in type column as below.

Column Name Values
type  FX = Memory-optimized tables filegroup


use SQLSeverHelp_IMO
select  * 
from    sys.data_spaces




A new column uses_native_compilation has been added to identify if it’s a natively compiled procedure. Same is true for sys.all_sql_modules as well. We already have a proc in the database.

Select object_name(object_id), 
from  sys.sql_modules 



In next post, we would explore DMVs related to In-Memory OLTP.


  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Google photo

    You are commenting using your Google account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    %d bloggers like this: