Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP
The transaction performance collector in SQL Server Management Studio helps you evaluate if In-Memory OLTP will improve your database application's performance. The transaction performance analysis report also indicates how much work you must do to enable In-Memory OLTP in your application. 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. Similarly, the Native Compilation Advisor will help you port a stored procedure to a natively compiled stored procedure.
This topic will discuss how to:
Configure Management Data Warehouse.
Configure data collection.
Generate transaction performance analysis reports to identify performance-critical tables and stored procedures.
For information about migration methodologies, see In-Memory OLTP - Common Workload Patterns and Migration Considerations.
The transaction performance collector and the transaction performance analysis reports help you accomplish the following tasks:
Analyze your workload to determine if In-Memory OLTP will improve performance. The transaction performance collector collects and evaluates the performance characteristics of your workload. . The transaction performance analysis report then recommends tables and stored procedures that would benefit most from conversion to In-Memory 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. 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.
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.
Important
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.
The transaction performance collector and the ability to generate a transaction performance analysis report are installed when you select Management Tools-Basic or Management Tools-Advanced when you install SQL Server 2019 (15.x).
Best Practices
The recommended workflow is illustrated in the following flowchart. The yellow nodes represent optional procedures:
You can use any method to establish a performance baseline, including but not limited to using performance counter logs or the SQL Server Activity Monitor. The information to use in your performance baseline and your comparisons are:
CPU consumption of SQL Server.
Memory consumption of SQL Server.
I/O activity of SQL Server.
Transaction throughput of the instance while processing transactions.
The transaction performance collector captures data every 15 minutes. To obtain usable results, run the transaction performance collector for at least one hour. To obtain best results, run the transaction performance collector for as much time as needed to capture data for your primary scenarios. Generate a transaction performance analysis report only after you have finished gathering data.
Configure the transaction performance collector to run on your SQL Server instance in production and collect the data on a SQL Server instance in your development (test) environment to ensure minimum overhead. For information on how to save data in a Management Data Warehouse database on a remote SQL Server instance, see Configure Data Collection on a Remote SQL Server Instance.
Performance Impacts
The transaction performance collector consists of two data collection sets:
Table Usage Analysis
Stored Procedure Analysis
The collection sets collect data from three dynamic management views (DMV) every fifteen minutes, and upload the data to the database configured to act as the Management Data Warehouse. Uploading the collected data incurs minimal performance impact.
Use the Transaction Performance Collector
The following steps require SQL Server Management Studio in SQL Server 2019 (15.x).
Important
Do not change the schema (for example, add or remove databases or create or drop tables) during profiling. If you change the schema of a database while collecting data, the database may not be accurately included in the report.
Configure Management Data Warehouse
Management Data Warehouse must be configured to use the transaction performance collector.
The version of the SQL Server instance that you will collect data on (profile) should be the same version or older than the SQL Server where Management Data Warehouse is configured.
In Object Explorer, expand Management.
Right click Data Collection and select Tasks and then Configure Management Data Warehouse. The Configure Management Data Warehouse Wizard begins.
Click Next to select the database that will act as the Management Data Warehouse.
Click New to create a new database to hold the profile data. After you finish creating the database, click Next in the wizard.
The next step in the wizard lets you add users and logins. You may map logins to role memberships for the MDW instance. This is not required to collect data from the local instance. If you are not collecting data from the local instance, you can grant database role membership
mdw_adminto the account that will run transactions that will be profiled. When done, click Next.Make sure that SQL Server Agent is running.
On the next screen, click Finish to exit the wizard.
Configure Data Collection on a Local SQL Server Instance
Data collection requires SQL Server Agent to be started. You only need to configure one data collector on a server.
A data collector can be configured on a SQL Server 2012 or later version of SQL Server.
To configure data collection to upload to a Management Data Warehouse database on the same instance,
In Object Explorer, expand Management.
Right click Data Collection, select Tasks, and then Configure Data Collection. The Configure Data Collection Wizard begins.
Click Next to select the database that will collect the profile data.
Select the current SQL Server instance and a Management Data Warehouse database on that instance.
In the box labeled Select data collector sets you want to enable, select Transaction Performance Collection Sets. Click Next when done.
Verify the selections. Click Back to modify the settings. Click Finish when done.
Configure Data Collection on a Remote SQL Server Instance
Data collection requires SQL Server Agent to be started on the instance that will collect the data.
A data collector can be configured on a SQL Server 2012 or later version of SQL Server.
You need a SQL Server Agent proxy established with the correct credential for a data collector to upload data to a Management Data Warehouse database on an instance that is different from where transactions will be profiled. To enable a SQL Server Agent proxy, you must first establish a credential with a domain-enabled login. The domain-enabled login must be a member of mdw_admin group for the Management Data Warehouse database. See How to: Create a Credential (SQL Server Management Studio) for information on how to create a credential.
To configure data collection to upload to a Management Data Warehouse database on a different instance,
On the instance that contains the disk-based objects that you want to migrate to In-Memory OLTP, expand the Management node in Object Explorer.
Right click Data Collection and select Tasks and then Configure Data Collection. The Configure Data Collection Wizard begins.
Click Next to select the database that will collect the profile data.
Make sure that a Management Data Warehouse database exists on the other SQL Server instance.
Select another SQL Server instance and a Management Data Warehouse database on that instance.
The version of the SQL Server instance that you will collect data on (profile) should be the same version or older than the SQL Server where Management Data Warehouse is configured.
In the box labeled Select data collector sets you want to enable, select Transaction Performance Collection Sets.
Select Use a SQL Server Agent proxy for remote uploads.
Click Next when done.
Select the proxy.
If you want to create a new SQL Server Agent proxy,
Click New to display the New Proxy Account dialog box.
In the New Proxy Account dialog box, enter the name of the proxy, select the credential, and optionally enter a description. Then, click Principals.
Click Add and select Msdb role.
Select
dc_proxyand click OK. Then click OK again.
After the correct proxy is selected, click Next.
To configure System Collection Sets, check System Collection Sets and click Next.
Verify the selections. Click Back to modify the settings. Clicck Finish when done.
Data collection sets should now be configured and running on your instance.
Generate Reports
You can generate transaction performance analysis reports by right clicking the database of the Management Data Warehouse and selecting Reports, then Management Data Warehouse, and then Transaction Performance Analysis Overview.
The report collects information about all user databases on the workload server. If your Management Data Warehouse (MDW) database is on the local computer, you will see the MDW database(s) in the report.
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 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.
Interop Gain and Native Gain. These columns estimate the amount of performance benefit a point lookup or range scan would have if the table is converted to a memory-optimized table.
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. 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.
Scan and contention statistics on the table details report is gathered and aggregated from sys.dm_db_index_operational_stats (Transact-SQL).
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.
Total CPU Time. The total CPU time that the stored procedure consumed during profiling. 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. 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.
Execution Statistics on the stored procedure details report is gathered and aggregated from sys.dm_exec_procedure_stats (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.