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.
|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
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
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 )
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.
|type||FX = Memory-optimized tables filegroup|
use SQLSeverHelp_IMO go 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), uses_native_compilation from sys.sql_modules
In next post, we would explore DMVs related to In-Memory OLTP.