Transitioning of SQLOS to SQLPAL

​The SQLOS​

Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on Windows OS to manage hardware resources

​. The hardware resource management includes the following;

• ​
  ​Processor scheduling
• Memory management
• Network 
• Disk I/O​

In SQL Server 2005, the hardware resource management part has been incorporated into SQL Server as a separate component known as SQLOS (SQL Server Operating System). The objective of this initiative was to improve SQL Server performance by assigning database own tasks manage itself, so that SQL Server can perform better. The SQLOS then acts as a non-preemptive schedule to service database own tasks by cooperating with Windows OS. As a result, SQL Server knows how to manage the hardware resources better than Windows OS. 

The SQLPAL

SQLPAL-SQL Platform Abstraction Layer, is an extended version of SQLOS to facilitate platform independent behaviour with the introduction of SQL Server on Linux initiative. To achieve this objective Microsoft has used a research project called, Drawbridge. Drawbridge uses essentials Windows components inside SQLPAL to interact with SQL Server and then SQLPAL will interact with the underlying OS. Essentially, SQL Server on Linux still thinks SQL Server is working on Windows. See below Figure;

The SQLPAL interact with underlying OS (Windows or Linux) through its host extension implementation. So host extension has the actually platform specific implementation for Windows OS as well as Linux OS. The biggest advantage of this innovative approach is, you did not need to rewrite SQL Server where more than 20+ years of development happened on Windows and contains thousands of millions of code. 

​How about the performance?

The obvious doubt is, adding an additional abstraction layer (SQLPAL) will negatively impact the performance?​ The testing shows that SQL Server performs almost the same against both the OS. In fact, the performance of SQL Server 2017 is far better than SQL Server 2016. 

​Need to know more: Click here​

Cheers!

SQL Server 2017 becomes the first commercial RDMS with built-in AI support

AI Into SQL Server 2017

Today (4/19/2017), Microsoft announced the release of SQL Server 2017 CTP 2.0 (formerly known as SQL Server vNext). It has pretty exciting features including deep learning AI capabilities. Deep learning model demonstrated today at Microsoft Data Amp is used some medical data to figure out the probability of getting a lung cancer for a particular patient. Faster early detection of such potential disease helps to reduce the healthcare cost and also to have a healthier life. All these happens under one roof meaning, in one single data platform which is SQL Server. All the analytics capabilities of the deep learning system are now integrated into SQL Server which makes super easy for data scientists to work and analyze data. The application has to call just a single stored procedure to do the deep learning analytics of a patient, it's that much simple.

The data, the model, and the intelligence are all built into SQL Server which will eliminate so many challenges of building today’s AI application by using so many different types of tools.

Python Integration

SQL Server 2017 has a built-in support for Python code inside T-SQL queries which gives so much powerful programming capabilities. SQL Server 2016 supported R integration as well. You can use sp_execute_external_script system stored procedure with T-SQL to call R or Python language which external to T-SQL.

Adaptive Query Processing

Adaptive Query Processing is one of the extremely powerful database engine enhancements in SQL Server 2017. With this new feature SQL Server has the intelligent to learn itself about query processing and improve itself and generate better execution plan based on past query execution with no human intervention. This is an amazing feature that I was dreamed for years.

SQL-Graph

Graph data represents many-to-many relationships and typically relational databases need to store that kind of relationships as one-to-many relationships. SQL Server now supports graph data and processing of graph data. You can create graph objects which create nodes and edges in SQL Server 2017. This essentially extends the SQL Server query processing capabilities. This query capability is fully integrated into SQL engine in SQL Server 2017. Previously, you had to use a special type of databases called graph databases to store this kind of data.

One platform which supports both Windows and Linux

All the features of SQL Server 2017 now support both Windows and Linux platforms.

In a nutshell, SQL Server is not just another database server, it is an extremely powerful data platform eco-system because it combines AI and analytics into one product. Everything is built in. So much easier than using hundreds of different tools. I can’t wait to get some hands-on of these new features.

Cheers!