First of all, I’d like to point to the fact that there is almost always a bottleneck with OLTP database’s performance that is related to disk speed. So in-memory processing can improve the current poor performance with disk-based tables significantly as well as achieving scalability.
In a very simple and quick explanation, Hekaton is new feature coming with SQL Server 2014 to handle in-memory processing for OLTPs. Actually it is something live xVelocity for OLAP. But, how Hekaton can help for improving OLTP performance and scalability? The answer is that SQL Server 2014 is trying to achieve these goals by using
Optimised algorithms to access memory-resident data
Eliminating logical locks using a concurrent control method for RDBMS (Relational Database Management Systems) that assumes that multiple transactions can complete without affecting each other, so that, the transactions can commit without locking the source data. This is called Optimistic Concurrency Control (OCC).
Using lock-free objects to access all data, so threads that perform transactional work do not use locks for concurrency control.
Using a new concept in SQL Server 2014 called Native Compilation. Native compilation refers converting programming constructs to native code, which consists of processor instructions that can be executed by the CPU without the need for further compilation or interpretation. So, SQL Server can natively compile stored procedures that access memory-optimized tables. Native compilation allows faster data access and more efficient query execution, than traditional, interpreted Transact-SQL.
Microsoft claims that “Use of main memory can result in performance gains from a few percentage points of performance improvement, to 20 times performance improvement.”
In SQL Server Installation Centre click on “Installation”
Click on “New SQL Server stand-alone installation or add features to an existing installation”
Note that if you have any of previous versions of SQL Server installed on your machine the setup process will fail because this release of SQL Server cannot be installed in until the existing instances of SQL Servers are uninstalled.
If you click on “Failed” you can read the complete error message that is “Previous SQL product installation failed. A SQL product other than SQL Server 2014 CTP1 is detected. You cannot install this release until the existing instances of SQL products are uninstalled.”
What does it actually mean that the database doesn’t have a database owner?! It seems odd, but, it is possible. In some cases that the database is created by an application it really happens that the database doesn’t have any database owner. For instance, the databases generated by CRM Deployment Manager doesn’t have database owner. So what if we had a bunch of databases in the SQL Server that doesn’t have any database owners? Okay, let me explain. I’ve faced to a situation that we needed to create some database diagrams for many databases and all of those databases where CRM databases created by CRM Deployment Manager. Hence, when we were trying to generate a database diagram an error message were raising saying the database doesn’t have DB owner. So it was very time consuming if we wanted to define a DB owner for each database individually using the GUI. The following code will define a particular database owner for the databases server wide. (In our situation it was a service user.)
One of the most exciting features in this version is a project code-named “Hekaton” that provides in-memory OLTP capabilities. As Hekaton is built into core SQL Server database it will improve the performance of the database applications significantly. It is more interesting that Hekaton is installed with SQL Server 2014 engine that means no additional action is needed to use the benefits of in-memory processing without rewriting the database application. It is really awesome that it can increase performance of existing database application without having to upgrade the hardware. Microsoft claims that Hekaton is easy to deploy and allows to access the other features in SQL Server, while taking the advantage of in-memory performance.
In my previous post called “How to drop tables database wide using T-SQL” I’ve explained that how we could delete all tables database wide in SQL Server. But, what if there are some other schemas other than “dbo” and some tables that are created in those schemas? If we just delete “[dbo].” from the code we will obviously face to the following error message: “Cannot find the object “TABLE_NAME” because it does not exist or you do not have permissions.” for those tables that are not under “dbo” schema. So, we need to retrieve all schemas and their related tables. This approach is really helpful when we are implementing an ETL process that is designed to delete the tables from particular schemas and recreate the tables and populate new data. This is a common way for staging to delete and recreate tables instead of updating existing tables as update is really costly.
There are some cases that we need to drop all tables database wide. For example when we have a test database and we need to delete all tables from the database and recreate some other tables for testing we need to drop each table manually using the GUI. Assume we have 50 tables in the database, so, we need to drop the tables one-by-one and repeat the process 50 times. Actually it is getting harder when there are some relationships between the tables!
So, what should we do now? Okay, we need to drop all dependencies first and the drop all 50 tables! It’s frustrating, isn’t it?
SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):
a. Uses relational modelling constructs like such as tables and relationships
b. Uses xVelocity in-memory analytics engine for sorting and data calculations
c. Needs to use SSDT (SQL Server Data Tools) to implement
d. Can import data from relational data sources using OLE DB native and managed providers
e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.
f. support DAX calculations, DAX queries, and MDX queries
g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)
h. It might be not a good choice for the systems that are going to load terabytes of data