确定表或存储过程是否应移植到内存中 OLTPDetermining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP

本主题适用于:是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

通过 SQL Server Management StudioSQL Server Management Studio 中的事务性能分析报表,可帮助你评估内存中 OLTP 是否将改进数据库应用程序的性能。The Transaction Performance Analysis report in SQL Server Management StudioSQL Server Management Studio helps you evaluate if In-Memory OLTP will improve your database application’s performance. 该报表还能够指明在应用程序中启用内存中 OLTP 所必须完成的工作量。The report also indicates how much work you must do to enable In-Memory OLTP in your application. 在你标识了要移植到内存中 OLTP 的基于磁盘的表之后,可以使用 内存优化顾问帮助你迁移表。After you identify a disk-based table to port to In-Memory OLTP, you can use the Memory Optimization Advisor, to help you migrate the table. 同样, Native Compilation Advisor 帮助您将存储过程移植到本机编译的存储过程。Similarly, the Native Compilation Advisor will help you port a stored procedure to a natively compiled stored procedure. 有关迁移方法的信息,请参阅 内存中 OLTP - 常见的工作负荷模式和迁移注意事项For information about migration methodologies, see In-Memory OLTP – Common Workload Patterns and Migration Considerations.

事务性能分析报表将直接对生产数据库或具有类似于生产工作负载的活动工作负载的测试数据库运行。The Transaction Performance Analysis report is run directly against the production database, or a test database with an active workload that is similar to the production workload.

报表和迁移顾问将帮助你完成以下任务:The report and migration advisors help you accomplish the following tasks:

  • 分析工作负载以确定在内存中 OLTP 可潜在帮助提高性能的热点。Analyze your workload to determine hot spots where In-Memory OLTP can potentially help to improve performance. 事务性能分析报表会建议可从转换为内存中 OLTP 获益最多的表和存储过程。The Transaction Performance Analysis report recommends tables and stored procedures that would benefit most from conversion to In-Memory OLTP.

  • 帮助您规划和执行到内存中 OLTP 的迁移。Help you plan and execute your migration to In-Memory OLTP. 从基于磁盘的表到内存优化表的迁移路径可能比较费时。The migration path from a disk based table to a memory-optimized table can be time consuming. 内存优化顾问可帮助您找到表中的不兼容之处(必须在将表迁移到内存中 OLTP 之前予以解决)。The Memory-Optimization Advisor helps you identify the incompatibilities in your table that you must remove before moving the table to In-Memory OLTP. 此外,内存优化顾问还可帮助您了解将表迁移到内存优化表会对应用程序产生何种影响。The Memory-Optimization Advisor also helps you understand the impact that the migration of a table to a memory-optimized table will have on your application.

    在规划到内存中 OLTP 的迁移时,或每当您需要将某些表或存储过程迁移到内存中 OLTP 时,都可了解应用程序是否可从内存中 OLTP 获益。You can see if your application would benefit from In-Memory OLTP, when you want to plan your migration to In-Memory OLTP, and whenever you work to migrate some of your tables and stored procedures to In-Memory OLTP.

    重要

    数据库的性能取决于多种因素,不是所有这些因素都能被事务性能收集器发现和度量。The performance of a database system is dependent on a variety of factors, not all of which the transaction performance collector can observe and measure. 因此,事务性能分析报告不保证实际性能收益会符合其预测(如果作出任何预测)。Therefore, the transaction performance analysis report does not guarantee actual performance gains will match its predictions, if any predictions are made.

    安装 SQL Server 2017SQL Server 2017 时选择“管理工具 - 基本”或“管理工具 - 高级”,或 下载 SQL Server Management Studio 时,事物性能分析报表和迁移顾问会作为 SQL Server Management Studio (SSMS) 的部分安装。The Transaction Performance Analysis report and the migration advisors are installed as part of SQL Server Management Studio (SSMS) when you select Management Tools—Basic or Management Tools—Advanced when you install SQL Server 2017SQL Server 2017, or when you Download SQL Server Management Studio.

