How will a service pack upgrade, configuration change or application change affect your production SQL Server? Which application, database or login is using the most resources? Which queries are responsible for high utilization of performance? I believe it is a good tool to give it a try. SQL Download. Description of the sample tables and procedures - includes descriptions of the tables and procedures added to AdventureWorks by the In-Memory OLTP sample, as well as considerations for migrating some of the original AdventureWorks tables to memory-optimized.
Instructions for performing Performance Measurements using the Demo Workload - includes instructions for installing and running ostress, a tool using for driving the workload, as well as running the demo workload itself.
Memory and Disk Space Utilization in the Sample. For performance testing, a server with specifications similar to your production environment. The sample creates new tables for products and sales orders, based on existing tables in AdventureWorks. The schema of the new tables is similar to the existing tables, with a few differences, as explained below. The memory-optimized tables used in the workload for performance comparison are fully durable and fully logged.
They do not sacrifice durability or reliability to attain the performance gain. The target workload for this sample is sales order processing, where we consider also information about products and discounts. Two new stored procedures, Sales. For the most part, the new tables introduced by this sample use the same columns and the same data types as the original tables.
However, there are a few differences. We list the differences below, along with a rationale for the changes. Default constraints are supported for memory-optimized tables, and most default constraints we migrated as is. However, the original table Sales. SalesOrderHeader contains two default constraints that retrieve the current date, for the columns OrderDate and ModifiedDate. In a high throughput order processing workload with much concurrency, any global resource can become a point of contention.
System time is such a global resource, and we have observed that it can become a bottleneck when running an In-Memory OLTP workload that inserts sales orders, in particular if the system time needs to be retrieved for multiple columns in the sales order header, as well as the sales order details.
OrderNumber and dbo. SQL Server Nullable columns in index keys - In the original table, the column SalesPersonID is nullable, while in the new tables the column is not nullable and has a default constraint with value This circumstance is because indexes on memory-optimized tables cannot have nullable columns in the index key; -1 is a surrogate for NULL in this case.
The new view Sales. Therefore, you can use this view if these columns are needed. Foreign key constraints are supported for memory-optimized tables in SQL Server Foreign keys that reference tables that are also migrated to memory-optimized are kept in the migrated tables, while other foreign keys are omitted.
Therefore, the assumption is that the app ensures referential integrity for the Sales. Rowguid - The rowguid column is omitted. Columns of this kind are typically used for either merge replication or tables that have filestream columns.
This sample includes neither. Computed columns - the computed column LineTotal was not migrated as computed columns are not supported with memory-optimized tables in SQL Server To access this column, use the view Sales. For details see the description for the table SalesOrderHeader. Alias UDTs - the original table uses the user-defined data type dbo.
Flag, which is equivalent to the system data type bit. The migrated table uses the bit data type instead. HASH indexes are can be used to further optimize the workload.
They are particularly optimized for point lookups and row inserts. However, one must consider that they do not support range scans, ordered scans, or search on leading index key columns. Therefore, care needs to be taken when using these indexes. It should usually be set at between one and two times the number of index key values, but overestimating is usually not a problem. The indexes on the migrated tables have been tuned for the demo sales order processing workload.
The workload relies on inserts and point lookups in the tables Sales. The data set provided does not have many sales persons. Windows 10, Windows 7, Windows 8, Windows 8. Warning: This site requires the use of scripts, which your browser does not currently allow. See how to enable scripts. Choose the download you want. Download Summary:. Total Size: 0. Back Next. Microsoft recommends you install a download manager. Microsoft Download Manager. You can also test how the system will behave with some change different service pack or hot fix build, changing a stored procedure or function, modifying or adding indexes, and so forth by using the provided tools to replay the trace files against another instance of SQL Server.
If you capture trace during this replay you can use the tools to directly compare to the original baseline capture. This release of the utilities supersedes the previous version, as described in KB The previous release continues to be available to customers who do not have SQL Server
0コメント