Download in memory oltp simulator

First published on MSDN on Nov 08, 2017 As Internet of Things (IoT) devices and sensors are becoming more ubiquitous in consumer, business and industrial landscapes, they introduce a unique challenge in terms of the volume of data they produce, and the velocity with which they produce it. The challenge is to ingest and analyze this data at the speed at which it is being generated, in real-time. Azure SQL Database with In-Memory OLTP and Columnstore technologies is phenomenal at ingesting large volumes of data from many different sources at the same time, while providing the ability to analyze current and historical data in real-time. The following sample demonstrates the high scale and performance of SQL Database, with the ability to insert 1.4 million rows per second by using a non-durable memory-optimized table to speed up data ingestion, while managing the In-Memory OLTP storage footprint by offloading historical data to a disk-based Columnstore table for real time analytics. One of the customers already leveraging Azure SQL Database for their entire IoT solution is Quorum International Inc. , who was able to double their key database’s workload while lowering their DTU consumption by 70%. Sample release and source code: Release | source code High Level Architecture Ingesting Data: IoT sensor data loader A multi-threaded data loader application (running on a Standard DS15 v2 Azure VM) is used to generate sensor readings that are inserted directly into the dbo.MeterMeasurement schema-only memory optimized table through the dbo.InsertMeterMeasurement natively compiled stored procedure (that accepts a memory optimized table valued parameter). Also, the application is responsible for off-loading historical data to a disk based Columnstore table to manage the In-Memory storage footprint. Below is a screenshot of the data simulator inserting 1.4M rows per second by using a single P15 Premium (4000 DTUs) Azure SQL Database. Off Loading Data: Bulk load historical data into a clustered Columnstore index Historical data is offloaded from the In-Memory table to a disk based Columnstore table to manage the In-Memory storage footprint. The dbo.InsertMeterMeasurementHistory stored procedure is called by multiple threads asynchronously to offload historical data into a clustered

The FILESTREAM tips on MSSQLTips.com, which includes syntax and practical use cases.Let’s see a simple example. Suppose we want to create a database named HRMS_Account with a FILESTREAM filegroup. The HRMS_Account database contains one FILESTREAM filegroup named HRMS_Account_Receipt, which stores the soft copies of invoices. The filestream location is D:\HRMS_Account_Receipts\HRMS_Account_Receipt.The T-SQL command to create a database is as follows:CREATE DATABASE [HRMS_Account]ON PRIMARY ( NAME = N'HRMS_Account', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Account.mdf' ),FILEGROUP [HRMS_Account_Receipts] CONTAINS FILESTREAM ( NAME = N'HRMS_Account_Receipt', FILENAME = N'D:\HRMS_Account_Receipts\HRMS_Account_Receipt' )LOG ON ( NAME = N'HRMS_Account_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Account_log.ldf' ); GOTo demonstrate, run the following query to view the details of the database:OutputExample 3: Create a Memory-optimized (In-memory OLTP) DatabaseNext, is a code example for the In-Memory OLTP feature in SQL Server that is designed to improve the performance of transaction processing workloads by storing and processing data entirely in memory. You can read In-Memory OLTP overview and usage scenarios documentation to learn more.The syntax to create a memory-optimized SQL Server database is:CREATE DATABASE [DBName]ON PRIMARY (NAME = 'LogicalName_FG_Primary', FILENAME = 'Location_FG_Primary') FILEGROUP [MemoryOptimize_FG] CONTAINS MEMORY_OPTIMIZED_DATA(NAME = 'LogicalName_MemoryOptimize_FG', FILENAME = 'Location_MemoryOptimize_FG')LOG ON( NAME = N'LogicalName_LogFile', FILENAME = N'Location_LogFile')In the syntax:DBName: Specify the desired database name.LogicalName_fg_Primary: Specify the logical name of the data file of the Primary filegroup.Location_FG_Pary: Specify the location of the Primary filegroup data file.LogicalName_MemoryOptimize_FG: Specify the logical name of the file of the memory-optimized filegroup.Location_MemoryOptimized_FG: Specify the location where In-memory datafiles will be stored.LogicalName_LogFIle: Logical name of the transaction log file.Location_LogFile: Location of the transaction log file.Create Database Example for In-Memory OLTP DatabaseWe want to create a highly transactional memory-optimized database named HRMS_Patient_Registration. Specify the “CONTAINS MEMORY_OPTIMIZED_DATA” option with a name of memory optimized file group and its location in CREATE DATABASE statement.Execute this T-SQL query to create the HRMS_Patient_Registration database:CREATE DATABASE [HRMS_Patient_Registration]ON PRIMARY ( NAME = N'HRMS_Patient_Registration', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Patient_Registration.mdf', SIZE = 8192KB, FILEGROWTH = 65536KB ),FILEGROUP [HRMS_Patient_Registration_Memory_optimized_data] CONTAINS MEMORY_OPTIMIZED_DATA ( NAME = N'HRMS_Patient_Registration_IMOLTP', FILENAME = N'D:\HRMS_Patient_Registration_Memory_optimized_data\HRMS_Patient_Registration_IMOLTP' )LOG ON ( NAME = N'HRMS_Patient_Registration_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\HRMS_Patient_Registration_log.ldf', SIZE = 8192KB, FILEGROWTH = 65536KB );GO USE [HRMS_Patient_Registration];GO IF NOT EXISTS( SELECT name FROM sys.filegroups WHERE is_default = 1 AND name = N'PRIMARY') ALTER DATABASE [HRMS_Patient_Registration] MODIFY FILEGROUP [PRIMARY] DEFAULT;GONext, run the following query to view the details of the database:USE [master];GO SELECT name [Database Name], create_date [Database Create Date], compatibility_level [Database Compatibility Level], collation_name [default collation], user_access_desc [User Access], state_desc [Database State], recovery_model_desc [Recovery Model]FROM sys.databasesWHERE name = 'HRMS_Patient_Registration';GO USE [HRMS_Patient_Registration];GO SELECT DB_NAME() AS DatabaseName, df.type_desc [File Type], df.name [File Name], physical_name [File Location], state_desc [Database File status]FROM sys.database_files df LEFT JOIN sys.filegroups sf ON df.data_space_id = sf.data_space_id;OutputSummaryIn summary, we learned about SQL Server databases, specifically the

