Home > In-MemoryOLTP > In-Memory OLTP –Part 5

In-Memory OLTP –Part 5

 

Create In-Memory database or use the exiting database to use In-Memory feature

 

Configure existing database for In-Memory OLTP .Add an In-Memory OLTP file group  and file to an existing database.

  • You do not need to enable filestream to create a memory-optimized filegroup. The mapping to filestream is done by the In-Memory OLTP engine.

 

Limitations of memory-optimized filegroup,

  • Once you create a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you will need to remove the memory-optimized filegroup.

  • You cannot drop a non-empty container or move data and delta file pairs to another container in the memory-optimized filegroup.

  • You cannot specify MAXSIZE for the container.

 

ALTER DATABASE AdventureWorks2014
ADD FILEGROUP INMOLTP_fg CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE AdventureWorks2014
ADD FILE (NAME='INMOLTP_fg', FILENAME='C:\SQLDatabase\DB\INMemory_OLTP\INMOLTP_fg')
  TO FILEGROUP INMOLTP_fg;
GO

 

Database In-Memory OLTP Status

 

SELECT   CASE  
	WHEN SERVERPROPERTY('IsXTPSupported')= 1 THEN ' Server supports In-Memory OLTP.'  
	WHEN SERVERPROPERTY('IsXTPSupported')= 0 THEN 'Server does not supports In-Memory OLTP.' 
	ELSE 'Input is not valid, an error, or not applicable.' END

 

image

 

Query Database file status

 

select s.name,g.name,g.type,g.type_desc,s.type,s.type_desc,s.physical_name,s.state_desc from 
sys.filegroups AS g
INNER JOIN sys.database_files AS s ON ((s.type = 2 or s.type = 0) 
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)

 

image 

 

Note: Optimized file group can be deleted if there is no Optimized file is associated to it. As you create the memory optimized file SQL Server will assign memory to it and system memory objects.

 

use AdventureWorks2014
go
select * from sys.dm_xtp_system_memory_consumers

 

image

Memory Optimized filegroup stores memory optimized data in the file system

 

image

 

Check the database-level memory consumers in the In-Memory OLTP database engine. The view returns a row for each memory consumer that the database engine uses.

select * from sys.dm_db_xtp_memory_consumers

image

 

Initial memory utilization : In-Memory Configuration

select  convert(char(10), object_name(object_id)) as Name, 
convert(char(10),memory_consumer_type_desc ) as memory_consumer_type_desc, 
object_id,index_id, allocated_bytes,  used_bytes 
from sys.dm_db_xtp_memory_consumers

 

image

Categories: In-MemoryOLTP
  1. No comments yet.
  1. No trackbacks yet.

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: