Welcome

Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein



Tuesday, 10 December 2013

Which Chart to Use

Choosing the right chart is important when displaying results as the charts tell a story.

This diagram provides a great starting place for decisions on which type of chart to use. Whether it be for comparison, distribution, relationship or composition.


http://extremepresentation.typepad.com/files/choosing-a-good-chart-09.pdf

Tuesday, 12 November 2013

SQL Relay R2



The second SQL Relay event this year was kicked off in Reading. I again was pleased to help out at this event. Microsoft opened with the Cloud OS Vision. The premise of how it unlocks insights on any data, transforms the datacentre, empowers people-centric  IT and enables modern business applications.  The future is about hybrid IT, and with the data explosion and rapid evolution being underway a rapid lifecycle is required.   There is a continuous offering from private to public cloud with various taxonomies towers; Physical, Virtual, IaaS, PaaS and SaaS.

There was a session on the new engine taken from this paper, SQL Server 2014 In-Memory OLTP (“Hekaton”)Whitepaper for CTP2 by Kalen Delaney.

An interesting sessionwas delivered positioning Enterprise Information Management including MDS, DQS and Integration Services. For clarity these services were defined as
DQS (Data Quality Services) is a knowledge driven data quality solution enabling IT pros and data stewards to easily improve the quality of their data
MDS (Master Data Services) is the set of data objects at the centre of business activities (customers, products, cost, locations, assets, tasks etc.)

There were many other great sessions during the day.  

Thursday, 17 October 2013

PASS Summit 2013 Keynote: Hekaton Why, What, and How


The day 2 keynote was excellent from David DeWitt.  The main differences of Hekaton, In-Memory Databases is that it is memory optimized but durable, a very high performance OLTP engine, fully integrated into SQL Server 2014 and architected for modern CPUs. 

Other products on the market with in memory technology are Oracle TimesTen, IBM SolidDB and Volt VoltDB, but  SAP Hana is not a direct competitor.

This new technology is not the same as the old functionality of pinning a table in memory. In the current core engine there is the need for concurrency control, locks and latches and interpreted query plans.

In the 1980’s the database field explorer had the use of many different concurrency control (CC) mechanisms. The technique (due to Jim Gray) known as 2 phase locking emerged as the standard. There are a variety of lock types including X(eXclusive) and S(shared). There are two simple rules to follow. Before access, the query must acquire an appropriate lock types from the lock manager and once a query releases a lock, no further locks can be acquired.  Jim Gray won the ACM Turing Award for his work on transaction processing. 

A comparison between the two engines and the required 3 components.


   













In SQL Server 2014 it uses lock free data structures invented by Maurice Herlihy at Brown University, who was elected to National Academy of Engineering. The first phase is to create a memory optimised table. Then the concurrency control components are:

Optimistic
Conflicts are assumed to be rare
Transactions run to completion without blocking or setting locks
Conflicts are detected during a validation phase
Multiversion
Updating a row creates a new version of the row
Timestamps
Each row version has an associated time range
Transactions use their begin timestamps to select correct version
Timestamps  are also used to create a total order for transactions to obtain equivalent of a serial order. 

Query execution in Hekaton has 2 alternatives modes:

Regular T-SQL access ‘Interop’
Queries can access and update both Hekaton and disk resident tables
Interpreted execution limits performance
Complied Stored Procedures ‘Native
T-SQL => C code => machine code (dll)
MUCH faster, but some language surface restrictions with V1



 













The slide deck can be obtained from  http://gsl.azurewebsites.net/People/dewitt.aspx and I would recommend looking at this  excellent presentation.

More details on the new SQL Server feature
Getting Started with SQL Server 2014 In-Memory OLTP
Hardware Considerations for In-Memory OLTP in SQL Server 2014
Architectural Overview of SQL Server 2014’s In-Memory OLTP Technology