Technical: Microsoft – SQL Server – v2014 (In Memory Table) – Benchmark Simple Queries

 

 

Technical: Microsoft – SQL Server – v2014 (In Memory Table) – Benchmark Simple Queries

 

Background

Comparing Simple SQL Server queries against traditional and in-memory tables.

 

Generate Data to query for

We will like our test queries to request data that is representative of our existing data. And, as such we will get a sampler of existing data.

This is easy to do once one discovers MS SQL Server tablesample keyword.

 

bcp "select distinct top 100000 quotename(firstname, '\"') as firstname, quotename(lastname, '\"') as lastname, quotename(emailaddress, '\"') as emailAddress, quotename(convert(varchar(10), dateofBirth, 101) , '\"')  as dateofBirth from   [dbo].[personTraditionalClustered] tablesample (100000 rows)  with (nolock)" queryout e:\tmp\sampleData.txt -c  -S (local) -d DBLabInMemory -T -t ","


 

Please note the following:

  • Quest Benchmark factory likes for strings to be quoted and as such we will use quotename to specify that our chosen strings and date columns be surrounded in double quotes
  • We are using (tablesample 100000 rows) to randomly get one hundred thousand rows

 

If your strings are not quoted, you might get an error that resembles the following.

IfFileContentsAreNotQuoted

 

Not quite descriptive nor informational, but an error is an error.

 

Sample Data



"Ezekiel","Brennan","Wallace@risus.edu","01/10/1994"
"Henry","Crane","Craig@ut.org","04/23/1963"
"Ulla","Workman","Chaney@vestibulum.org","10/10/1955"
"Ralph","Gamble","Avye@erat.edu","06/03/1929"
"Diana","Vang","Ori@mattis.gov","07/31/1921"
"Kasper","Sanford","Ori@morbi.edu","06/05/1975"
"Nicholas","Daniel","Joshua@posuere.net","01/19/1903"
"Erich","Howe","Kirestin@placerat.com","07/24/1969"
"Piper","Hill","Austin@id.gov","12/30/2012"
"Baxter","Key","Channing@placerat.org","01/29/1900"


 

Prepare Microsoft SQL Server

There are a couple of areas that we should cover prior to load testing.

 

Microsoft SQL Server Stored Procedures

Here are the Stored Procedures that we will use to retrieve data.

Stored Procedure – Traditional


if object_id('dbo.usp_Traditional_getPerson_FilteredOnName') is not null
begin
	drop procedure dbo.usp_Traditional_getPerson_FilteredOnName
end
go

create procedure dbo.usp_Traditional_getPerson_FilteredOnName
(
	@firstname varchar(40)
)
as

	select
			   [firstname]
			  ,[lastname]
			  ,[emailAddress]
			  ,[phoneNumber]
			  ,[addressStreet]
			  ,[addressCity]
			  ,[addressState]
			  ,[addressPostalCode]
			  ,[dateofBirth]
			  ,[uniqueID]
		from   [dbo].[personTraditionalClustered] tblPerson
		where  tblPerson.firstname = @firstname


end

go 

Stored Procedure – dbo.usp_Traditional_getPerson_FilteredOnUniqueID


if object_id('dbo.usp_Traditional_getPerson_FilteredOnUniqueID') is not null
begin
 drop procedure dbo.usp_Traditional_getPerson_FilteredOnUniqueID
end
go

create procedure dbo.usp_Traditional_getPerson_FilteredOnUniqueID
(
     @uniqueID uniqueIdentifier
)
as
 select 
    
       [firstname]
      ,[lastname]
      ,[emailAddress]
      ,[phoneNumber]
      ,[addressStreet]
      ,[addressCity]
      ,[addressState]
      ,[addressPostalCode]
      ,[dateofBirth]
      ,[uniqueID]

 from [dbo].[personTraditionalClustered] tblPerson

 where tblPerson.UniqueID = @uniqueID


go


Stored Procedure – Memory Optimized



if object_id('dbo.usp_InMemory_getPerson_FilteredOnName') is not null
begin
	drop procedure dbo.usp_InMemory_getPerson_FilteredOnName
end
go