ProblemThe SQL Server In-Memory OLTP feature, also known as Hekaton, is an interesting in-memory processing technology that was introduced in SQL Server 2014 and optimized mainly for the Online Transaction Processing (OLTP) workload. As with any new feature, it came with a number of limitations and restrictions such as the ability to create Foreign Keys on the Memory-Optimized tables. Has this limitation been removed in SQL Server 2016?SolutionThe SQL Server In-Memory OLTP feature, with its exciting in-memory processing technology, enhances SQL Server querying performance in many scenarios, but the limitations and restrictions that came with this feature make it not feasible to use in many cases. In SQL Server 2016, a number of the In-Memory OLTP limitations have been removed, making this feature more useful. One of the limitations removed is the ability to create a Foreign Key on Memory-Optimized tables, which is very important to guarantee data integrity within these tables.In this tip, we will test the Foreign Key creation using two SQL Server instances, the first one is a SQL Server 2014 instance and the second one is a SQL Server 2016 instance, both containing the same MSSQLTipsDemo testing database. Let’s prepare the MSSQLTipsDemo database in both instances to host the memory optimized tables, by adding a new filegroup that contains MEMORY_OPTIMIZED_DATA, adding a new database data file on that filegroup and finally enabling the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT as shown in the below script:USE MSSQLTipsDemo GOALTER DATABASE MSSQLTipsDemo ADD FILEGROUP MemoryOpt_FG CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE MSSQLTipsDemo ADD FILE (name='MemoryOptDataDF', filename='D:\Data\MemoryOptDataDF') TO FILEGROUP MemoryOpt_FG ALTER DATABASE MSSQLTipsDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON GONow the MSSQLTipsDemo databases in both instances are ready to host the memory optimized tables. We will start by creating the EmployeeDep_MemOptTable as a simple memory optimized table that will act as the parent table on both instances:USE MSSQLTipsDemo GOCREATE TABLE [EmployeeDep_MemOptTable]( [DepID] INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_EmployeeDep_MemOptTable_Depid PRIMARY KEY NONCLUSTERED HASH (DepID) WITH (BUCKET_COUNT = 10000), [Dep_Name] nvarchar(10) NULL )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )GOThen trying to create the child Employee_MemOptTable memory optimized table that contains the Dep_ID column as a foreign key from the previous table. If we try to run the below CREATE TABLE script on the SQL Server 2014 instance:USE [MSSQLTipsDemo]GOSELECT @@VERSION AS SQLServerVersionGOCREATE TABLE [Employee_MemOptTable]( [ID] INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Employee_MemOptTable_id PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 10000), [First_Name] nvarchar(10) NULL, [FLast_Name] nvarchar(10) NULL, [Dep_ID] INT CONSTRAINT fk_Employee_MemOptTable_DepID FOREIGN KEY REFERENCES EmployeeDep_MemOptTable(DepID),)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )GOThe table creation will fail with the below error message, indicating that the Foreign Key is not supported with memory optimized tables:Trying the same CREATE TABLE script on the SQL Server 2016 instance, the command will execute successfully and the table will be created with no error, as the Foreign Key is supported now in SQL Server 2016 with memory optimized tables:We can also check the Foreign Key functionality by inserting two new records to the EmployeeDep_MemOptTable parent table:USE [MSSQLTipsDemo]GOINSERT INTO [dbo].[EmployeeDep_MemOptTable] ([Dep_Name]) VALUES ('IT'), ('HR')GOThen trying to insert

CROSS APPLY operator.If you don't specify a filter predicate, the entire table is migrated.When you specify a filter predicate, you also have to specify MIGRATION_STATE.MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }Applies to: SQL Server 2016 (13.x) and later, Azure SQL Database, and Azure SQL Managed Instance.Specify OUTBOUND to migrate data from SQL Server to Azure SQL Database.Specify INBOUND to copy the remote data for the table from Azure SQL Database back to SQL Server and to disable Stretch for the table. For more info, see Disable Stretch Database and bring back remote data.This operation incurs data transfer costs, and it can't be canceled.Specify PAUSED to pause or postpone data migration. For more info, see Pause and resume data migration -Stretch Database.[ DATA_DELETION = ON { ( FILTER_COLUMN = column_name, RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS } ) } ]Applies to: Azure SQL Edge onlyEnables retention policy based cleanup of old or aged data from tables within a database. For more information, see Enable and Disable Data Retention. The following parameters must be specified for data retention to be enabled.FILTER_COLUMN = { column_name }Specifies the column that should be used to determine if the rows in the table are obsolete or not. The following data types are allowed for the filter column.datedatetimedatetime2smalldatetimedatetimeoffsetRETENTION_PERIOD = { INFINITE | number {DAY | DAYS | WEEK | WEEKS | MONTH | MONTHS | YEAR | YEARS }}Specifies the retention period policy for the table. The retention period is specified as a combination of a positive integer value and the date part unit.MEMORY_OPTIMIZEDApplies to: SQL Server 2014 (12.x) and later, Azure SQL Database, and Azure SQL Managed Instance. Azure SQL Managed Instance does not support memory optimized tables in General Purpose tier.The value ON indicates that the table is memory optimized. Memory-optimized tables are part of the In-Memory OLTP feature, which is used to optimize the performance of transaction processing. To get started with In-Memory OLTP see Quickstart 1: In-Memory OLTP Technologies for Faster Transact-SQL Performance. For more

Is shared by many processes and users as a temporary working area. The default configuration is suitable for most workloads, but the installation process can help guide your configuration, as described in the Microsoft TempDB Database documentation If the server is dedicated to the MS SQL Server workload, then use either the default dynamic memory management model or follow Microsoft SQL documentation guidelines to manually configure memory options if you need more granular controlFigure 1. Lenovo ThinkSystem SR665 V3 Performance Testing Details and Results HammerDB ConfigurationHammerDB is an open-source database transactional and analytics load testing/benchmarking tool for databases. The OLTP workload is based on TPC® Benchmark C (TPC-C), and the Analytics workload is based on TPC Benchmark H (TPC-H). The HammerDB OLTP and Analytics workloads are open source workloads derived from the TPC-C Benchmark Standard and the TPC-H Benchmark Standard, respectively, and as such are not comparable to published TPC-C or TPC-H results, as the results do not comply with the TPC-C and TPC-H Benchmark Standards. The testing described below used HammerDB running on a separate server. Table 1. TPC-C and TPC-H performance testing details and results Database tested MS SQL Server 2022 Enterprise Edition Processors 2x 32-core AMD EPYC 9334 Hardware Configuration ThinkSystem SR665 V3, 2x AMD EPYC 9334, 1.5 TB memory, ThinkSystem PM1655 SSDs Benchmarks simulated TPC-C and TPC-H Database size: TPC-C 100GB 800 warehouses, distributed over 8 NVMe drives (6x DB, 2x Log) Database size: TPC-H 1000GB scale, distributed over 8 NVMe drives (6x DB and tempDB, 2x Log) OLTP run time parameters: TPC-C Virtual users 150 User delay 1 ms Analytics run time parameters: TPC-H Virtual users 7 Scale 1000GB Virtualized OLTP results Transactions Per Minute (TPM) 16 million (8 VMs) Analytics results Queries per Hour (QpH) 3805 Bill of Materials Table 2. Bill of Materials 7D9ACTO1WW Server: ThinkSystem SR665 V3 - 3yr Warranty 1 BLKK ThinkSystem V3 2U 24 x 2.5" Chassis 1 BREC ThinkSystem AMD EPYC 9334 32C 210W 2.7GHz Processor 2 BQ29 ThinkSystem SR665 V3 2U High Performance Heatsink 2 BQ3D ThinkSystem 64GB TruDDR5 4800MHz (2Rx4) 10x4 RDIMM-A 24 2212 Storage devices (custom RAID configuration) 1 BMFT ThinkSystem RAID 540-8i PCIe Gen4 12Gb Adapter 1 BNW6 ThinkSystem 2.5" PM1655 3.2TB Mixed Use SAS 24Gb HS SSD 8 B8LU ThinkSystem 2U 8 x 2.5" SAS/SATA Backplane 1 BT7N ThinkSystem Raid 5350-8i for M.2/7MM SATA Boot Enablement 1 BM8X ThinkSystem M.2 SATA/x4 NVMe 2-Bay Enablement