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 . Microsoft Data Platform MVP

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



Showing posts with label SQL Pass. Show all posts
Showing posts with label SQL Pass. Show all posts

Sunday, 10 June 2018

24 Hours of PASS Summit Preview 2018

Free Microsoft Data Platform training is to be held 12 June 2018 starting at 12:00 UTC. It is a great opportunity to hear some amazing sessions in 24 hours, of 1 hour back to back content.

Topics covered in this edition include Performance Tuning, Azure Data Lake, Digital Storytelling, Advanced R, Power BI and more! Join in on sessions from Kendra Little, Melissa Coates, Rob Sewell, Brent Ozar, Dejan Sarka, Devin Knight, Mico Yuk and many more experts in their fields.

Friday, 6 November 2015

SQL Pass 2015 Day 2 Keynote

The SQL Pass 2015 day 2 keynote by David DeWitt and Rimma Nehme of Microsoft was on IoT.

There are various technical challenge to consider about IoT:

  • variety of sensors
  • number of devices 
  • dirty sensor readings
  • data volume / scale
  • device / sensor security
  • cloud to device messaging
  • set up deployment and maintenance of large sensor networks
 The Azure IOT Services that currently exist are









Azure Stream Analytics can be used for streaming the real time IoT



The Metadata for this is not currently stored in a DBMS so querying of the data is not possible. IoT is a database problem not just a networking problem.






Rimma explained about the Fog. It is the cloud plus the fog. This in database terms is predicate pushdown. Database 101 is to never move the data to the computation always move the computation to the data.






 An interesting set of ideas about Polybase for IoT where discussed covering the declarative language, complex object modeling, scalable metadata management, discrete & continuous queries, multi purpose querying and computation pushdown.

The final picture about the future.


Tuesday, 28 April 2015

SQL Saturday Exeter 2015



SQL Saturday Exeter was held Apr24-25 2015 at Jurys Inn Hotel in Exeter. I was excited to be helping at the event for the community again.  There were various excellent training day sessions. 

The Saturday keynote was the most amazing fun keynote I have seen and a highlight of the event. The video was by Phil Factor about Spinach and Database Development. 

Here a few of the points contained within the video on Spinach:

What has spinach got to do with Database Development? Generations of children were fed spinach in preference to more nutritious things, such as cardboard, because of the persistence of bad data.

Guarding the quality of your data is about the most important thing that you as a data professional can do. You may think that performance is important but it would just deliver you the wrong answer faster. Resilience? it would just make it more likely that you’d be able to deliver the wrong answer. Delivery? Yep you got it, the wrong answer quicker.

A good database professional must be unequivocal.

Statistics can help us to make very confident assertions about large datasets if they conform to one of the common distributions but they cannot tell us anything about individual items of data. You can of course remove outliers but in fact outliers are just items of data that don’t conform to your assumptions about the data and the whole point of data analysis is to test your assumptions. By cleaning data, by removing outliers you can prove almost anything scientifically.

I would recommend watching the keynote.


 




Thursday, 19 June 2014

SQL Server 2014 Training with 24 hours of PASS


Another 24 hours of PASS  is scheduled on 25 and 26 June covering end to end SQL Server 2014 topics.


The Sessions cover these topics



Session 01: Field Testing Buffer Pool Extension and In-Memory OLTP Features in SS2014
Session 02: SQL Server 2014: In-Memory OLTP Overview
Session 03: SQL Server 2014: In-Memory OLTP for Database Administrators
Session 04: SQL Server 2014: In-Memory OLTP End-to-End: Preparing for Migration
Session 05: SQL Server 2014: In-Memory OLTP for Database Developers
Session 06: AMA: Open Questions and Answers on SQL Server 2014 In-Memory OLTP
Session 07: The Modern Data Warehouse
Session 08: SQL Server 2014: Private and Hybrid Cloud Features
Session 09: AlwaysOn in Microsoft SQL Server 2014
Session 10: Columnstore Technical Deep Dive
Session 11: Columnstore Indexes in SQL Server 2014: Flipping the DW Faster Bit
Session 12: Migration and Deployment for SQL Server in Azure Virtual Machines
Session 13: AMA: Open Questions and Answers on SQL Server 2014 HA-DR in the Cloud
Session 14: Designing Modern Apps Using New Capabilities in Azure SQL Database
Session 15: Introduction to Microsoft Analytics Platform System (APS)
Session 16: SQL Server 2014 Upgrade: A Case Study Featuring In-Memory OLTP and Hybrid-Cloud Scenarios
Session 17: Patterns and Practices for Scaling Microsoft Azure SQL Database
Session 18: AMA: Open Questions and Answers on Azure SQL Database
Session 19: SQL Server 2014: In-Memory OLTP Performance Troubleshooting
Session 20: SQL Server 2014 In-Memory OLTP: Memory/Storage Monitoring and Troubleshooting
Session 21: SQL Server 2014: Changes to Query Plans
Session 22: AMA: Open Questions on SQL Server 2014, Microsoft Azure, and Big Data



24hours of Pass recordings on SQL Server 2014 are now availabile

 

Thursday, 17 October 2013

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

[I attended this presentation. The original links to material have been removed as these no longer exist]

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 is no longer available from its original source.  

More details on the new SQL Server feature was in these articles listed below (links are now archived and not available so they have been removed):
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