DIR Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
HTML https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
DIR Return to: General
*****************************************************
#Post#: 51--------------------------------------------------
SQL Server 2014 In-Memory OLTP Dynamic Management Views
By: srinivasma_exceldbp Date: April 16, 2014, 4:45 am
---------------------------------------------------------
SQL Server 2014 In-Memory OLTP Dynamic Management Views
By now you have probably heard that when Microsoft rolls out
project Hekaton with SQL Server 2014 they will be calling it
In-Memory OLTP. This
new feature allows you to place SQL Server tables in memory. As
you start considering moving disk based tables to memory based
tables it will be important to understand how this affects
memory and other resources on your SQL Server instance. To help
you better understand the inner workings of the In-Memory OLTP
tables and the In-Memory OLTP engine, Microsoft has also
released a number of new In-Memory OLTP specific Dynamic
Management Views (DMV’s). This is a quick primer of all these
new DMV’s that can help you better understand and manage your
In-Memory OLTP tables and your Instance that support In-Memory
OLTP tables.
sys.dm_db_xtp_checkpoint_stats
This DMV will return information about In-Memory OLTP checkpoint
operations in the current database. By using this DMV you can
you explore the log statistics since SQL Server started up for
your databases that contain In-Memory OLTP tables. With this
DMV you can determine stats related to log blocks written, wait
statistics, LSN information, and more. If this DMV is run
against a database that doesn’t contain any In-Memory OLTP
tables then it will return no data.
sys.dm_db_xtp_checkpoint_files
This DMV shows information about In-Memory checkpoint files.
With this DMV you can determine information about the DATA and
DELTA files associated with your In-Memory tables. Using this
DMV you can find out information that can be used to determine
things such as:
•
tables
•
•
occupying
sys.dm_db_xtp_gc_cycle_stats
This DMV shows ring buffer output of garbage collection cycles
for your In-Memory OLTP database. You can use this DMV to
determine how often the garbage collection process runs against
your In-Memory OLTP database. When run against a database that
does not support In-Memory OLTP object it returns an empty set.
sys.dm_db_xtp_hash_index_stats
The sys.dm_db_xtp_hash_index_stats DMV allows you to review the
statistics associated with HASH indexes that you created on your
In-Memory OLTP tables. By using this DMV you can determine how
many buckets are associated with your HASH indexes, how many
empty buckets a HASH index has as well as the maximum number of
rows chained together in a single bucket, and the average number
of rows chained together. You can use this DMV to determine
how well you have set the BUCKET_COUNT option when you created
your HASH index.
sys.dm_db_xtp_index_stats
This DMV shows statistics related to In-Memory OLTP objects.
With this DMV you can find out things like:
•
•
•
Just like other DMV’s, the statistics shown by this DMV are
since the index was created or the last time SQL Server was
started.
sys.dm_db_xtp_memory_consumers
This DMV reports the amount of memory allocated and used based
on memory_consumer_id’s. With this view you can identify the
amount of allocated verses used memory for a given database
object.
sys.dm_db_xtp_merge_requests
By using this DMV you can track merge requests. Merge
operations are performed in order to manage the checkpoint file
pairs (Data and Delta files). With this DMV you can see the
status of the merge requests, as well as identify the source and
target files of the merge operations.
sys.dm_db_xtp_table_memory_stats
This DMV will show you the amount of memory used by your
In-Memory OLTP tables. This DMV exposes the memory
information for both user and system tables. By using this DMV
you can see the amount of space allocated, and used for both row
data and index data.
Here is a sample query that will return the amount of space
allocated and used for In-Memory OLTP objects in a database.
SELECT OBJECT_NAME(object_id) ObjectName,
Object_Id,
SUM( memory_allocated_for_indexes_kb +
memory_allocated_for_table_kb) AS memoryallocated_object_in_kb,
SUM( memory_used_by_indexes_kb + memory_used_by_table_kb) AS
memoryused_object_in_kb
FROM sys.dm_db_xtp_table_memory_stats
GROUP by object_id;
Note when you run this DMV if the ObjectName is NULL and the
object_id has a negative value then those rows of output
represents a system object.
sys.dm_db_xtp_transactions
The sys.dm_db_xtp_transactions DMV shows the active transactions
that are being processed by the In-Memory OLTP database engine.
With this DMV you can determine which sessions are processing
transactions against In-Memory OLTP tables and what the state of
these transactions is.
sys.dm_xtp_gc_queue_stats
This DMV returns information about each garbage collection
queue. By using this DMV you can find out the number of garbage
collection work items that have been en-queued and de-queued
since your SQL Server instance has started. Additionally you
can find out how many work items are currently in the garbage
collection queue and the maximum number of items that have ever
been queued
sys.dm_xtp_gc_stats
By using the sys.dm_xtp_gc_stats DMV you can view statistics
related the current behavior of the garbage collection process.
Here is a partial list of some of statistics you can return:
•
subsystem since your instance of SQL Server has started.
•
the first row in the hash bucket.
•
the first row in the hash bucket that have been removed.
sys.dm_xtp_system_memory_consumers
This DMV reports system level memory consumers for In-Memory
OLTP. Using this DMV you can determine things like, the type of
memory consumer (lookaside, varheap, pgpool, or aggregated) and
the number of bytes allocated and used by a consumer.
sys.dm_xtp_transaction_stats
This DMV provides rolled up statistics since a SQL Server
instance has started for transactions that have processed
against In-Memory OLTP tables. Here is a partial list of the
statistics you can return with this DMV:
•
In-Memory OLTP engine
•
•
•
*****************************************************