Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Sample Workload Tools


By:   |   Read Comments (1)   |   Related Tips: More > SQL Server 2016

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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 SQL Server disk based table

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


Insert data into in-memory based SQL Server table

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.

Multi threaded In Memory Table Insert

Here we can see the Last Execution Time was 15973ms for On-Disk.

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.

Last Execution Time was 8848ms for In-Memory

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.

Increase the number of threads to 200 for disk based tables


Increase the number of threads to 200 for in memory table

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.

order entry workload test with 10 threads

Increase Threads and Run Again

order entry workload test with 15 threads

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



Last Update:


signup button

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, December 01, 2016 - 10:40:18 AM - Sarada Back To Top

 

 

In  cross database and distributed transactions setup.  Instead of using availability groups, if we use always on with failover cluster, will cross DB transactions and distributed transaction (MSDTC) work. If we implement always on without availability group, will these transactions work. If so which version of SQL server will support and setup we need to configure. Pls suggest.

 

 


Learn more about SQL Server tools