The inescapable problem of Latency within Data-based systems

It’s long troubled me people touting Real-Time Business Intelligence when its physically impossible to achieve true real-time; I’ve jotted down some thoughts in an attempt to bring order and better understanding to the reality of “Real-Time” – hopefully the post will help you in dealing with vendors, giving you some insight into what questions to […]

TSQL Smells in SSDT – The monster lives

What seems like many many moons ago, I released a powershell script that could detect many different “TSQL Smells“. Whilst it worked, I was never very happy with the interface, it was clunky and didnt feel quite ‘right’. One of my longer term aims was to do something about that and make TSQLSmells more accessible. […]

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

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

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

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

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

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 […]

Reading a snapshot – Are there performance implications ?

My present role is to look at the performance of a large datawarehouse load.  This is right up my alley,  and I have be playing with all manner of performance tuning fun, indexing, execution plans, filtered statistics etc, all good stuff. However,  during QA testing some of my suggestions actually made things worse.  Not a little worse, but a […]

What is Hashing; using Modulus to partition data

Hopefully this post goes some way in helping the reader understand better what hashing, hash indexes are and the need for row chains with In-memory Tables (Hekaton) in SQL Server 2014 hash indexes. Purpose of hashing? Hashing can be used to index character data, instead of building an index on a varchar(50) column for example, […]

Manchester and Leeds user groups – 16/17 Oct

Thanks to all those that turned out of Wednesday and Thursday night in Leeds and Manchester to hear my presentation on Estimation and Statistics.  The ‘lucky’ Manchester crowd also got to hear ‘Trace Flag fun’, a deep dive into optimizer internals. As promised here are the slides and scripts:   Estimation and Statistics TraceFlag Fun […]