create procedure dbo.usp_InMemory_getPerson_FilteredOnName
(
	@firstname varchar(40)
)
with native_compilation, schemabinding, execute as owner
as

	begin atomic
	with (transaction isolation level=snapshot, language=N'us_english')


	select
			   [firstname]
			  ,[lastname]
			  ,[emailAddress]
			  ,[phoneNumber]
			  ,[addressStreet]
			  ,[addressCity]
			  ,[addressState]
			  ,[addressPostalCode]
			  ,[dateofBirth]
			  ,[uniqueID]
		from   [dbo].[personInMemory] tblPerson
		where  tblPerson.firstname = @firstname


end

go




 

Stored Procedure – dbo.usp_InMemory_getPerson_FilteredOnUniqueID


if object_id('dbo.usp_InMemory_getPerson_FilteredOnUniqueID') is not null
begin
     drop procedure dbo.usp_InMemory_getPerson_FilteredOnUniqueID
end
go

create procedure dbo.usp_InMemory_getPerson_FilteredOnUniqueID
(
     @uniqueID uniqueIdentifier
)
with native_compilation, schemabinding, execute as owner
as

 begin atomic
 with (transaction isolation level=snapshot, language=N'us_english')


      select 
          [firstname]
         ,[lastname]
         ,[emailAddress]
         ,[phoneNumber]
         ,[addressStreet]
         ,[addressCity]
         ,[addressState]
         ,[addressPostalCode]
         ,[dateofBirth]
         ,[uniqueID]
     from [dbo].[personInMemory] tblPerson
     where tblPerson.UniqueID = @uniqueID

end

go




Quest Benchmark Factory

Scenarios

In Quest Benchmark, we reference the generated sample data file by using BFFFileArray. Please keep in mind that it allows us to reference each column individually by specifying its position in the file.

 

Scenario SQL
SQLStmt_FirstName_Traditional select *
from [dbo].[personTraditionalClustered] tblPerson
where tblPerson.firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
 SQLStmt_FirstName_InMemory select *
from [dbo].[personInMemory] tblPerson
where tblPerson.firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
SQLStmt_UniqueID_Traditional select *
from [dbo].[personTraditionalClustered] tblPerson
where tblPerson.uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’
 SQLStmt_UniqueID_InMemory select *
from [dbo].[personInMemory] tblPerson
where tblPerson.uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’
SQLSP_FirstName_Traditional  exec dbo.usp_Traditional_getPerson_FilteredOnName
@firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
 SQLSP_FirstName_InMemory  exec dbo.usp_InMemory_getPerson_FilteredOnName
@firstname = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,1)’
SQLSP_UniqueID_Traditional exec dbo.usp_Traditional_getPerson_FilteredOnUniqueID
@uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’
 SQLSP_UniqueID_Traditional exec dbo.usp_InMemory_getPerson_FilteredOnUniqueID
@uniqueID = ‘$BFFileArray(“E:\tmp\sampleData.txt”,RANDOM,5)’

 

 

Latencies

Here is how we configured latency for each of our Transactions:

LoadSetup-Latency

Explanation:

  • We are setup for zero delay (No Delay).  In a whole lot of cases, one adds Think Time and the like, but since we know we are employing the Free Version of Quest Benchmark and we can only go up to 20 users, and not 100 users as the actual product allows, we wanted to stress the system a bit

 

 

 

SQL Server – Benchmark – Profiler

In certain cases, it is important to review the client Application interaction with SQL Server and glean how the queries are being submitted.

SQL Statements

SqlServerProfiler-SelectStatement

 

Stored Procedure Statements

SQLServerProfiler-StoredProcedureExecutionThroughExecStatement

 

SQL Server – Benchmark – Results

Here is the results of running the benchmark.

Summary

QuestBenchMark-ResultsSummary

Graph

Response Time vs Userload

 

QuestBenchmark-SQLServerCompare

Tabular

 