事务性能分析报表Transaction Performance Analysis Reports

通过右键单击数据库,然后依次选择“报表”、“标准报表”、“事务性能分析概述”,可以在“对象资源管理器”中生成事务性能分析报表。You can generate transaction performance analysis reports in Object Explorer by right-clicking on the database, selecting Reports, then Standard Reports, and then Transaction Performance Analysis Overview. 数据库需要有活动的工作负载或最近运行的工作负载,才能生成有意义的分析报表。The database needs to have an active workload, or a recent run of a workload, in order to generate a meaningful analysis report.

Tables

表的详细报告包含三个部分:The details report for a table consists of three sections:

  • 扫描统计信息部分Scan Statistics Section

    本部分包含一个表,其中显示已收集的有关数据库表扫描的统计信息。This section includes a single table that shows the statistics that were collected about scans on the database table. 列包括:The columns are:

    • 总访问次数百分比。Percent of total accesses. 对此表的扫描和查询次数相对于整个数据库的活动的百分比。The percentage of scans and seeks on this table with respect to the activity of the entire database. 这个比例越高,使用此表的频率相对于数据库中的其他表就越大。The higher this percentage, the more heavily used the table is compared to other tables in the database.

    • 查找统计数据/范围扫描统计数据。Lookup Statistics/Range Scan Statistics. 此列记录在探查期间对表执行的点查询和范围扫描(索引扫描和表扫描)次数。This column records the number of point lookups and range scans (index scans and table scans) conducted on the table during profiling. 每事务的平均值为估计值。Average per transaction is an estimate.

  • 争用统计数据部分Contention Statistics Section

    本部分包含一个表,其中显示数据库表的争用。This section includes a table that shows contention on the database table. 有关数据库闩锁和锁的详细信息,请参阅“锁定体系结构”。For more information regarding database latches and locks, please see Locking Architecture. 这些列如下所示:The columns are as follows:

    • 总等待百分比。Percent of total waits. 此数据库表上闩锁和锁等待数占数据库活动的百分比。The percentage of latch and lock waits on this database table compared to activity of the database. 这个比例越高,使用此表的频率相对于数据库中的其他表就越大。The higher this percentage, the more heavily used the table is compared to other tables in the database.

    • 闩锁统计信息。Latch Statistics. 这些列记录涉及此表的查询的闩锁等待数。These columns record the number of latch waits for queries involving for this table. 有关闩锁的信息,请参阅“闩锁”。For information on latches, see Latching. 此数值越高,表的闩锁争用就越多。The higher this number, the more latch contention on the table.

    • 锁定统计信息。Lock Statistics. 这组列记录对此表的查询的页锁定获取和等待次数。This group of columns record the number of page lock acquisitions and waits for queries for this table. 有关锁的详细信息,请参阅“了解 SQL Server 中的锁定”。For more information on locks, see Understanding Locking in SQL Server. 等待越多,对表的锁定争用就越多。The more waits, the more lock contention on the table.

  • 迁移难度部分Migration Difficulties Section

    本部分包含一个表,其中显示将此数据库表转换为内存优化表的困难程度。This section includes a table that shows the difficulty of converting this database table to a memory-optimized table. 难度等级越高,转换表的难度就越大。A higher difficulty rating indicates more difficultly to convert the table. 要查看转换此数据库表的详细信息,请使用内存优化顾问。To see details to convert this database table, please use the Memory Optimization Advisor.

表扫描和争用统计信息详细报表从 sys.dm_db_index_operational_stats (Transact-SQL) 收集和聚合而成。Scan and contention statistics on the table details report is gathered and aggregated from sys.dm_db_index_operational_stats (Transact-SQL).

存储过程Stored Procedures

CPU 时间与占用时间的比值较高的存储过程适合迁移。A stored procedure with high ratio of CPU time to elapsed time is a candidate for migration. 该报告显示所有表引用,因为本机编译的存储过程只能引用内存优化的表,这可能加大迁移成本。The report shows all table references, because natively compiled stored procedures can only reference memory-optimized tables, which can add to the migration cost.

存储过程的详细报告包含两个部分:The details report for a stored procedure consists of two sections:

  • 执行统计信息部分Execution Statistics Section

    本部分包含一个表,其中显示与已收集的存储过程执行相关的统计信息。This section includes a table that shows the statistics that were collected about the stored procedure’s executions. 这些列如下所示:The columns are as follows:

    • 缓存的时间。Cached Time. 高速缓存此执行计划的时间。The time this execution plan is cached. 如果存储过程删除计划高速缓存并重新输入,这里将有每个缓存的时间。If the stored procedure drops out of the plan cache and re-enters, there will be times for each cache.

    • 总 CPU 时间。Total CPU Time. 存储过程在探查期间使用的总 CPU 时间。The total CPU time that the stored procedure consumed during profiling. 此数值越高,存储过程使用的 CPU 就越多。The higher this number, the more CPU the stored procedure used.

    • 总执行时间。Total Execution Time. 存储过程在探查期间使用的执行时间总量。The total amount of execution time the stored procedure used during profiling. 此数值与 CPU 时间之间的差值越高,存储过程使用 CPU 的效率就越低。The higher the difference between this number and the CPU time is, the less efficiently the stored procedure is using the CPU.

    • 总高速缓存失误数。Total Cache Missed. 探查期间由存储过程执行引起的高速缓存失误数(从物理存储读取)。The number of cache misses (reads from physical storage) that is caused by the stored procedure’s executions during profiling.

    • 执行计数。Execution Count. 在探查期间,此存储过程执行的次数。The number of times this stored procedure executed during profiling.

  • 表引用部分Table References Section

    本部分包含一个表,其中显示此存储过程引用的那些表。This section includes a table that shows the tables to which this stored procedure refers. 在将存储过程转换为本机编译的存储过程前,所有这些表必须转换为内存优化表,且它们必须位于同一服务器和数据库上。Before converting the stored procedure into a natively compiled stored procedure, all of these tables must be converted to memory-optimized tables, and they must stay on the same server and database.

    存储过程执行统计信息详细报表从 sys.dm_exec_procedure_stats (Transact-SQL) 收集和聚合而成。Execution Statistics on the stored procedure details report is gathered and aggregated from sys.dm_exec_procedure_stats (Transact-SQL). 从 sys.sql_expression_dependencies (Transact-SQL) 获取引用。The references are obtained from sys.sql_expression_dependencies (Transact-SQL).

    要详细了解如何将存储过程转换为本机编译存储过程,请使用本机编译顾问。To see details about how to convert a stored procedure to a natively compiled stored procedure, please use the Native Compilation Advisor.

生成内存中 OLTP 迁移清单Generating In-Memory OLTP Migration Checklists

迁移清单识别内存优化表或本机编译存储过程不支持的任何表或存储过程功能。Migration checklists identify any table or stored procedure features that are not supported with memory-optimized tables or natively compiled stored procedures. 内存优化和本机编译顾问可为基于单个磁盘的表或解释 T-SQL 存储过程生成一个清单。The memory-optimization and native compilation advisors can generate a checklist for a single disk-based table or interpreted T-SQL stored procedure. 还有可能为数据库中的多个表和存储过生成迁移清单。It is also possible to generation migration checklists for multiple tables and stored procedures in a database.

使用“生成内存中 OLTP 迁移清单”命令或使用 PowerShell,可以在 SQL Server Management StudioSQL Server Management Studio 中生成迁移清单。You can generate a migration checklist in SQL Server Management StudioSQL Server Management Studio by using the Generate In-Memory OLTP Migration Checklists command or by using PowerShell.

使用 UI 命令生成迁移清单To generate a migration checklist using the UI command

  1. 在“对象资源管理器”中,右键单击除系统数据库以外的数据库,单击“任务”,然后单击“生成内存中 OLTP 迁移清单”。In Object Explorer, right click a database other than the system database, click Tasks, and then click Generate In-Memory OLTP Migration Checklists.

  2. 在“生成内存中 OLTP 迁移清单”对话框中,单击“下一步”以导航到“配置清单生成选项”页。In the Generate In-Memory OLTP Migration Checklists dialog box, click Next to navigate to the Configure Checklist Generation Options page. 在该页上,执行下列操作。On this page do the following.

    1. 在“将清单保存到” 框中,输入文件夹路径。Enter a folder path in the Save checklist to box.

    2. 验证“为特定的表和存储过程生成清单” 处于选中状态。Verify that Generate checklists for specific tables and stored procedures is selected.

    3. 展开选择框中的“表” 和“存储过程” 节点。Expand the Table and Stored Procedure nodes in the section box.

    4. 在选择框中选择几个对象。Select a few objects in the selection box.

  3. 单击“下一步” 并确认任务列表列表与“配置清单生成选项” 页上的设置一致。Click Next and confirm that the list of tasks matches your settings on the Configure Checklist Generation Options page.

  4. 单击“完成” ,然后确认仅为选定的对象生成迁移清单报表。Click Finish, and then confirm that migration checklist reports were generated only for the objects you selected.

    将这些报表与内存优化顾问工具和本机编译顾问工具生成的报表进行比较,验证这些报表的准确性。You can verify the accuracy of the reports by comparing them to reports generated by the Memory Optimization Advisor tool and the Native Compilation Advisor tool. 有关详细信息,请参阅 Memory Optimization AdvisorNative Compilation AdvisorFor more information, see Memory Optimization Advisor and Native Compilation Advisor.

使用 SQL Server PowerShell 生成迁移清单To generate a migration checklist using SQL Server PowerShell

  1. 在“对象资源管理器” 中,单击数据库,然后单击“启动 PowerShell” 。In Object Explorer, click on a database and then click Start PowerShell. 验证出现了下面的提示。Verify that the following prompt appears.

    PS SQLSERVER: \SQL\{Instance Name}\DEFAULT\Databases\{two-part DB Name}>  
    
  2. 输入下面的命令。Enter the following command.

    Save-SqlMigrationReport –FolderPath “<folder_path>”  
    
  3. 验证下列各项。Verify the following.

    • 创建了文件夹路径(如果它尚不存在)。The folder path is created, if it doesn’t already exist.

    • 为数据库中的所有表和存储过程生成迁移清单报表,且报表位于 folder_path 指定的位置。The migration checklist report is generated for all tables and stored procedures in the database, and the report is in the location specified by folder_path.

使用 Windows PowerShell 生成迁移清单To generate a migration checklist using Windows PowerShell

  1. 启动提升的 Windows PowerShell 会话。Start an elevated Windows PowerShell session.

  2. 输入下面的命令。Enter the following commands. 对象可以是表或存储过程。The object can either be a table or a stored procedure.

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  
    
    Save-SqlMigrationReport –Server "<instance_name>" -Database "<db_name>" -FolderPath "<folder_path1>"  
    
    Save-SqlMigrationReport –Server "<instance_name>" -Database "<db_name>" -Object <object_name> -FolderPath "<folder_path2>"  
    
  3. 验证下列各项。Verify the following.

    • 为数据库中的所有表和存储过程生成迁移清单报表,且报表位于 folder_path 指定的位置。A migration checklist report is generated for all tables and stored procedures in the database, and the report is in the location specified by folder_path.

    • <object_name> 的迁移清单报表是 folder_path2 所指定位置的唯一报表。A migration checklist report for <object_name> is the only report in the location specified by folder_path2.

另请参阅See Also

迁移到内存中 OLTPMigrating to In-Memory OLTP