Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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)

sys.tables

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

Column Name Values
durability  0 = SCHEMA_AND_DATA
1 = SCHEMA_ONLY
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.

SELECT name
    ,durability
    ,durability_desc
    ,is_memory_optimized
FROM sys.tables

image

 

sys.table_types

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
go
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)
) WITH (MEMORY_OPTIMIZED=ON)
GO

Now, look at catalog view.

select  name, is_memory_optimized 
from    sys.table_types

image

sys.indexes

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'
    ,type_desc
FROM sys.indexes
WHERE object_id IN (
        SELECT object_id
        FROM sys.tables
        WHERE is_memory_optimized = 1
        )

image

sys.index_columns

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.

sys.data_spaces

New values available in type column as below.

Column Name Values
type  FX = Memory-optimized tables filegroup
type_desc  MEMORY_OPTIMIZED_DATA_FILEGROUP

 

use SQLSeverHelp_IMO
go
select  * 
from    sys.data_spaces

image

 

sys.sql_modules

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), 
    uses_native_compilation  
from  sys.sql_modules 

image

 

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

 

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Leave a Reply

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

    WordPress.com Logo

    You are commenting using your WordPress.com 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 )

    Google+ photo

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

    Connecting to %s

     
    %d bloggers like this: