Hello! Easter is here already; we’re 1/4 of the way through 2015, the clocks have gone forward, and for the first time in a long time it was warm enough for me to venture outside without a hat.

April Fools Came A Day Early

On the 31st of March, I made a decision: This here blog of mine is hosted on Wordpress, and the theme I use is the default Twenty Eleven, modified with some CSS. I’ve been happy with the look of it for some time, but I decided that a change was necessary and so upgraded to the Twenty Fifteen theme. As my CSS changes would be redundant, I disposed of them knowing that the Wordpress site would keep a history of the changes. And, being the eternal tinkerer that I am, I started customising via CSS again. I had finally got it changed to my liking before I realised that there was no search feature on the Twenty Fifteen theme! Seeing as I use the search myself quite a bit, and with no real alternative, I decided to change back. However, to my dismay, the history of the custom CSS only goes back a certain amount of revisions, certainly less than the number of changes I had made. Disaster! What a fool I was. Of course I had no backup, or a copy, so I was faced with either putting it back together, but I’m no web developer, and the original custom CSS was hacked together via trial and error, not actual knowledge!

Fortunately, I had a Plan B: using the Way Back Machine, I was able to travel back in time to the customised theme I used and using the “inspect elements” feature in Firefox, find the custom css and paste it back in. This was a total bacon saver, and so by way of thanks I donated some money to the good cause they do over there. And so the next time I think about tinkering with the site, I’m going to remember this experience, and also that no one but me cares about how it looks!

The Microsoft Surface Pro 3 Sucks!

No, it doesn’t really. It’s too early from my purchase of a Surface Pro 3 to make such a conclusion, but I like what I have experienced so far.

For me, I’m not that bothered about using it as a tablet. My Xperia Z2 is large enough to act as a tablet. What I really wanted myself was a laptop that weighed very little, fitted in my TimBuk2 XS Messenger Bag, and ran Windows.

I got the 256GB i5 version because I didn’t think the i7 would bring the benefits for the price. I would however have been tempted to get the 512GB version, because components in the Surface cannot be upgraded and you can never have too much storage. A benefit of the Surface is that it has a SD card slot to expand the storage by up to 128GB. However, some of the reviews warned that the SD reader was “slow”. But just how slow is “slow”? It’s pretty clear that a SD card won’t compete with the PCI-e SSD found in the Surface, but is it acceptable for maybe running some databases on? Using Crystal Disk Mark, I ran some tests agains the built in SSD on the Surface, a Class 10 32GB Micro SD card plugged into the Surface, and a 7200 RPM SATA 3 HDD on a separate desktop.

These are the results from the HDD:

This is the SSD:

And finally, the Micro SD:

Clearly we can infer that writing to the SD is appalling whatever the conditions. Under read the results are not much better, but the drop off in read performance is not nearly as much as the HDD drop off. Performance between the SD and SSD is that the SD is about 10 - 20* worse over most tests and up to 70-240* worse on 2 of 8 of the tests.

But how does the translate into real world usage? It took just under 3 minutes to transfer a 1.85 GB mp4 file from the SSD to the Micro SD card, and the top transfer time corresponded to our result above: 13.1mb /s. Playback of the video was also fine. Even playback of a 4K video recorded off my Xperia was as good as the SD card.

How does the SD Card cope with a SQL Workload

I’m going to loop through this script a variety of times by altering @i and record how long it takes. It’s a simple insert script into a table.

--**************************************
-- Table definition
--**************************************

create table [dbo].[CciDemo](
[Id] [int] IDENTITY(1,1),
[WatchDate] [date] NOT NULL,
[associateId] [bigint] NOT NULL,
[OsKey] [varchar] (4) NOT NULL,
[value] [int] NOT NULL
)
GO

--**************************************
-- Insert the rows
--**************************************

declare @i as int;
set @i = 1;
begin tran
while @i <= 2345678
begin
insert into dbo.CciDemo
([WatchDate], [associateId],[OsKey],[value] )
values
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13),
( GETDATE(), RAND ()*12, 'FFOS', RAND ()*13)
set @i = @i + 1;
end;
commit;

--**************************************
-- Creating our Clustered Columnstore Index
--**************************************
create clustered columnstore index CCI_CciDemo
 on dbo.CciDemo;
GO

(The Y axis is time in seconds, X axis is total inserts)

Finally, we’ll create a Clustered Columnstore Index on a table with 10,000,000 rows. It took 43 seconds on the Micro SD database, and 11 seconds on the SSD database. Clustered Columnstore creation is intensive on memory, processor and storage, but clearly the bottleneck here was the storage. How this scales, the tests don’t really show.

It’s pretty clear that the Micro SD card can cope with a small workload and be as efficient as the SSD; perfect for dev or a bit of simple examples. However the time taken when we’re talking bigger operations scale far worse on the Micro SD over the SSD. However, you may want to keep videos/music on the Micro SD and change the default paths for these as the Micro SD card has no performance issues with media.

If you want to read some proper in depth reviews of the Surface Pro 3, then I urge you to read the following:

Scott Hanselman: My initial impressions of a Surface Pro 3

Scott Hanselman: Using Surface Pro 3 For 2 Months

Paul Thurrott: Surface Pro 3 Review - Third Time’s The Charm This review is my favourite, with links to many different in depth analysis on features.

AnandTech: Microsoft Surface Pro 3 Review

When I’ve spent more time with the machine I’ll post an update on how I have found it. But the story so far: Yes, 1 USB isn’t enough, yes the dock is expensive for what it is, but both of these can be solved by purchasing a 3 in 1 USB adaptor that comes with an ethernet port for about £11 ($15), and is infinitely more portable than the dock. The screen is gorgeous, the keyboard works well, and though I hate trackpads, this one I can live with until I remember to pack my spare mouse.

Finally

I’m writing this on April Fools, and so it would be remiss if I were not to post this tweet:

https://twitter.com/SQLRockstar/status/486219835165388801

Happy Easter!