Memory-Optimized Tables – Insert Test (continued)

Memory-optimized tables didn’t show much performance gain during the test last time. So I have decided to do more tests – just inserting tests and see how well memory-optimized tables perform.


Last time my VM had 2GB RAM and memory-optimized table could only hold 150K rows. To be able to test more cases, I added another 6GB and made it 8GB this time. In the meantime, I have also added 3 more logical CPU cores.


The tests I’m going to do this time is to compare insert performance of memory-optimized tables and disk-based tables under different workload: 50K, 100K, 150K, 200K, 250K and 300K row inserts. And I’m going to run these tests 3 times:


First run:

Second run:


Third run:


The results seem to be consistent – memory-optimized table showed performance gain only under 200K to 300K workload. For less heavy workload (<200K), disk-based table can perform just as well as memory-optimize table.


From this test, memory-optimized table seems to be working better under heavy workload. To prove this, I once again, added 4 GB to the testing VM and expand the test row set up to 450K. Again, 3 times:


First run:


Second run:

Third run:


The results are close – disk-optimized table can perform just as well as memory-optimized table under given workload. Unfortunately, I don’t have a power server handy for more tests. For now, I will consider memory-optimized table has no apparent performance gain over traditional disk-based table.

Memory-optimized Table – Day 1 Test

I had been checking Microsoft’s website since 12:00AM April 1st. Finally downloaded and installed it. After my SQL Server 2014 Enterprise Edition is up and running on my Windows Server 2012R2, I have decided to do some quick test:


First thing first, create a test environment:

use master;
create database db1; go

alter  database db1 add filegroup  IMOLTP contains memory_optimized_data;


alter  database db1 add file (name=‘imoltp’,filename=‘c:\db\imoltp’) to filegroup IMOLTP;


use  db1;


create table t1 (id int primary key, msg char(8000)); 


create table t1_oltp (id int not null primary key nonclustered hash with (bucket_count = 1000000), msg char(8000)) with (memory_optimized = on)



2 Tables are created, one is disk-based table called t1 and another one is memory-optimized table called t1_oltp. They have same definition so the results will be comparable.


Now, let’s run some tests:

Test #1: Insert Operation:


declare  @start datetime2;select @start = getdate(); set statistics time on


declare @n int = 1;

while (@n < 1000000)


    insert into t1 values (@n,replicate(‘a’,8000)) 

    set @n = @n +


select datediff(ms,@start,getdate())


declare @start datetime2; 

select @start = getdate();

declare @n int = 1;

while (@n < 1000000) 


insert into t1_oltp values (@n,replicate(‘a’,8000)) 

set @n = @n + 1


select datediff(ms,@start,getdate())



The plan was to insert 1M rows and compare the performance. However, it turned out to be I was way too optimistic about the capability of my VM. SQL Server showed this error while inserting:


It’s good that SQL Server actually has a hard limit (80%) on how much memory memory-optimized tables could use. This helps SQL Server to ensure memory-optimized tables won’t consume all SQL Server has and leave other tables starving to death.


Now I will have to reduce the amount of record for this test, I’m going to be conservative this time – 50K records is what I will be testing on.


The results are:

Disk-base table: 33323ms

Memory-optimized table: 124017ms


Surprisingly, memory-optimized table is much slower compare to disk-based table. MSDN explicitly mentioned high insert rate is one of the scenarios we should consider memory-optimized table:

“High data insert rate

One of the critical needs of high performance applications is an extremely high data loading rate. The typical pattern is a need to ingest data where due to either high rate fluctuations and spikes, the workload requirements exceed the capabilities of a traditional RDBMS. Some business cases which may use this solution include smart metering, or repositories for telemetry or performance data ingestion.”


This test makes no sense. Before I start investigating what happened to my insert test, I decided to continue finish the rest of my test.


Test #2: Select Operation – Table Scan:

set  statistics time onselect * from t1
select * from t1_oltp
set statistics time off




(49999 row(s) affected)


SQL Server Execution Times:

CPU time = 2047 ms, elapsed time = 27617 ms.


(49999 row(s) affected)


SQL Server Execution Times:

CPU time = 1703 ms, elapsed time = 59434 ms.


Here we see some performance improvement.



Test #3 Select – Seek:

set  statistics time on select * from t1 where id = 25000
select * from t1_oltp where id = 25000
set statistics time off



(1 row(s) affected)


SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 44 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.


(1 row(s) affected)


SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 37 ms.


Hard to tell, both are lightning fast! However, notice that there is difference, disk-based table has an entry called “parse and compile” time while memory-optimized table doesn’t. This is one of the benefits of compile object SQL Server 2014 brings.


Test #4: Delete:

set  statistics time on delete from t1 delete from t1_oltp

set statistics time off


SQL Server Execution Times:

CPU time = 13328 ms, elapsed time = 24345 ms.


(49999 row(s) affected)


SQL Server Execution Times:

CPU time = 219 ms, elapsed time = 208 ms.


(49999 row(s) affected)


Here we see a big difference, 98% less in CPU time, WOW!


Test #5: Bulk load:


Create a staging table for bulk load:

–staging table
create table t2 (id int primary key, msg char(8000));godeclare @n int = 1;

while (@n < 50000)


    insert into t2 values (@n,replicate(‘a’,8000))

    set @n = @n + 1



Now let’s load them:

set statistics time on
insert into t1
select * from t2 insert into t1_oltp

select * from t2

set statistics time off



SQL Server Execution Times:

CPU time = 9765 ms, elapsed time = 18497 ms.


(49999 row(s) affected)


SQL Server Execution Times:

CPU time = 1235 ms, elapsed time = 39432 ms.


(49999 row(s) affected)


To review the tests we have done so far:

Disk_based Table_Memory_based_table

Except for my suspicious insert operation and my miscalculation about the number of 50K rows (49,999 rows instead), all results are positive and performance friendly. Just keep in mind memory-optimized tables are memory monsters and they do consume A LOT memory and default resource pool has a hard limit on how much memory-optimized tables can use, that is 80%.


And, I will look into what happened to my insert test in my next blog.

Is your hard drive ready for SQL Server

Back in the age of Windows Server 2003, there was a best practice to align disk partitions for SQL Server for performance gain:


This seems old, right? Come on, we are using SQL Server 2014 on Windows Server 2012 R2 now!


Well, yesterday I happened to come across this white paper again and wanted to check one of our new servers and guess what – the cluster size on this new box is using default 4096 bytes instead of 64KB which is a best practice:

(Screenshot was taken on my VM, not the actual production server)

I have decided to blog this so I can remind myself in the future – change the cluster size for SQL Server!

SQL Server Data Compression – Uncompressable Data

Data compression is a tool to improve query performance by reducing physical IO reads and writes. It can only reduce IO reads and writes when it saves space and it normally does so. I say normally because just like other performance tools it can hurt performance in some cases. As I mentioned earlier, SQL Server provides 2 types of data compression – page compression and row compression. Both of these two types of compression will consume additional space when they are implemented, normally this is called metadata overhead. The final space saving is the difference between saved space from compressed data and consumed metadata overhead space. Therefore, when metadata overhead space is greater than saved space, implementing data compression will expand actual storage space.


To demonstrate the growth of data space after implementing data compression, I engineered the following scenario.


Let’s first create a table. This table will have only one char column. Normally row compression will be able to save data space by eliminating used blank spaces, however, in this case, I intentionally left no space for row compression.

create database DCTest;


use DCTest;

create table table1 (msg char(36));


Once the table is created, I populate it with 1 million rows of fixed length random data:

–insert 1 million random records in table1

insert into table1 select NEWID()

go 1000000

Run the estimate stored procedure with both page compression and row compression and notice that the estimates show space growth:

–Get estimate on both row compression and page compression. Estimate shows table1 will grow after compresssion

exec sp_estimate_data_compression_savings ‘dbo’,’table1′,null,null,’row’

exec sp_estimate_data_compression_savings ‘dbo’,’table1′,null,null,’page’

For demonstration purposes, let’s ignore the estimates and continue implementing data compression:

–Get a baseline:

exec sp_spaceused table1

–Test row compression

alter table table1 rebuild with (data_compression = row)

exec sp_spaceused table1

–Test page compression

alter table table1 rebuild with (data_compression = page)

exec sp_spaceused table1

After executing this script, table1 now consumes more space than it did before.


Below is a screenshot I took from my test environment:


One last step before we close this topic.

–Clean up

drop table table1;

use master;

drop database DCTest;

I have lost my SA password…

Have you or your coworker ever forgotten your sa password for your SQL Server and you can no longer log into your SQL server? Strangely, it had happened to me twice in last few months.

If you find yourself in the same situation, read this before you start screaming.

When we lose access to SQL server, it’s possible to connect to SQL Server using your Windows local admin. The trick is to run SQL Server engine under single user mode.

Now let’s take a look at how we can get our server back.

Before we begin, we need to have a user who is in local admin group. For demonstration purposes, I created SQLUser. Notice, SQLUser is a Windows user, it doesn’t have a SQL Server login.

After we log in as SQLUser, first thing we do is to stop SQL server engine:

Net Now we need to start SQL Server engine in single user mode. We could do it with /m parameter:

OK, now we are ready to connect to SQL Server, Use SQLCMD to login:

Perfect! We are in. Now we can use ALTER USER  <USERID> WITH PASSWORD = <NEWPASSWORD> to change sa or any login password.

Welcome back, my server!

.Net Debugging – Conditional Break Points

Often developers need to setup breakpoints for debugging. There are times we need to stop execution under certain condition. I used to add logic in my code using following logic

if (i == 1)


    //break here

    i= i;


This works well for debugging, however, it’s a pain to keep my code clean. And, sometimes I forget to clean them!

A few years back, I learned Visual Studio had build-in functions for this type of situation call conditional breakpoints. I have showed to a few coworkers how to use it and now I want to blog it for whoever might see it. It’s a very simple technique yet power in debugging.

Below, I’m showing this in VS2008:

To start, make sure Breakpoint window is open in Visual Studio, if not, press Ctrl. D,B to open it.

In source code, press F9 to setup break point at the line. This break point will show in ‘Breakpoints’ window, right click on this breakpoint, there will be a pop-up menu like this:

Select ‘Condition’, conditional break point setup window will appear. All we need to do is to enter the condition

After setup is done, run the code and it will break when condition is met: 

That’s all.

There is another way to break: hit count, you can enter a number and Visual Studio will stop code when this line of code has been hit for xx many times:

 See how easy it is?

Breakpoint doesn’t change much in Visual Studio 2010:

VS2012 just came out, I haven’t got chance to check it out. Will update once I get chance.