Archive

Archive for July, 2015

In-Memory OLTP – Part 12

July 14, 2015 Leave a comment

 

Monitor XTP Transaction

Couple of dmv’s where provided to monitor In-Memory transactions

sys.dm_db_xtp_transactions

sys.dm_xtp_transaction_recent_rows

sys.dm_xtp_transaction_stats

 

sys.dm_db_xtp_transactions : Reports the active transactions in the In-Memory OLTP database engine.

 

image

The result of this transaction will have below possible values.

0 – IN PROGRESS

1 – SUCCESS

2 – ERROR

3 – COMMIT DEPENDENCY

4 – VALIDATION FAILED (RR)

5 – VALIDATION FAILED (SR)

6 – ROLLBACK

 

sys.dm_xtp_transaction_stats:Reports statistics about transactions that have run since the server started.

 

image

Categories: In-MemoryOLTP

In-Memory OLTP –Part 11

July 8, 2015 Leave a comment

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 over time with minimal impact on the workload.

  • Cooperative. User transactions participate in garbage collection with main garbage-collection thread.

  • Efficient. User transactions delink stale rows in the access path (the index) being used. This reduces the work required when the row is finally removed.

  • Responsive. Memory pressure leads to aggressive garbage collection.

  • Scalable. After commit, user transactions do part of the work of garbage collection. The more transaction activity, the more the transactions delink stale rows.

Garbage collection is controlled by the main garbage collection thread.The main garbage collection thread runs every 1 min, or when the number of committed transactions exceeds an internal threshold.The task of the garbage collector is to:

 

After a user transaction commits, it identifies all queued items associated with the scheduler it ran on and then releases the memory. If the garbage collection queue on the scheduler is empty, it searches for any non-empty queue in the current NUMA node. If there is low transactional activity and there is memory pressure, the main garbage-collection thread can access garbage collect rows from any queue. If there is no transactional activity after (for example) deleting a large number of rows and there is no memory pressure, the deleted rows will not be garbage collected until the transactional activity resumes or there is memory pressure.

 

Below are full steps involved to show basically garbage collection works

 

Add In-Memory OLTP File group with 2 containers (file stream files), the files will distributed in two containers, the first container will be used for DATA and second container will be used for DELTA

 

image

Check the no of files created.I have 16 GB Memory with one Physical CPU , dual core  with 4 logical processors.

 

select state,file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count
from sys.dm_db_xtp_checkpoint_files order by  file_type_desc, upper_bound_tsn

 

image

 

select file_type_desc, state_desc,relative_file_path, internal_storage_slot
from sys.dm_db_xtp_checkpoint_files  order by container_id, file_type_desc, upper_bound_tsn

 

image

image

image

 

Insert 90000 records and 2 new CPF’s in under construction were created.

 

image

Let’s insert some more records

The column file_size_used_in_bytes will be updated after the next checkpoint.

 

image

Automatic checkpoint completion: It is done when the transaction log since the last automatic checkpoint exceeds 512MB. In other words, an automatic checkpoint is kicked off for every 512MB of log records accumulated containing changes both from memory-optimized and diskbased tables. Unlike checkpoint for disk-based tables, the persistence of the data for memoryoptimized tables is done continuously by a background thread, the completion of a checkpoint updates internal metadata information

The Under Construction files will move to Active

image

 

image

image

 

I have disabled the Automatic Merge to issue a manually merge for demo

 

exec sys.sp_xtp_merge_checkpoint_files adventureworks2014, 2, 44

 

select * from sys.dm_db_xtp_merge_requests

 

image

Perform a log backup for log truncation checkpoint, to get the merge request installed.

clip_image002

Check the files which were merged.

select state,file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, 
drop_table_deleted_row_count from sys.dm_db_xtp_checkpoint_files  where state = 4
order by container_id, file_type_desc, upper_bound_tsn

 

image

Once the Merge is complete, file should be in required for backup state

image

 

image

After the backup and log truncation checkpoint [REQUIRED FOR BACKUP/HA] files will be moved to  TOMBSTONE

image

 

Issue a manual garbage collection

EXEC sp_filestream_force_garbage_collcection

 

image

 

Files marked as Tombstone were removed from the disk

image

After several checkpoints

clip_image002[4]

Categories: In-MemoryOLTP

In-Memory OLTP –Part 10

July 2, 2015 Leave a comment

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 in the checkpoint, performance of crash recovery degrades as the relevant number of rows in each data file decreases

The checkpoint system hands this through an automatic process called merge. SQL Server 2014 implements the following merge policy:

· A merge is scheduled if 2 or more consecutive CFPs can be consolidated, after accounting for deleted rows, such that the resultant rows can fit into 1 CFP of ideal size. The ideal size of CFP is determined as follows

  • For machines < 16GB. The data file is 16MB and delta file is 1MB
  • For machines > 16GB, the data file is 128MB and delta file is 8MB

· A single CFP can be self-merged the data file exceeds 256 MB and over half of the rows are deleted. A data file can grow larger than 128MB if, for example, a single transaction or multiple concurrent transactions insert/update large amount of data forcing the data file to grow beyond its ideal size because a transaction cannot span multiple CFPs.

There are 2 ways to merge CPF files.

  • Automatic Merge

Will cover this later.

  • Manual Merge (sys.sp_xtp_merge_checkpoint_files)
In most cases, the automatic merging of checkpoint files will be sufficient to keep the number of files to a manageable number. However, in rare situations, or for testing purposes, you might want to use a manual merge.

To manual merge find the min and max tsn, use sys.sp_xtp_merge_checkpoint_files with database name,min and max tsn

select min(lower_bound_tsn),max(upper_bound_tsn) from sys.dm_db_xtp_checkpoint_files cf

image

 

exec sys.sp_xtp_merge_checkpoint_files adventureworks2014, 379, 398

Once the Request is placed , check the status on the merge request using sys.dm_db_xtp_merge_requests

select * from sys.dm_db_xtp_merge_requests

image

 

  • Requested means that a merge request exists.

  • Pending means that the merge is being processing.(Request can be in pending state,if a checkpoint is required.)

  • Installed means that the merge is complete.

  • Abandoned means that the merge could not complete, perhaps due to lack of storage.

 

select file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='PRECREATED'
order by container_id, file_type_desc, upper_bound_tsn

There are 16 files under ‘PRECREATED’ state.The demo was run on machine with 4 logical processors with 16GB memory

  • PRECREATED – A small set of Checkpoint File Pairs (CFPs) are kept pre-allocated to minimize or eliminate any waits to allocate new files as transactions are being executed. These are full sized with data file size of 128MB and delta file size of 8 MB, but contain no data. The number of CFPs is computed as the number of logical processors or schedulers, with a minimum of 8. This is a fixed storage overhead in databases with memory-optimized tables.

select file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='PRECREATED'
order by container_id, file_type_desc, upper_bound_tsn

 

image

 

There are 3 data files marked ‘UNDER CONSTRUCTION’ as checkpoint has not been taken but these files together store 2700000 rows that was inserted. If database is restarted, the data rows will loaded using transaction log and NOT from these files as they are not part of a durable checkpoint

select file_type_desc, state_desc, relative_file_path,internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='UNDER CONSTRUCTION'
order by container_id, file_type_desc, upper_bound_tsn

 

image

 

image

 

You will notice that there will be 3 active files as soon as  the checkpoint occur and data will be written to these file

  • ACTIVE – These contain the inserted/deleted rows from previous closed checkpoints. These CFPs contain all required inserted/deleted rows required before applying the active part of the transaction log at the database restart. We expect that size of these CFPs to be approximately 2x of the in-memory size of memory-optimized tables, assuming the merge operation is keeping up with the transactional workload.

select file_type_desc, state_desc,relative_file_path, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='Active'
order by container_id, file_type_desc, upper_bound_tsn

 

image

Now, execute a manual checkpoint by executing the following command and see the state transition in the CFPs. You will note that UNDER CONSTRUCTION CFPs are now marked ACTIVE as they are part of a durable checkpoint that we have just completed. The manual checkpoint closed the data file with internal_storage_slot . These data file can potentially be merged in future as it qualifies under the merge policy.

image

 

Let’s issue the CHECKPOINT

 

CHECKPOINT

select file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count
from sys.dm_db_xtp_checkpoint_files where state = 2 order by container_id, file_type_desc, upper_bound_tsn

 

image

Let’s force a manual merge by executing the below command, the Merge will merge the active files

select min(lower_bound_tsn),max(upper_bound_tsn) from sys.dm_db_xtp_checkpoint_files

image

exec sys.sp_xtp_merge_checkpoint_files adventureworks2014, 392, 415

Check the Merge Request

select * from sys.dm_db_xtp_merge_requests

 

image

 

As we have forced the Merge  you will see the file marked [MERGE TARGET].

 

image

 

The Merge request will remain in Pending state if any of the file is pending for Backup or for a checkpoint, In this case a backup is required to install the Merge Request

 

image

 

Lets do a diff backup, as the backup kicks in Merge Request will  be installed, and CPF files will be marked as [MERGED SOURCE]

image 

image

 

Here is the output of the DMVs. It shows that the MERGE TARGET is now changed to ACTIVE state and all the CFPs that were used as source of the MERGE are now marked as MERGED SOURCE.

image

 

***state REQUIRED FOR BACKUP/HA meaning that these CFPs can’t be garbage collected as they are needed for operational correctness of the database with memory-optimized table

 

image

Categories: In-MemoryOLTP