sql oltp
On the last article about the best modes to move the disk-based tables to using the In-Memory feature we covered all the aspects and styles available on SQL Server 2014. Continuing on the migration process now we’re going to look at some of the new enhancements of makes SQL Server 2016
在上一篇文章中，关于将基于磁盘的表移至使用内存中功能的最佳模式的上一篇文章 ，我们介绍了SQL Server 2014上可用的所有方面和样式。现在继续迁移过程，我们将介绍一些SQL Server 2016的新增功能
数据收集器[交易绩效分析概述报告] ( Data Collector [Transaction Performance Analysis Overview Report] )
The Management Data Warehouse was incorporated into SQL Server 2008 for one specific objective, collect different sets of data and store in a relational database for report purposes.
为了一个特定的目标，管理数据仓库已合并到SQL Server 2008中，可以收集不同的数据集并存储在关系数据库中以用于报表。
A data collector is a centralized place where we can collect and store performance and analytic data for further analysis and troubleshooting purposes.
数据收集器是一个集中的地方，我们可以在其中收集和存储性能和分析数据，以进行进一步的分析和故障排除。
If you have never used it before, I strongly recommend that you to test it in your development environment first. Please refer to the article here to configure and understand more about the MDW.
如果您以前从未使用过它，我强烈建议您首先在开发环境中对其进行测试。 请参考此处的文章以配置和了解有关MDW的更多信息。
In SQL Server 2014 the Data Collector added a new report called – Transaction Performance Analysis Overview with the main purpose to provide a smart way to identify which tables and stored procedure will give you the best performance gain after being migrated to In-Memory.
在SQL Server 2014中，数据收集器添加了一个名为“ 事务性能分析概述”的新报告，其主要目的是提供一种聪明的方法来识别哪些表和存储过程在迁移到内存后将为您带来最佳性能提升。
Those new collectors were added to SQL Server 2014 and named the Analysis, Migrate and Report (AMR) tool.
这些新的收集器已添加到SQL Server 2014中，并命名为分析，迁移和报告（AMR）工具。
The main point to adding this into SQL Server 2014 was to provide a better way for the DBA to have a location to discover and find the most applicable tables and SPs that can be migrated to memory-optimized objects. The AMR basically consists of the:
将其添加到SQL Server 2014中的主要目的是为DBA提供一种更好的方法，使其能够发现并找到最适用的表和SP，这些表和SP可以迁移到内存优化的对象。 AMR基本上包括：
- Transaction Performance Collector交易绩效收集者
- Memory Optimization Advisor内存优化顾问
- Native Compilation Advisor本机编译顾问
In SQL Server 2016, new enhancements have been introduced in the Data Collector feature for this particular report that we’re looking into. Now the contention and usage analysis is more precise.
在SQL Server 2016中，针对我们正在研究的此特定报表，数据收集器功能中引入了新的增强功能。 现在，竞争和使用情况分析更加精确。
Those sets of collectors are responsible for analyzing the workload and discovering the necessary patterns to suggest the best tables and SPs to be ported to In-Memory.
这些收集器负责分析工作负载并发现必要的模式，以建议将最佳表和SP移植到内存中。
The AMR tool leverages the new Transaction Performance Analysis Overview report in order to identity bottlenecks and provide assistance to convert the disk-based tables to memory-optimized objects.
AMR工具利用新的“ 事务性能分析概述”报告来识别瓶颈，并提供帮助将基于磁盘的表转换为内存优化的对象。
The Report page displays two forms to analyze your workload/environment divided by Tables and SPs
“报告”页面显示两种表格来分析您的工作负载/环境，分别是表和SP
- Usage Analysis 使用情况分析
- Contention Analysis 竞争分析
As starting point let’s check the Table Analysis bar by digging into the 2 reports – Usage Analysis and Contention Analysis.
作为起点，让我们检查表分析 通过深入研究2个报告（ 使用率分析和竞争分析）来进行选择。
Table Analysis – Usage & Contention Analysis
表分析–使用情况和竞争分析
Figure 2 – Recommended Tables Based on Usage 图2 –基于用法的推荐表 The most important information that we can take from here is the magic quadrant that appears on both reports. These arrows indicates 2 major bits of information – High Gain and Minimal Migration Work. This combination is very attractive because if one of the tables appears there means that the migration from disk-based tables to in-memory tables will be able to take the most advantage of the migration with minimal effort.
我们可以从此处获取的最重要信息是出现在两个报告中的魔力象限。 这些箭头指示2个主要信息- 高增益和最小迁移工作 。 这种组合非常吸引人，因为如果其中有一个表出现，则意味着从基于磁盘的表到内存中表的迁移将能够以最小的努力最大程度地利用迁移的优势。
Clicking on one such table, we will step into detailed information about the possible gains to port the table to memory.
单击一个这样的表，我们将逐步进入有关将表移植到内存的可能收益的详细信息。
Figure 3 – Details for Table 图3 –表的详细信息 The report leverages a lot of useful information but unfortunately the data related to Lookup and Range Scan Statistics are blank at this stage because data is not being generated from the high workload environment.
该报告利用了许多有用的信息，但遗憾的是，由于不是从高工作负载环境中生成数据，因此与“ 查找”和“ 范围扫描统计信息”相关的数据在此阶段为空白。
On this report are two key things that are really interesting to capture, the interop and native gain aspects. Normally we break the In-Memory migration process into 2 steps, first we recommend the Interop Gain and then if your gains were satisfactory, but you want more, you can jump and go to the Native Gain.
在此报告中，有两个非常有趣的关键要素，即互操作性和本机增益方面。 通常，我们将内存中迁移过程分为两个步骤，首先我们建议互操作增益，然后，如果您的增益令人满意，但是想要更多，则可以跳转并转到“本机增益”。
The Interop Gain is the easiest and safest way to migrate the data to the memory-optimized objects because it will require a minimum of effort from. The process is quite simple, just use the Memory Optimization Advisor, to move the table to In-Memory. This will not interfere or affect the application because you can rename the new optimized-memory table to the same name of the table that the application uses making for a clean, fast and easy migration. The catch here is that the potential gains of the Interop Mode are not so significant as the Native Gains because every query that hits that table in Interop Mode needs to pass through the Query Interoperability component inside of the Sqlservr.exe.
互操作增益 这是将数据迁移到内存优化对象的最简单，最安全的方法，因为这将花费最少的精力。 这个过程很简单，只需使用内存优化顾问 ，表移动到内存中。 这不会干扰或影响应用程序，因为您可以将新的优化内存表重命名为应用程序使用的表的相同名称，以进行干净，快速和轻松的迁移。 这里要注意的是， 互操作模式的潜在收益没有本机增益那么重要，因为在互操作模式下命中该表的每个查询都需要通过查询互操作性 Sqlservr.exe内部的组件。
On the other hand the Native Gain doesn’t use the Query Interoperability component to access the memory objects. Actually using this mode, SQL Server will simply point to the Natively Compiled Stored Procedure component that will require the Storage Engine to access the already persisted and compiled object in native code. This access is faster than the Interop Mode but comes with some drawbacks like:
另一方面，本机增益不使用查询互操作性组件来访问内存对象。 实际上使用此模式时，SQL Server将仅指向“本机编译的存储过程”组件，该组件将要求存储引擎以本机代码访问已持久存储和已编译的对象。 这种访问比互操作模式要快，但也有一些缺点，例如：
- Limited Code Functionality 有限的代码功能
- Logic Change on SPs SP上的逻辑更改
- Cost of Time and Effort 时间和精力的成本
Application Change
申请变更
Stored Procedure Analysis – Usage Analysis
存储过程分析–使用情况分析
Figure 4 – Recommended Stored Procedure Based on Usage 图4 –基于使用情况的推荐存储过程 Based upon the usage, the report gives you the top rated SPs by analyzing the code and checks if they are good candidates to become Natively Stored Procedures. By clicking on the first SP showed at the prior on the list we can retain more statistical information. After reviewing this information the next move is deciding if it is pertinent to rewrite to code and turn this into a Natively Stored Procedure. Please note that Natively Stored Procedures just access memory-optimized tables.
根据使用情况，该报告通过分析代码为您提供评分最高的SP，并检查它们是否适合成为本机存储过程。 通过单击列表上前面显示的第一个SP，我们可以保留更多统计信息。 在查看了此信息之后，下一步就是确定是否需要重写代码并将其转变为本地存储过程。 请注意，本机存储过程仅访问内存优化表。
Figure 5 – Details for SP 图5 – SP的详细信息
在GUI和PowerShell上生成内存中OLTP迁移清单 ( Generate In-Memory OLTP Migration CheckList on GUI & PowerShell )
The new migration checklist option identifies any table or stored procedure that are not supported to be ported to a memory-optimized object in form of a report representation. This advisor doesn’t have any dependency on the Management Data Warehouse and gives a complete checklist for a single or multiple disk-based tables or interpreted stored procedure as part of the SQL Server 2016 enhancements.
新的迁移清单选项可标识不支持以报表表示形式移植到内存优化对象的任何表或存储过程。 该顾问程序对管理数据仓库没有任何依赖性，并且作为SQL Server 2016增强功能的一部分，提供了一个或多个基于磁盘的表或解释性存储过程的完整清单。
There are 2 different forms to use the In-Memory OLTP Migration CheckList. The first option is to use the UI command, so for this go to the database that has the tables or SPs that you want to verify, right click – tasks and go to Generate In-Memory OLTP Migration CheckList.
有两种不同的形式可以使用内存中OLTP迁移清单。 第一种选择是使用UI命令，因此，请转到具有要验证的表或SP的数据库，右键单击–任务，然后转到“ 生成内存中OLTP迁移清单”。
Select the place that you want to save the checklist and choose the Tables and SPs that you want to verify if they’re complying with all the pre-requisites established and finish the prompt.
选择要保存清单的位置，然后选择要验证的表和SP是否符合已建立的所有前提条件，并完成提示。
Openning the folder that you selected that in my case is the C:\Users\administrator\Downloads\DWConfiguration\Tables you will see all the tables that you selected to be reported.
打开您选择的文件夹（在本例中为C：\ Users \ administrator \ Downloads \ DWConfiguration \ Tables），您将看到所有选择报告的表。
The second option available will return the same input of the generated report and you can accomplish it by using PowerShell Commandlets. Open PowerShell on SQL Server and use this command to generate the same report that we looked at before. To learn more about the PowerShell commandlet, please click here.
第二个可用选项将返回生成的报告的相同输入，您可以使用PowerShell Commandlet完成该输入。 在SQL Server上打开PowerShell，并使用此命令生成我们之前查看过的相同报告。 要了解有关PowerShell Commandlet的更多信息，请单击此处 。
PS C:\Users\administrator> $objectsList = "dbo.distribution","dbo.UpgradeDWConfiguration"
for ($i = 0; $i -le $objectsList.count-1; $i++)
{
$schema = $objectsList[$i].Split(".")[0]
$object = $objectsList[$i].Split(".")[1]
Save-SqlMigrationReport -Server 'SERPENS' -Database 'DWConfiguration' -Schema $schema -Object $object -FolderPath 'C:\Users\administrator\Downloads'
}
结论 ( Conclusion )
At the end of these two articles, we visited all the migration steps/possibilities to transform disk-based objects into memory-optimized objects passing through the SQL Server 2014 and 2016 versions.
在这两篇文章的结尾，我们访问了所有迁移步骤/可能性，以将基于磁盘的对象转换为经过SQL Server 2014和2016版本的内存优化对象。
Now that you already know the steps to proceed with this, you are ready to start your first migration and see the power of the In-Memory Optimization for yourself.
既然您已经知道执行此步骤的步骤，则可以开始进行第一次迁移，并亲自了解内存中优化的功能。
翻译自: https://www.sqlshack.com/in-memory-oltp-series-data-migration-guideline-process-on-sql-server-2016/
sql oltp
2893
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
