OLTP in SQL SERVER

August 20, 2016 by Arti Khedkar

Filed under Database, SQL

Last modified September 22, 2016

OLTP in SQL SERVER

 

Online Transaction and Processing helps and manages applications based on transactions involving high volume of data. The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. It involves Queries accessing individual record like Update your Email in Company database.

OLTP in SQL SERVER

OLTP in SQL SERVER

For OLTP systems, the middle tier plays an important role in defining the overall success of the system; while the database is important, it is not the only system component. When a new OLTP system is developed, the initial challenges in scalability and performance are often encountered in the middle tier. It is only after the challenges in the middle tier are addressed that the database tier scalability and performance issues are revealed. We recommend that you refer to the Technical Reference Guides included in the Data Warehouse and AppFabric topics when gathering data points for operational considerations.

OLTP in SQL SERVER

OLTP in SQL SERVER

 

Implementation Scenario
Characteristics Benefits of In-Memory OLTP
High data insertion rate from multiple concurrent connections, for example sensors in IoT (Internet of Things) scenario. Primarily append-only store.

Unable to keep up with the insert workload.

Eliminate contention.

Reduce logging.

Read performance and scale with periodic batch inserts and updates. High performance read operations, especially when each server request has multiple read operations to perform.

Unable to meet scale-up requirements.

Eliminate contention when new data arrives.

Lower latency data retrieval.

Minimize code execution time.

Intensive business logic processing in the database server. Insert, update, and delete workload.

Intensive computation inside stored procedures.

Read and write contention.

Eliminate contention.

Minimize code execution time for reduced latency and improved throughput.

Low latency. Require low latency business transactions which typical database solutions cannot achieve. Eliminate contention.

Minimize code execution time.

Low latency code execution.

Efficient data retrieval.

Session state management. Frequent insert, update and point lookups.

High scale load from numerous stateless web servers.

Eliminate contention.

Efficient data retrieval.

Optional IO reduction or removal, when using non-durable (SCHEMA_ONLY) tables.

Temp Tables and Table-Valued Parameters Frequent use of temporary tables in application logic.

Frequent use of Table-Valued Parameters (TVPs) to send data from the client to the server.

Eliminate contention in tempdb.

Remove IO by using memory-optimized table variables and non-durable (SCHEMA_ONLY) tables.

ETL (Extract, Transform, Load) Use of staging tables to load data into a data warehousing database. Efficient data access.

Remove IO by using non-durable (SCHEMA_ONLY) tables.

 

 

Leave a Comment