ID Scenario Response Time in millisecond for 20 Userload (with 1 being best)
1 SQLStmt_FirstName_Traditional 6  ( 31 milliseconds )
2  SQLStmt_FirstName_InMemory 5  ( 27 milliseconds )
3 SQLStmt_UniqueID_Traditional 2  ( 3 milliseconds )
4  SQLStmt_UniqueID_InMemory 4  ( 6 milliseconds )
5 SQLSP_FirstName_Traditional 6  ( 31 milliseconds )
6  SQLSP_FirstName_InMemory 8  ( 38 milliseconds )
7 SQLSP_UniqueID_Traditional 3 ( 4 seconds)
8  SQLSP_UniqueID_InMemory 1 ( 0 seconds )

 

 

Explanation:

  • The queries that filters on UniqueID, a uniqueidentifier a column, are the fastest
  • In the traditional table we are able to create clustered indexes and we created one on the UniqueIDs column.
  • The query on the in-memory table against the unclustered hash index did better than the one against the traditional table with a clustered indexed
  • Due to our tool’s usage of prepared statements, we did not pay a price on SQL Statements

 

SQL Server – Query Plan

Fetch on Name

Query

Here is what our query on name looks like:

declare @firstname varchar(40)

set @firstname = 'David'

exec dbo.usp_Traditional_getPerson_FilteredOnName
	   @firstname = @firstname

exec dbo.usp_InMemory_getPerson_FilteredOnName
	   @firstname = @firstname

 

Estimated Execution Plan

Traditional Table:

FecthOnNameAgainstTraditional

 

In-Memory Table:

FetchOnNameAgainstInMemoryTable

 

Explanation:

  • The query plan for in-memory is a bit simpler
  • For In-Memory, it consists of a single index seek
  • For Traditional, it does an index-seek and a clustered-index lookup to get the additional columns we are querying for, and a nested loops inner join to combine the results of the two
  • Interestingly, the traditional query is also suggesting that we create a covering index to avoid the two index retrievals

Statistics I/O

Table 'personTraditionalClustered'. Scan count 1, logical reads 3554, 
physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical 
reads 0, lob read-ahead reads 0.

Explanation

  • The traditional table registers I/O metrics, but not so for in-memory tables

 

 

Fetch on UniqueID

Query

Here is what our query on name looks like:

declare @uniqueID uniqueIdentifier

set @uniqueID = newid()

exec dbo.usp_Traditional_getPerson_FilteredOnUniqueID
	   @uniqueID = @uniqueID

exec dbo.usp_InMemory_getPerson_FilteredOnUniqueID
	   @uniqueID = @uniqueID

 

Estimated Execution Plan

Traditional Table:

FecthOnUniqueIDAgainstTraditional

 

In-Memory Table:

FecthOnUniqueIDAgainstInMemory

 

Explanation:

  • The query plans are very similar
  • Fo traditional table, we have a clustered index seek
  • For In-Memory table, we have an index seek
  • Not so sure that SQL Server is properly costing In-Memory queries

 

Statistics I/O

Table 'personTraditionalClustered'. Scan count 1, logical reads 3, 
physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical 
reads 0, lob read-ahead reads 0.

Explanation

  • The traditional table registers a very small I/O metric, but not so for in-memory tables

 

 

SQL Server – Wait Stats

In-Memory

Activity Monitor

For in_memory selects, here are our wait stats

ResourceWaits

 

Explanation:

  1. Table access is performed via SQLCLR
  2. Return of selected data is noted as Network I/O

 

Dynamic Management View

Using the stolen query https://danieladeniji.wordpress.com/2013/06/19/technical-microsoft-sql-server-wait-stats-cx-packet/, here is raw wait type data:

waitStatsDMV

Explanation:

  1. Again, CLR_AUTO_EVENT

 

Summary

Queries targeting UniqueIdentifier datatype column seem to run consistently better than those that are targeting the varchar datatype.

For in-memory tables because we can only create indexes on BIN2 columns.  As Binary means binary and does not support case-insensitive searches, for actual business use-cases, workarounds will have to be understood and diligently employed.

In a follow-up posting will like to compare In-Memory searches where an index is available against ones where indexes are not created.

I think our Tool choice is not fully supporting Stored Procedure calls and it is treating them as Prepared calls.

 

References

References – Microsoft – SQL Server – Memory Optimized DB

References – Quest Benchmark Factory

References – Microsoft – SQL Server – Transact SQL – Table Sample

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s