In the interests of curiosity I’m going to take a query that runs a relatively simple aggregation over a large table and see how much I can reduce the IO. I’m not suggesting anything here should be blindly followed, as with all things there are trade-offs. but the results are I think interesting none the less. Disks are getting faster and cheaper all the time, however no amount of progress in this area will ever give you free IO, the cheapest IO will always be the IO you don’t make. If we can tune our query to do less it will often give a far better bang for buck than any advancements in hardware.
If you want to follow along the Stack Overflow database I’m using can be downloaded from here.
Let’s take this fairly simple query on the Stack Overflow database to get the top 5 users by posts and return their name along with the amount of posts they have…
On my machine the statistics output I get is this…
SQL Server Execution Times: CPU time = 7294 ms, elapsed time = 7178 ms.
So roughly 7 seconds to execute the query with our biggest hit on reads being 799999 on the Posts table and 121 on Users. I should probably add that at this point both our posts and users table have a single index and that’s a clustered index on their ID columns. Let’s start on the small but easy low hanging fruit….
Lightweight Covering Index
The user table is currently scanning the clustered index to get the DisplayUsername, because the clustered index contains all the fields it’s having to read data we don’t need, we can create a lightweight covering index with just Id and an include on DisplayName to stop the query reading any pages with data in them that it doesn’t need.
It’s a small gain but we’ve halved the reads required for our join to the users table.
Now to tackle that posts table, this is a bit more difficult as we have to touch every record in order to group and count. An obvious place to start is to create a nonclustered index with a sort order more suited to our aggregation and without any of the fields we don’t need…
SQL Server Execution Times: CPU time = 1157 ms, elapsed time = 738 ms.
For me, the query now runs in less than a second and our reads on the posts table have gone from 799999 down to 6539. We could happily stop here but in the interest of this post, I wanted to see how much further I could take it.
We’re now at the point where our query is reading only information it absolutely needs in order to complete, so how can we reduce reads further? Compression!
We have a couple of options here, we can compress our indexes with Row or Page level compression or we can change tracks a little and use a Columnstore index. Let’s compare these options…
First up lets set our posts index to use page compression…
SQL Server Execution Times: CPU time = 1457 ms, elapsed time = 695 ms.
That’s just over another 2000 reads knocked off and about 200ms faster. We’ve traded off reads for CPU a little here as the compression/decompression process will add overhead on the processor.
Let’s now drop our compressed index and try a Columnstore index…
I’ll leave you to read more about Columnstore elsewhere but just know they work great on large reporting tables with lots of duplication, because of the way they store data a lot of duplication in the storage is removed.
SQL Server Execution Times: CPU time = 187 ms, elapsed time = 276 ms.
Clearly, our read counts have shot up here, whilst we only read 6382 pages (Similar to our non compressed index) 22818 were pre-fetched in anticipation that we might need them as can be seen in the “lob read-ahead reads”. So in the interest of just trying to reduce reads, our columnstore was a failure, however I should also add that this query ran in less than 300ms being more than twice as fast as our previous compressed covering index. The compression of a Columnstore index will vary massively depending on how much duplication you have in your data, the more duplication the more compression you will see.
We’ve created lightweight indexes to reduce the data touched, we’ve compressed them to reduce IO and we’ve tried Columnstore for it’s aggregation and compression wizardry. So what next? This one feels a bit like cheating but we can harness indexed views to pre-calculate our aggregations and automatically manage them going forwards…
We’ll need to make a couple of changes to our query to get it to use the indexed view and return the results we need…
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 283 ms.
22 reads! Jackpot? Maybe, the indexed view is a bit of a cheat as it’s just moved the IO to the writes rather than reads. Depending on how read or write heavy your system is you may or may not see this as a worthwhile tradeoff. Something to highlight here that I think is often missed is whilst the clustered index on an indexed view has a lot of restrictions, once you’ve created it you can create a restriction free nonclustered index (not unique, non grouped fields etc).
Any thing I’ve missed? Do you have other tricks for lowering IO? Let me know in the comments or drop me a message on one of the social networks in the header/footer.
You know that old SQL Server you’ve left running the last 5 years and had numerous databases dropped and restored to? Have any databases been detached/restores failed part way through and data files just been left behind unused?
Depending on how many databases you have it can be a bit of a pain to go through the files in each one and compare that to the files in your data/log directories. I recently wrote a bit of a hacky script to do just this and list any MDF/LDF files that are not attached to a database on a given instance. It is a bit of a hack and requires you have xp_cmdshell enabled which I’ll leave you to read about separately and I would definitely not advise enabling it just for this purpose. But if you have it enabled the script may serve some use to you. If you have chosen the enable xp_cmdshell then the following code will turn it on…
To get a list of unused data files set the path in the below script to be the location of your data/log or a directory above them (It searches subdirectories)
Any filenames returned from the above are not attached to any databases on the instance you are connected to.
Indexing on In Memory OLTP tables is a little different from your traditional on-disk rowstore tables…
In Memory Differences…
There is no clustered index
The nonclustered index still exists but its structure is quite different.
There is a new hash index ideal for unique single record lookups
Below I’m going to go over these points with demos, these demos are all run on a data dump of the Stack Overflow Database from 2010 which can be downloaded from BrentOzar.com. They all revolve around creating the following in memory table and copying the relevant fields out of the Stack Overflow user table to test out different indexing strategies…
No Clustered Index
In Memory OLTP table indexes are just pointers to the in memory data structure for the row and all its columns, this means that from any item in an index you have full access to all the columns in the row with no lookups needed. This effectively makes a nonclustered index on an in memory table act like a clustered index in that it will be a covering index for any query that uses it.
These are quite different on in memory tables to on disk tables.
If you query an on-disk table index the query will via a seek or a scan end up on a leaf node which will contain the fields in the index and an identifier (RID or clustered index key) back to the underlying table for lookups to any additional fields the query needs.
In Memory indexes have eliminated the need for locking due to the way they work, traditional indexes use a B-Tree whereas In Memory indexes are something the SQL Server team coined as BW-Tree, you can read more about BW-Trees in this research paper.
The end result is much the same as the indexes you know and love with classic on-disk nonclustered indexes. One thing to note is that in memory table schema cannot be altered once it is created so and all indexes must be created inline with the table creation. Let’s create our in memory people table with a clustered index on Id (Primary Key) and Location…
Lets then test our a couple of queries…
Notice anything odd about that plan? Yup, no key lookup. It’s used the index ix_people_location which only has ID on it and no include fields yet we’ve managed to do a SELECT * with no key lookups! Result!
‘Lets look at another query…
Again the exact same plan but this time we’ve returned 100 records with no key lookups, probably not surprising given the previous query but I just wanted to highlight it anyway.
Lastly, let’s look at a query with multiple matches on a single predicate
Basically the same plan again with the only difference we’re now going against the location index.
Now let’s look at something else that does behave quite differently…
Looks ok right, it’s returned the ordered data using our location index with no sort needed. Well, what happens if we want it descending?
Hmmm, Not Good. It’s performed a manual sort even though we have an index on the field we want sorted, an on disk nonclustered index would have no trouble with this. This is because on disk indexes use a doubly linked list to scan leaf nodes (Allowing 2 way traversal) whereas in memory tables do not. If you need to return data ordered in more than one direction then you need to define multiple indexes to store the data ordered.
New Hash Index
The Nonclustered index works great for finding ranges of records but when the SQL Server team were changing so much with In Memory OLTP they also designed a new type of index to speed up seeks to single unique records, e.g find me the record with id x. It’s actually just what it sounds like, SQL Server will hash the fields in the index and store that hash along with a pointer to the in memory location of the row(s).
Hash indexes perform great when the index is unique or very close to it, if however you start to get lots of records in the index hashing to the same value inserts and seeks and updates can slow down massively and you should think about using a nonclustered index instead.
When you define a hash index you have to tell it how many hash buckets you want, this should be a figure 1-2* the amount of unique values you plan to have in the index. A hash bucket defines how many possible values SQL Server will calculate when hashing your index. For example, if you define a hash bucket count of 10 will only ever give 10 possible hashes even if you store a million different values, in this case each bucket will end up with something like 100K rows which will all need to be scanned when you are seeking a record. Ideally, you want a single record per hash bucket then you can just hash the predicate in your query and go directly to the record. Something to watch out for here is that the values within a hash bucket are stored in order (This makes scans within a bucket faster) so if you under define your bucket count and end up with a many rows in a hash bucket then inserts\updates can take a big hit as once they’ve found the correct hash bucket they need to then scan through it to find the correct place to insert\move new row.
Because hash indexes are really designed for retrieving single rows they do not worry about sorting the data so if you’re doing anything that requires a range of data to be returned a hash index is not a good fit e.g WHERE x > y.
With the above in mind lets try a single record lookup, First, recreate the People table with a hash index on the ID field…
Now lets run our single record lookup from before…
Pretty much the same query plan as the non clustered version however if we compare the cost of this one it’s gone from 0.0004596 to 0.0000175 so as expected the hash match for single record on a properly sized bucket far outperforms the nonclusterd index.
With the above information, you should be armed with everything you need to create optimized indexes on your in memory tables.