sql oltp
Special thanks to Matthew Wilkin for kindly helping to peer review this article.
特别感谢Matthew Wilkin慷慨地帮助同行审阅本文。
Internet applications use the HTTP protocol, following a request-response paradigm. As each request is independent of the other, we need to find a way to maintain some information across multiple requests. For that, either we put all the common data in each request, or we put key(s) in request(s) to store the data on the server against each key.
Internet应用程序遵循请求-响应范例，使用HTTP协议。 由于每个请求彼此独立，因此我们需要找到一种方法来维护多个请求中的某些信息。 为此，我们要么将所有通用数据放入每个请求中，要么将密钥放入请求中，以针对每个密钥将数据存储在服务器上。
State management is a basic requirement for any web application. As a user interacts with an application, we need to maintain the user’s preferences — the application state with its corresponding actions and responses, and potentially other metadata related to the application. Therefore, most of the web application frameworks today provide various out-of-the-box features for state management.
状态管理是任何Web应用程序的基本要求。 当用户与应用程序进行交互时，我们需要维护用户的首选项-应用程序状态及其相应的动作和响应，以及可能与该应用程序相关的其他元数据。 因此，当今的大多数Web应用程序框架都为状态管理提供了各种现成的功能。
The ASP.NET framework is one of the most used frameworks for building web applications. It provides rich features and tools for rapid application development. Sessions are commonly used as a state management technique in numerous ASP.NET applications due to their simplicity and flexibility. Let’s have a quick overview of session state management.
ASP.NET框架是用于构建Web应用程序的最常用的框架之一。 它为快速的应用程序开发提供了丰富的功能和工具。 会话由于其简单性和灵活性而通常在许多ASP.NET应用程序中用作状态管理技术。 让我们快速概述会话状态管理。
At a high level, the diagram above shows there are two types of session modes: InProc (In Process) and OutProc (Out of Process). While using InProc mode, session data is stored in the web server’s memory, while OutProc allows us to store the session data either in a State Server, SQL Server or at any other custom location. InProc is the fastest, as data is available in the web server’s memory itself, with no serialization/deserialization or I/O required.
在较高级别上，上图显示了两种类型的会话模式：InProc(处理中)和OutProc(处理中)。 使用InProc模式时，会话数据存储在Web服务器的内存中，而OutProc允许我们将会话数据存储在State Server，SQL Server或任何其他自定义位置。 InProc是最快的，因为数据可以从Web服务器的内存中获取，而无需序列化/反序列化或I / O。
But this doesn’t fit into a Web Farm/Web Garden scenario, commonly found in Enterprise applications, where SQL Server is used for storing Session State. A Web Farm is a group of web servers hosting the application, where a load balancer routes the request based on the load of a server. On the other hand, a Web Garden involves multiple worker processes (w3wp.exe) assigned to the same application pools, which is used by the hosting application. Both provide a way to scale the application if traffic load can’t be handled by one server or one worker process.
但这不适用于企业应用程序中常见的Web场/ Web Garden方案，在该方案中，SQL Server用于存储会话状态。 Web场是托管应用程序的一组Web服务器，负载平衡器根据服务器的负载路由请求。 另一方面，Web Garden涉及分配给同一应用程序池的多个辅助进程(w3wp.exe)，托管应用程序使用该进程。 如果一台服务器或一个工作进程无法处理流量负载，两者都提供了一种扩展应用程序的方法。
Whenever the need for reliable sessions becomes a necessity, Enterprise applications tend to leverage the High Availability features found in SQL Server, such as redundancy and clusters. However, using SQL Server has an extra performance cost, because we need to serialize/de-serialize session data while reading/writing to SQL. This requires an I/O operation as data resides in disk-based files.
每当需要可靠的会话时，企业应用程序就会倾向于利用SQL Server中的高可用性功能，例如冗余和群集。 但是，使用SQL Server会带来额外的性能成本，因为在读取/写入SQL时我们需要对会话数据进行序列化/反序列化。 由于数据驻留在基于磁盘的文件中，因此这需要I / O操作。
Session data is generally small in size but very dynamic in nature. In times of heavy load, many threads could be writing session data while others read the data. As the threads actively access the data in a small region, lock and latch contentions start creating issues. Reading and writing data starts to take more time, which makes for a bad user experience. So there’s a trade off in each mode, but as discussed, we end up using SQL in many scenarios and bear the performance cost. For better performance, we invest more dollars by adding more powerful servers into the system, but are benefitted only to a certain extent, and all the available resources are not used optimally.
会话数据通常很小，但本质上是非常动态的。 在负载很重的时候，许多线程可能正在写入会话数据，而其他线程则正在读取数据。 当线程主动访问较小区域中的数据时，锁和闩锁争用开始产生问题。 读取和写入数据开始需要花费更多时间，这会带来糟糕的用户体验。 因此，每种模式都需要权衡取舍，但是如上所述，我们最终在许多情况下使用SQL并承担了性能成本。 为了获得更好的性能，我们在系统中添加了更多功能强大的服务器，从而投入了更多资金，但仅在一定程度上受益，并且所有可用资源均未得到最佳利用。
In-Memory OLTP is Microsoft’s (relatively) new online transaction processing technology, which was introduced with SQL Server 2014 to provide a high-performance database engine with the help of memory optimized tables that permanently stays in memory and uses a native stored procedure compiler.
内存中OLTP是Microsoft的(相对)新的在线事务处理技术，是随SQL Server 2014一起引入的，可借助内存优化表(永久保留在内存中并使用本机存储过程编译器)提供高性能的数据库引擎。
These tables don’t suffer from latching, because the In-Memory OLTP engine uses lock-free algorithms and structures.
这些表不会受到闩锁的影响，因为内存中OLTP引擎使用了无锁算法和结构。
Microsoft started working on a project with code name Hekaton that was aimed to provide 100 times faster database engine than the disk based one. In SQL Server 2014, they introduced this new engine, which is 30–40 times faster than the traditional one. They’ve made a good amount of changes in SQL Server 2016 (which is in preview currently) and made it more robust. So if we leverage this feature of SQL Server then we can almost mitigate the performance cost of IO-bound SQL as Session state provider.
微软开始开发一个代号为Hekaton的项目，该项目旨在提供比基于磁盘的数据库引擎快100倍的数据库引擎。 在SQL Server 2014中，他们引入了这种新引擎，它比传统引擎快30–40倍。 他们在SQL Server 2016(目前正在预览)中进行了大量更改，并使其更加强大。 因此，如果我们利用SQL Server的此功能，则几乎可以减轻作为会话状态提供者的IO绑定 SQL的性能成本。
To use In-Memory OLTP, we can write our own custom provider. However, there’s already one package available via NuGet that leverages this feature, so instead of rolling our own, we’ll use it. Configuring this package is very straightforward. The NuGet package may be found on the NuGet site, or it can be installed using Package Manager Console with
要使用内存中OLTP，我们可以编写自己的自定义提供程序。 但是，通过NuGet已经有一个利用此功能的软件包，因此我们将使用它而不是自己开发。 配置此程序包非常简单。 您可以在NuGet网站上找到NuGet软件包，也可以使用Package Manager Console通过以下方式安装：
PM> Install-Package Microsoft.Web.SessionState.SqlInMemoryThis package performs the following changes to your application:
该软件包对您的应用程序执行以下更改：
Adds an assembly reference of Microsoft.Web.SessionState.SqlInMemory
添加Microsoft.Web.SessionState.SqlInMemory的程序集引用
Updates the sessionState configuration in your web.config to use SqlInMemoryProvider:
更新web.config中的sessionState配置以使用SqlInMemoryProvider：
<sessionState mode="Custom" customProvider="SqlInMemoryProvider"> <providers> <add name="SqlInMemoryProvider" type="Microsoft.Web.SessionState.SqlInMemoryProvider" connectionString="data source=***;initial catalog=ASPStateInMemory;User ID=user;Password=password;" /> </providers> </sessionState>Change the connection string accordingly.
相应地更改连接字符串。
Provides a SQL script named ASPStateInMemory.sql, which can be used to configure the In-Memory database. Before executing, be sure to update the script with any configuration changes as noted below.
提供一个名为ASPStateInMemory.sqlSQL脚本，该脚本可用于配置内存数据库。 在执行之前，请确保按照以下说明进行任何配置更改来更新脚本。
a. Change the name of the database, which is by default named as ASPStateInMemory. Replace
一个。 更改数据库的名称，默认情况下名为ASPStateInMemory 。 更换
CREATE DATABASE [ASPStateInMemory] to CREATE DATABASE <your database name>b. Configure the path of the database primary group. Replace
b。 配置数据库主组的路径。 更换
NAME = ASPStateInMemory, FILENAME 'D:\SQL\data\ASPStateInMemory_data.mdf' with NAME = ASPStateInMemory, FILENAME <path of mdf file>c. Change the file name that specifies the path for the memory-optimized file group according to your own server. Replace
C。 根据您自己的服务器，更改为内存优化的文件组指定路径的文件名。 更换
NAME = ASPStateInMemory_xtp, FILENAME = 'D:\SQL\data\ASPStateInMemory_xtp' with NAME = ASPStateInMemory_xtp, FILENAME = <path of xtp file>d. In-Memory OLTP provides two durability options:
d。 内存中OLTP提供两种耐用性选项：
SCHEMA_ONLY: in case of server restart, the tables would be recreated and previous data would be lost. It is faster and used when we have transient data. By default it is enabled.
SCHEMA_ONLY ：如果服务器重新启动，则将重新创建表，并且先前的数据将丢失。 当我们有瞬态数据时，它会更快并被使用。 默认情况下启用。
SCHEMA_AND_DATA: in case of server restart, the schema and data are maintained, similar to disk-based tables. To configure it, change the DURABILITY attribute from SCHEMA_ONLY to SCHEMA_AND_DATA.
SCHEMA_AND_DATA ：如果服务器重新启动，则将保留架构和数据，类似于基于磁盘的表。 要对其进行配置，改变了DURABILITY ，从属性SCHEMA_ONLY到SCHEMA_AND_DATA 。
After execution, it creates a new database [ASPStateInMemory] (default) with two tables as dbo.[Sessions] and dbo.[SessionItems].
执行后，它将创建一个新数据库[ASPStateInMemory](默认)，其中包含两个表，分别为dbo。[Sessions]和dbo。[SessionItems]。
When we use SQL for session state management, we need to delete the expired session data. In the traditional way, we used an SQL Server Agent Job to delete the expired session periodically.
当我们使用SQL进行会话状态管理时，我们需要删除过期的会话数据。 以传统方式，我们使用SQL Server代理作业定期删除过期的会话。
Similarly here, we have a new Stored Procedure DeleteExpiredSessions, which can be configured in a job to delete the expired sessions. By default, session timeout is 20 minutes, which can be configured based on our needs, by adding the timeout attribute to the sessionState tag in our web.config, and each time a session is accessed this timeout gets reset.
同样，在这里，我们有一个新的存储过程DeleteExpiredSessions ，可以在作业中对其进行配置以删除过期的会话。 默认情况下，会话超时为20分钟，可以根据需要通过将timeout属性添加到web.config中的sessionState标记中来配置，并且每次访问会话时都会重置此超时。
In-Memory OLTP will boost the performance when using SQL Server Session State by a factor of 30–40 times when compared to using a traditional SQL Server Session State. We can also leverage the following features of SQL using In-Memory OLTP:
与使用传统SQL Server会话状态相比，使用SQL Server会话状态时，内存中OLTP可以将性能提高30–40倍。 我们还可以使用内存中OLTP利用SQL的以下功能：
We can make our session highly available with the help of the SQL Server AlwaysOn feature. We can also leverage the traditional way of making high availability using failover clustering. In order to do that, we need to make some changes in the script, like setting durability as SCHEMA_AND_DATA and changing the Id constraint for the SessionItems table.
我们可以借助SQL Server AlwaysOn功能使会话高度可用。 我们还可以利用使用故障转移群集的传统方式来实现高可用性。 为此，我们需要在脚本中进行一些更改，例如将持久性设置为SCHEMA_AND_DATA并更改SessionItems表的Id约束。
We can make use of the Geo-redundancy feature of the SQL Server, which makes it durable and highly available. Geo-redundancy replicates data between two geographically distant sites, so that applications can switch from one site to another. Using this, in case of a failure on one site for whatever reason, the other site can be used, as it has all the data and configuration available.
我们可以利用SQL Server的地理冗余功能，使其持久且高度可用。 地理冗余可在两个地理位置遥远的站点之间复制数据，以便应用程序可以从一个站点切换到另一个站点。 使用此方法，万一某个站点由于某种原因发生故障时，可以使用另一站点，因为它具有所有可用的数据和配置。
In-Memory OLTP can be used in Web Farm and Web Garden scenarios.
内存中OLTP可用于Web场和Web Garden方案。
Performance-wise, In-Memory OLTP is as good as InProc mode.
在性能方面，内存中OLTP与InProc模式一样好。
As demonstrated in a recent case study (which you can download as a Word document from Microsoft.com), the throughput of an ASP.NET application – which was using a traditional SQL Server for maintaining the session state – got increased 16 times (15000 to 250000 request per sec) after migration. It allowed for consolidation of many logically partitioned servers into one. It did not require any code changes at all.
正如最近的一个案例研究(您可以从Microsoft.com作为Word文档下载)所证明的那样 ，使用传统SQL Server维护会话状态的ASP.NET应用程序的吞吐量增加了16倍(15,000)。迁移到每秒250000个请求)。 它允许将许多逻辑分区的服务器合并为一个。 它根本不需要任何代码更改。
This article has looked at the ASP.NET session state provider and its available options, key challenges, and how each option has its own drawbacks. It also looked at the In-Memory OLTP option, introduced with SQL Server 2014, that can be leveraged for storing session state information.
本文研究了ASP.NET会话状态提供程序及其可用选项，主要挑战以及每个选项如何具有自身的缺点。 它还研究了SQL Server 2014附带的内存中OLTP选项，该选项可用于存储会话状态信息。
In-Memory OLTP stores the data in server memory, uses a native stored procedure compiler, and is free from lock/latch contentions. It provides 30–40 times faster performance than its predecessor and can improve an application’s throughput by 16 times after moving to an In-Memory database.
内存中OLTP将数据存储在服务器内存中，使用本机存储过程编译器，并且没有锁/闩锁争用。 它提供的性能比其前代产品快30–40倍，并且在移至内存数据库后可以将应用程序的吞吐量提高16倍。
However, there are limitations in SQL Server 2014, such as the size of memory optimized tables, absence of parallel plans, and some issues in native compilations. All of these are expected to be addressed in SQL Server 2016, which will make this feature more powerful and flexible.
但是，SQL Server 2014中存在一些限制，例如内存优化表的大小，缺少并行计划以及本机编译中的某些问题。 所有这些都有望在SQL Server 2016中得到解决，这将使此功能更加强大和灵活。
翻译自: https://www.sitepoint.com/sql-server-in-memory-oltp-as-asp-net-session-state-provider/
sql oltp
相关资源：jdk-8u281-windows-x64.exe