SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server vs SQL Server v.Next

By Henrico Bekker,

So, you ask "Why?" 

Well, firstly, because it is fun knowing.  In this short article, I'll try to convince you that SQL Server on Linux (v.Next) should not be discarded as a performance orientated database solution. Yes, it still comes with limitations, but I firmly believe Microsoft is onto something here.

For this test, I won't be discussing any feature comparison, license or support costs, v.Next is still in its infancy and should be allowed to be developed to its full potential as a complete Enterprise market offering.

The testing was done on the hardware available to me, 2 x laptops. Not the most comprehensive test, but for most sceptics, this should indicate something, and hopefully change views on the feasibility of using SQL Server on Linux from a performance point of view and start some converations and tests of your own.

Hardware specificiations of laptops used and SQL configuration:

Configurations
OS Physical Memory CPU HDD SQL Version SQL Maximum Memory CTfP
Windows 10 64 16GB
Core i7-5600 @2.6GHz
Toshiba 512GCSU SSD
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) 
10240
50
Linux (Ubuntu 16.10)
16GB
Core i7-4700 @2.4GHz
Samsung 850 SSD
Microsoft SQL Server v.Next (CTP1.2) - 14.0.200.24 (X64) 
                              10240 50

Note: The hardware is very similar, though some might argue the performance of the Toshiba is below that of the Samsung. What the SSD on the Windows instance lacks, it gains with it's faster CPU. 

Here's what we'll do:

  1. Create a test database, with a test table
  2. insert 999 999 rows, 3 times - raw, not indexes, just plain bad row inserts
  3. perform some updates to the data sets
  4. create an index on the table 
  5. review statistics and times

:

We have a very simple table, with 4 columns with generated characters. The database and table are similar in design, with no specific autogrowth settings or configurations applied. Here is the script.

create database test
go

use test
create table testimports
(column1 int, column2 varchar(100), column3 varchar(100), column4 varchar(100), column5 bit, column6 datetime)

go

For the data loads, we'll go ahead and load the 3 x runs of 999 999 rows each

declare @int int
set @int = 1

while @int < 1000000

 begin
 insert into testimports values
 (@int, LEFT(CAST(NEWID() AS VARCHAR(100)), 99), LEFT(CAST(NEWID() AS VARCHAR(100)), 99), LEFT(CAST(NEWID() AS VARCHAR(100)), 99),
 1, getdate())
 set @int = @int+1

end

Next we perform some updates. Bad queries and updates are a reality, so let's be real. We'll go ahead and replace every "D" with "%":

UPDATE [dbo].[testimports]
SET column2 = REPLACE(column2, 'D','%')

Next, we delete some rows with a LIKE '$4%'.

delete from [dbo].[testimports]
where column3 like '%4%' 

Finally, we'll go ahead and create a clustered index on these tables.

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndexSQLSERVER] ON [dbo].[testimports]
([column1] ASC)WITH 
(PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
GO

Alright, I feel confident I've done enough bad coding, and bring my instruction testing to a quick conclusion.

Results!

Test

Platform

Run1

Run2

Run3

Insert 3 x999 999

SQL Server

3min17sec

3min28sec

3min38sec

SQL v.Next

1min58sec

2min33sec

3min06sec

Updates

SQL Server

9 Seconds

4 Seconds

4 Seconds

SQL v.Next

6 Seconds

3 Seconds

3 Seconds

Delete

SQL Server

10 Seconds

SQL v.Next

7 Seconds

Index Creation

SQL Server

3 Seconds

SQL v.Next

< 0 Seconds

SQL v.Next was the winner in each section, but what was interesting was during the 999 999 row inserts, SQL Server got slower consistantly by 10 seconds, while v.Next was slower by a rate of 30 seconds at each run, meaning, if another 2 INSERT runs were done, v.Next would have been slower by 8 seconds for run 5, and slower by 28 seconds at run 6.

A quick look proved that v.Next is by no means slower out of the box, and gave a full SQL Server a run for its money, and I am looking forward to what comes next. 

 
Total article views: 2142 | Views in the last 30 days: 6
 
Related Articles
FORUM

Tips on creating indexes

Tips on creating indexes

FORUM

Dynamically Creating Indexes

Dynamically Creating Indexes

FORUM

Creating an index

Creating an index

ARTICLE

Create, Alter, and Drop Indexes: Stairway to SQL Server Indexes Level 12

Options and impacts when creating, altering and dropping an index

FORUM

basic guidelines on creating Indexes

basic guidelines on creating Indexes

Tags
linux    
performance    
sql    
vnext    
 
Contribute