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…

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

On my machine the statistics output I get is this…

Table ‘Posts’. Scan count 5, logical reads 800230, physical reads 0, read-ahead reads 797892, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Users’. Scan count 0, logical reads 121, physical reads 39, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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.

CREATE NONCLUSTERED INDEX ndx_user_id_include_displayname 
	ON Users(Id) INCLUDE(DisplayName)

Now let’s try our query again…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Users’. Scan count 0, logical reads 69, physical reads 21, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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…

CREATE NONCLUSTERED INDEX ndx_posts_owner_userId
	ON Posts(OwnerUserId)

Then let’s try our query again…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Posts’. Scan count 5, logical reads 6539, physical reads 0, read-ahead reads 11, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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!

Compressed Indexes

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…

ALTER INDEX ndx_posts_owner_userId 
	ON Posts REBUILD WITH (DATA_COMPRESSION = PAGE)

Then let’s try our query again…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Posts’. Scan count 5, logical reads 4137, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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.

Columnstore

Let’s now drop our compressed index and try a Columnstore index…

DROP INDEX ndx_posts_owner_userId ON Posts
CREATE NONCLUSTERED COLUMNSTORE INDEX ndx_cs_owner_user_id ON Posts(OwnerUserId)

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.

Now let’s see what this does…

SELECT TOP 5 
  Users.DisplayName,
  COUNT(*)
FROM 
  Posts
  INNER JOIN Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Posts.OwnerUserId,Users.DisplayName
ORDER BY COUNT(*) DESC

Table ‘Posts’. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 6382, lob physical reads 0, lob read-ahead reads 22818.

Table ‘Posts’. Segment reads 5, segment skipped 0.

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.

Indexed Views

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…

CREATE VIEW vw_TopPosters_Indexed WITH SCHEMABINDING
AS
SELECT 
   Users.Id,
  Users.DisplayName,
  COUNT_BIG(*) Posts
FROM 
  dbo.Posts
  INNER JOIN dbo.Users ON Users.Id = Posts.OwnerUserId
GROUP BY 
  Users.Id,Posts.OwnerUserId,Users.DisplayName
GO

CREATE UNIQUE CLUSTERED INDEX pk_PostAggregates ON vw_TopPosters_Indexed(Id)
CREATE NONCLUSTERED INDEX ndx_test ON vw_TopPosters_Indexed(Posts)

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…

SELECT TOP 5
   DisplayName,
   Posts
FROM vw_TopPosters_Indexed WITH(NOEXPAND)
ORDER BY Posts DESC

Table ‘vw_TopPosters_Indexed’. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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).

Summary

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…

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE 
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

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)

DECLARE @Path NVARCHAR(200) = 'D:\Databases\'
DECLARE @CmdFileList VARCHAR(150) = 
    'cmd  /c ' + /* Run x_cmdshell */
    '"cd /d ' + @path + ' && ' +  /* change directory */
    'dir /b /s *.mdf, *.ldf ' /* List filenames only (/b) and subdirs (/s)  Lfd and Mdf*/

CREATE TABLE #SqlFiles ([filename] VARCHAR(1024))
INSERT INTO #SqlFiles 
   EXEC xp_cmdshell @CmdFileList

DELETE FROM #SqlFiles WHERE [Filename] IS NULL OR [Filename] = 'File Not Found'
SELECT [filename] FROM #SqlFiles
EXCEPT
SELECT physical_name FROM sys.master_files

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…

CREATE TABLE People
(
   Id INT,
   Name NVARCHAR(200),
   Location NVARCHAR(200),
   CONSTRAINT pk_people PRIMARY KEY NONCLUSTERED (id)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA) 

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.

NonClustered Index

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…

CREATE TABLE People
(
   Id INT,
   Name NVARCHAR(200),
   Location NVARCHAR(200),
   CONSTRAINT pk_people PRIMARY KEY NONCLUSTERED (id),
   INDEX ix_people_location NONCLUSTERED(Location)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA) 

Lets then test our a couple of queries…

SELECT * FROM People WHERE id = 1

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…

SELECT * FROM People WHERE id < 100

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

SELECT * FROM People WHERE Location = 'Brighton'

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…

SELECT TOP 100 * FROM People ORDER BY Location

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?

SELECT TOP 100 * FROM People ORDER BY Location DESC

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…

CREATE TABLE People
(
   Id INT,
   Name NVARCHAR(200),
   Location NVARCHAR(200),
   CONSTRAINT pk_people PRIMARY KEY 
      NONCLUSTERED HASH(Id) WITH (BUCKET_COUNT=1000000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA) 

Now lets run our single record lookup from before…

SELECT * FROM People WHERE id = 1

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.

The End

With the above information, you should be armed with everything you need to create optimized indexes on your in memory tables.