By: Rajendra Gupta | Comments (1) | Related: > SQL Server 2016
Problem
In my previous tip about downloading and using the new SQL Server 2016 sample databases we saw that Microsoft launched new sample databases to help you learn more about SQL Server 2016. Along with the sample databases, they also introduced new tools to mimic sample workloads to further explore the functionality. In this tip we will explore these sample workload tools.
Solution
The sample workload tools are provided to understand the functionality and benefits in SQL Server 2016. There are two sample workloads provided which are Windows Form applications that will only run on Windows and can be download here. The two workload tools are Order Insert and Vehicle Location Insert.
Prepare SQL Server Test Environment
Before we start using this application, we need to create schemas, tables and stored procedures in a test database which is included in the downloads. For this example I am using the new WorldWideImporters database.
Validate SQL Server In-Memory Support and Configure Database
The script below validates you are using the correct version and edition of SQL Server and then ALTERs the database for testing.
SET NOCOUNT ON; SET XACT_ABORT ON; -- 1. validate that In-Memory OLTP is supported IF SERVERPROPERTY(N'IsXTPSupported') = 0 BEGIN PRINT N'Error: In-Memory OLTP is not supported for this server edition or database pricing tier.'; END IF DB_ID() < 5 BEGIN PRINT N'Error: In-Memory OLTP is not supported in system databases. Connect to a user database.'; END ELSE BEGIN BEGIN TRY; -- 2. add MEMORY_OPTIMIZED_DATA filegroup when not using Azure SQL DB IF SERVERPROPERTY('EngineEdition') != 5 BEGIN DECLARE @SQLDataFolder nvarchar(max) = cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar(max)) DECLARE @MODName nvarchar(max) = DB_NAME() + N'_mod'; DECLARE @MemoryOptimizedFilegroupFolder nvarchar(max) = @SQLDataFolder + @MODName; DECLARE @SQL nvarchar(max) = N''; -- add filegroup IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = N'FX') BEGIN SET @SQL = N'ALTER DATABASE CURRENT ADD FILEGROUP ' + QUOTENAME(@MODName) + N' CONTAINS MEMORY_OPTIMIZED_DATA;'; EXECUTE (@SQL); -- add container in the filegroug IF NOT EXISTS (SELECT * FROM sys.database_files WHERE data_space_id IN (SELECT data_space_id FROM sys.filegroups WHERE type = N'FX')) BEGIN SET @SQL = N'ALTER DATABASE CURRENT ADD FILE (name = N''' + @MODName + ''', filename = ''' + @MemoryOptimizedFilegroupFolder + N''') TO FILEGROUP ' + QUOTENAME(@MODName); EXECUTE (@SQL); END END; END -- 3. set compat level to 130 if it is lower IF (SELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID()) < 130 ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130 -- 4. enable MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT for the database ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON; END TRY BEGIN CATCH PRINT N'Error enabling In-Memory OLTP'; IF XACT_STATE() != 0 ROLLBACK; THROW; END CATCH; END;
Drop Objects If They Exist in the SQL Server Database
The script just cleans up objects in case they already exist, so we can do the testing.
DROP PROCEDURE IF EXISTS InMemory.Insert500ThousandVehicleLocations DROP PROCEDURE IF EXISTS InMemory.InsertVehicleLocation DROP PROCEDURE IF EXISTS OnDisk.InsertVehicleLocation DROP TABLE IF EXISTS InMemory.VehicleLocations DROP TABLE IF EXISTS OnDisk.VehicleLocations DROP SCHEMA IF EXISTS InMemory DROP SCHEMA IF EXISTS OnDisk GO
Create SQL Server Objects for Disk Based Testing
The below statements create the schema, table and stored procedure for the disk based testing.
-- We then create the disk based table and insert stored procedure CREATE SCHEMA OnDisk AUTHORIZATION dbo; GO CREATE TABLE OnDisk.VehicleLocations ( VehicleLocationID bigint IDENTITY(1,1) PRIMARY KEY, RegistrationNumber nvarchar(20) NOT NULL, TrackedWhen datetime2(2) NOT NULL, Longitude decimal(18,4) NOT NULL, Latitude decimal(18,4) NOT NULL ); GO CREATE PROCEDURE OnDisk.InsertVehicleLocation @RegistrationNumber nvarchar(20), @TrackedWhen datetime2(2), @Longitude decimal(18,4), @Latitude decimal(18,4) WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; INSERT OnDisk.VehicleLocations (RegistrationNumber, TrackedWhen, Longitude, Latitude) VALUES (@RegistrationNumber, @TrackedWhen, @Longitude, @Latitude); RETURN 0; END; GO
Create Objects for SQL Server In-Memory Based Testing
The below statements create the schema, table and stored procedure for the in-memory based testing.
-- And then in-memory and natively-compiled alternatives CREATE SCHEMA InMemory AUTHORIZATION dbo; GO CREATE TABLE InMemory.VehicleLocations ( VehicleLocationID bigint IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, RegistrationNumber nvarchar(20) NOT NULL, TrackedWhen datetime2(2) NOT NULL, Longitude decimal(18,4) NOT NULL, Latitude decimal(18,4) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); GO CREATE PROCEDURE InMemory.InsertVehicleLocation @RegistrationNumber nvarchar(20), @TrackedWhen datetime2(2), @Longitude decimal(18,4), @Latitude decimal(18,4) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English' ) INSERT InMemory.VehicleLocations (RegistrationNumber, TrackedWhen, Longitude, Latitude) VALUES (@RegistrationNumber, @TrackedWhen, @Longitude, @Latitude); RETURN 0; END; GO
Insert data into SQL Server disk based table
SET STATISTICS IO ON GO -- Note the time to insert 500 thousand location rows using on-disk DECLARE @RegistrationNumber nvarchar(20); DECLARE @TrackedWhen datetime2(2); DECLARE @Longitude decimal(18,4); DECLARE @Latitude decimal(18,4); DECLARE @Counter int = 0; SET NOCOUNT ON; WHILE @Counter < 500000 BEGIN -- create some dummy data SET @RegistrationNumber = N'EA' + RIGHT(N'00' + CAST(@Counter % 100 AS nvarchar(10)), 3) + N'-GL'; SET @TrackedWhen = SYSDATETIME(); SET @Longitude = RAND() * 100; SET @Latitude = RAND() * 100; EXEC OnDisk.InsertVehicleLocation @RegistrationNumber, @TrackedWhen, @Longitude, @Latitude; SET @Counter += 1; END; GO
Insert data into in-memory based SQL Server table
SET STATISTICS IO ON GO -- Now insert the same number of location rows using in-memory and natively compiled DECLARE @RegistrationNumber nvarchar(20); DECLARE @TrackedWhen datetime2(2); DECLARE @Longitude decimal(18,4); DECLARE @Latitude decimal(18,4); DECLARE @Counter int = 0; SET NOCOUNT ON; WHILE @Counter < 500000 BEGIN -- create some dummy data SET @RegistrationNumber = N'EA' + RIGHT(N'00' + CAST(@Counter % 100 AS nvarchar(10)), 3) + N'-GL'; SET @TrackedWhen = SYSDATETIME(); SET @Longitude = RAND() * 100; SET @Latitude = RAND() * 100; EXEC InMemory.InsertVehicleLocation @RegistrationNumber, @TrackedWhen, @Longitude, @Latitude; SET @Counter += 1; END; GO
To insert 500K rows using the disk based table it took 00:08:29 and for the in-memory table it took 00:05:17. This clearly shows the performance improvement using in-memory. Note: For this example I used my laptop which has an Intel R Core (TM) i5 4200U CPU @1.60 GHz with 8 GB RAM, so if you perform this on a server with faster hardware the improvement may be even more significant.
Workload Tool 1 - Vehicle Location Insert
The forms application is named MultithreadedInMemoryTableInsert.exe and allows us to test a sample workload using On-Disk vs. In-Memory for a comparison. Below are the configuration options:
- Connection String - The server and database related configuration can be done in the connection string provided.
- Number of Threads - This is how many threads should be run at a time
- Number of Rows Per Thread - This is how many rows are inserted per thread
- Table Insertion Type - Either On Disk or In Memory
The default value for Number of Threads is 120 and Number of Rows Per Thread 1000, so let's run this with default values for both on disk and in memory. Click Insert to run.
Here we can see the Last Execution Time was 15973ms for On-Disk.
Here we can see the Last Execution Time was 8848ms for In-Memory which is much faster.
Increase Threads and Run Again
Now lets increase number of threads. We can see the On-Disk goes to 24411ms and In-Memory to 13284ms. We can clearly notice the difference between on-disk and in-memory.
Workload Tool 2 - Order Insert
This application is used to provide an intensive order entry workload for the WideWorldImporters database. I have included several runs of this to show has this performs.
Increase Threads and Run Again
We can see the average order insertion time based on the number of threads. We can try to further tune the queries to improve performance.
Next Steps
- Note: These workload apps should be explored in the non-production environments.
- Download and explore SQL Server 2016
- Check out SQL Server 2016 Tips
- Read more about WideWorldImporters OLTP database
- Explore more about SQL Server In-Memory OLTP Tips
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips