In many blogs or articles, we can read that it is recommended to recycle the error log to be sure that the size is not too big In the Best Practice Analyzer (BPA) for SQL Server, this is a rule. But what is a good size for the error log file? I have often heard “it depends”, but in my…
A couple of weeks ago, I had to provide some best practices in term of architecture for a “heavy writes” environment that consists in importing data from different sources into SQL Server tables. At the customer place, I was asked about interesting new SQL Server features that could be used to increase the performance speed of the data import process.…
SQL Server 2014 introduces hash indexes with in-memory optimized tables. I described some of their aspects in a previous blog post. These indexes are very efficient with lookup search operations but have some limitations with operations like range scans, inequality predicates or scan order operations. These limitations are linked to hash index design that stores rows in the index in…
Buffer pool extension (aka BPE) is an interesting feature of SQL Server 2014. Database administrators already know that memory is one of the key assets available for SQL Server. This is especially true for buffer pool memory, which plays an important role in storing data page and reducing the I/O demand. Nowadays, even if the memory has become cheaper, we…
A recurrent question I have often heard about Hekaton objects is the following: Is an accidental deletion of the compilation files of a hekaton table on the file system irreversible and could this compromise the execution of SQL Server? To check the SQL Server behaviour in such situation, we can perform the following test with an in-memory optimized table: CREATE…
Microsoft will introduce four new security permissions in SQL Server 2014. One of them called SELECT ALL USERS SECURABLES is the subject of this post. As explained by Microsoft SQL Server 2014 will allow a database administrator to manage data without seeing sensitive data or personally identifiable information. We can achieve a greater compliance but we must take care what…
For my first blog post at dbi services, I think it could be a good opportunity to begin by discussing around SQL Server 2014, Hekaton memory optimized tables, and hash indexes. When you create a memory optimized table you have to consider the number of buckets that you have to reserve for its associated hash index. It’s an important aspect…
Delayed Transaction Durability is a new SQL Server 2014 feature that was not included in SQl Server 2014 CTP1 but discreetly published in CTP2. It is a very interesting functionality that helps reducing the IO contention for transaction log writes. In other databases engine, you will find equivalents such as: Oracle: ‘COMMIT WRITE BATCH WAIT|NOWAIT’ (link here) MySQL: ‘group_commit’ since…
In-Memory OLTP is one of the best new functionalities of SQL Server 2014 as it was the case with AlwaysOn and SQL Server 2012. In-Memory OLTP is fully integrated with AlwaysOn and also with AlwaysOn Availability Group. During my test with AlwaysOn Availability Group under SQL Server 2014 CTP2, I discovered that a Readable Secondary Replica was not able to…
I was surprised that you have to rebuild indexes that are stored In Memory. My previous posting “SQL Server 2014 – New Features: xVelocity memory optimized columnstore index” explains all processes connected with columnstore indexes. In this article, I will explain why, when, and how to rebuild them. Why do we need to rebuild the Clustered Columnstore Indexes? When a…
While exploring SQL Server 2014, I was surprised by the powerful Power Query feature. In the recent past, converting an internet table to Excel was a nasty work leading to format problems, implicit conversions, etc. With Power Query, importing tables from the internet is very easy! What is Power Query? Power Query is a tool based on the concept of…
Some customers or colleagues recently asked me about the SQL Server concept xVelocity and in particular the SQL Server 2014 feature new xVelocity memory optimized columnstore index. So I decided to write a few words on the dbi services blog. What is xVelocity? Introduced in SQL Server 2012, xVelocity is composed of two features: xVelocity in-memory analytics engine xVelocity memory-optimized…
