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