From BOL A resource pool represents a subset of physical resources that can be governed. By default, SQL Server databases are bound to and consume the resources of the default resource pool. To protect SQL Server from having its resources consumed by one or more memory-optimized tables, and to prevent other memory users from consuming […]

Why the xtp transaction do not kill or rollback when a long running process is stopped? A long running transaction inserting millions of records in a In-Memory OLTP Table  is stopped but the spid remains in running state, it do not kill or rollback. Even you try to kill the session it do not actually […]

  Indexes are used as entry points for memory-optimized tables.Data in memory is quickly read by index to locate in-memory data. [From BOL] A hash index consists of a collection of buckets organized in an array. A hash function maps index keys to corresponding buckets in the hash index. The following figure shows three index […]

Monitor XTP Transaction Couple of dmv’s where provided to monitor In-Memory transactions GO SELECT *  FROM sys.dm_db_xtp_transactions GO SELECT *  FROM sys.dm_xtp_transaction_recent_rows GO SELECT * FROM  sys.dm_xtp_transaction_stats GO   sys.dm_db_xtp_transactions : Reports the active transactions in the In-Memory OLTP database engine. The result of this transaction will have below possible values. 0 – IN PROGRESS […]

Garbage Collection of Checkpoint Files   What is Garbage Collection and how it works?   From:BOL   A Data row which has been deleted by a transaction that is no longer active.a stale row is considered or is eligible for garbage collection.The deleted transactions are written in DELTA files.   Non-blocking. Garbage collection is distributed […]

Merging of Checkpoint Files The set of files involved in a checkpoint grows with each check-point. However the active content of a data file decreases as more and more of its versions are marked as deleted in the corresponding delta file. Since the recovery process will read the contents of all data and delta files […]

How is my Data written? The data in memory-optimised tables is stored as free-form data rows that are linked through one or more in-memory indexes, in memory. There are no page structures for data rows, such as those used for disk-based tables. When the application is ready to commit the transaction, the In-Memory OLTP generates […]

Where is my data? Cont… So, As we know the data is saved in checkpoint (data)files , In this post we will go over monitoring the checkpoint files and how data is really saved. The checkpoint files were created at the location which was provided at the time of adding MEMORY_OPTIMIZED DATA file.     […]

Accessing IN-MEMORY OLTP Tables   In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile memory-optimized tables. Native compilation allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored […]

Table Types In-Memory   In-Memory table are similar to disk-based tables with few enhancement for In-Memory OLTP tables.To create a memory optimized table you need to add MEMORY_OPTIMIZED=ON clause with durability mode options (SCHEMA_ONLY and SCHEMA_AND_DATA) The DURABILITY argument is not applicable to table types, which is a type from which table variables or table-valued […]

  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. […]

Memory Optimization Advisor Transaction performance reports tool informs you about which tables in your database will benefit if ported to use In-Memory OLTP. After you identify a table that you would like to port to use In-Memory OLTP, you can use the memory optimization advisor to help you migrate the disk-based database table to In-Memory […]

Reviewing the AMR Tool Reports   AMR tool is to provide a easy way and single glance view to the tables or stored procedures which can be leveraged for In-Memory OLTP. No matter how well we understand the database workload but its not easy to identify which object to move into this efficient engine. AMR […]

After the MDW setup is complete you will see couple of jobs to collect and upload data were created. The Collection jobs where set to start at start-up and will continuously run.The upload jobs are scheduled to run every 15 mins which uploads the data in MDW database . You can collect and upload the […]

The Subject is introduced with SQL Server 2014,use of In-Memory can significantly improve the Database and Application performance.In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.   To use In-Memory OLTP, you define a heavily accessed table as memory optimized. Memory-optimized-tables are fully transactional, durable, and are accessed […]