SQL Server

Entity Attribute Value (EAV) data model meets SQL Server Clustered Columnstore for dramatic performance improvement (Part #1) 2

<sarcasm>Want to have complete flexibility adding/removing attributes without DDL, store all your data in a single database table and still have performance then read on….</> It’s often the complaint of developers they can’t easily adjust the data model and add more attributes to it (columns), rightly or wrongly it happens and it’s a legitimate requirement. Ordinarily data […]

SQL Server Version Upgrade – Don’t follow the myth of migrating to the previous version! 3

I thought I’d put out some thoughts around why I think people who’d rather migrate to or start a project afresh with SQL Server [Previous] are potentially costing themselves a lot of pain, resource and thus cash in the longer term. I’ve been working with SQL Server and Windows since the very early days – […]

How Hekaton (XTP) achieves Durability for “Memory Optimised” Tables 3

“Memory Optimised” tables reside entirely in memory, the operations INSERT, UPDATE and DELETE as well as SELECT are all significantly quicker, but just how is it possible to achieve durability with memory optimised tables while still achieving the significantly higher throughput? Like traditional on-storage tables durability is achieved once the data has been written and hardened off […]

Changing In-Memory Table definition without down time 4

One of the restrictions of a “memory optimised” table is that you cannot modify its definition once created. You may find that once your table has been in use for a period of time it may be apparent you don’t need one of the Hash indexes for example, or perhaps you made a hash (pardon the […]

SQL Server bitmap operators, bitmasks and bit arrays 1

In this post I cover what bitmap and bitmasks are, before I can do that I need to make sure you are up-to speed on binary, base 2 and how the bits are layed out in data. Binary Binary (Base 2) is used throughout computer systems, the Windows platform amongst others relies on it. Base […]

SQL Server Hekaton (XTP) in-memory tables: Choosing the correct BUCKET_COUNT for a Hash Index 2

In this post I cover off how to choose the correct number for the BUCKET_COUNT and how you go about that, also, how to monitor and change the bucket_count. The general approach is that you set the BUCKET_COUNT to the number of unique values there will be given just the columns on your hash index (see […]

SQL Server Hekaton (XTP) in-memory Tables: Range Indexes and Row Chains 2

Hash and Range indexes both involve row chains, if you haven’t already read my post on Understanding the row chains of Hash Indexes I’d suggest you do before continuing with this post which essentially is a continuation of it and assumes you know the basics of row chains already. A range index is implemented using the […]

SQL Server Hekaton In-memory tables: Understanding the Row Chains of Hash Indexes 5

Having a good understanding of how the hashing and row chains work will go a long way in helping you design for performance and diagnose performance and resource issues you may get once live. This post covers off some of the basics and hopefully will give you a working insight. We’ll start with a Hash […]

Throughput improvement through Delayed Durability on COMMIT TRAN from SQL Server 2014 4

Durability is not a requirement of a relational database, you would term a database system as ACID compliant where the D in ACID is Durability, note – HBASE which sit’s upon HADOOP is ACID compliant! ACID applies to Transactions and not the prevailing database organisation method e.g. Relational, Key Value, Hierarchical etc. Back to SQL […]

Hekaton In-Memory Tables: HASH Indexes 2

The purpose of this post is to help you understand the new HASH indexing in SQL Server 2014 in-memory tables feature (project Hekaton). As ever, it’s actually quite a big topic so I’ll cover aspects in multiple posts (I’ll pop back here and update the links once complete)… Post 1 – How the Hash Index works Post […]

%d bloggers like this: