Help: SQL Server

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.

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

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

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
        )

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.

use SQLSeverHelp_IMO
go
select  * 
from    sys.data_spaces

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 

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