5

In my application, several times I have to show results that are paginated and sorted by some field.

For example a simple user list sorted by last name. Because of that and because I also have logical deletion and it's a multi-tenancy application, I generally use a CLUSTERED INDEX like this:

CREATE CLUSTERED INDEX [idx] ON [Users]
(
     IsDeleted ASC,
     [AccountId] ASC,
     [LastName] ASC
)

That means that a query like paginated like SELECT TOP(20) * FROM Users WHERE IsDeleted = 0 AND AccountId = xxx is sorted by LastName. I know it's not guaranteed to be sorted, but in practice it always is.

However, reading here Kimberly Tripp blog post on clustered indexes, she says it's a horrible idea to do it like so. And it's even worse because the IsDeleted (BIT) field doesn't let me to set the

However, if I change the CLUSTERED INDEX to just the unique ID, I would need to start using ORDER BY LastName, which in practice is very slow.

My table has a few million records (tens of millions at most) and it's used in general as follows:

  • Querying data. Most of the time.
  • Bulk updates/inserts, where only the data modified is under the subset IsDeleted = 0, AccountId = xxxx (only non-deleted data for a single account is updated in bulk).

Question:

What would be the recommended index (and how to sort) for these kind of tables?

Another example for these kind of tables would be a survey results table that has the following columns IsDeleted (BIT), AccountId (FK GUID), UserId (FK GUID), QuestionKey (NVARCHAR), AnswerValue (TEXT), where my CLUSTERED KEY would be probably (IsDeleted, AccountId, UserId, QuestionKey) and 99% of time I would query the table or update it in bulk by the 3 first fields

WHERE IsDeleted = 0 
AND AccountId = xxx 
AND UserId = yyy

or even the 4 fields: ... AND QuestionKey = 'country'

EDIT:

One of the primary reasons I did this is because the bulk updates and the queries are always limited to 1 or a small number of pages. Having an Identity column would require the queries and the updates to read/write in most pages.

EDIT 2:

Following Joe Obbish's example:

This query:

SELECT TOP (20) * 
FROM Users2
WHERE IsDeleted = 0 
AND AccountId = '46FC5693-7446-415A-8626-8937365460D1'
ORDER BY [LastName];
  • Having 1 cluster index in (IsDeleted, AccountId, LastName) results to this:

CPU time = 3 ms, elapsed time = 3 ms.

Table 'Users2'. Scan count 1, logical reads 5, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  • Having one cluster index in a new PK ID (NEWID()) column (that way the data is randomly sorted internally) and one non-clustered (IsDeleted, AccountId, LastName) results to this:

CPU time = 16 ms, elapsed time = 18 ms.

Table 'Users2'. Scan count 1, logical reads 533, physical reads 5, read-ahead reads 1240, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note the IO and time. It's way slower and requires more IO if the data is not stored together. It might take more space, but the speed difference is notable. Am I wrong to do it like that?

  • Is it more expensive to sort the data in the application? – Antoine Hernandez Jun 8 '17 at 16:14
  • @AntoineHernandez It might not have millions of records returned, but easily 100K records. So returning 100K records just to display 20 is slow. – Diego Jancic Jun 8 '17 at 17:07
  • 2
    Assuming that your queries' where statement has IsDeleted as a constant and not a variable, have you tried a filtered index on the IsDeleted column? This would allow you to remove it from the clustered index and if the AccountId only can be used as the clustered index you can make your filtered index sort on last name and include the other columns needed to satisfy the query. You would narrow the clustered index to just the account id and add a non clustered filtered index. – Aaron Jun 8 '17 at 19:18
  • Does "multi-tenancy" mean more than one user-name per AccountID? That's puzzled me. – Robert Carnegie Jun 8 '17 at 23:07
  • 1
    @RobertCarnegie - I assume thar each tenant represents an account, and that an account would have many users. A "multi-tenant" usually means that it's a single database that's used by multiple entities, and that each entity must be able to access only the data relevant to them. Imagine a time-keeping system that's used by 40 small restaurants in an area. You don't want the guy at Joe's Italian Place to get the contact info for The Vienna Experience's entire kitchen staff. – RDFozz Jun 9 '17 at 3:55
7

First I need to reiterate what RDFozz said in his answer about including an explicit ORDER BY. If SQL Server does an allocation order scan you may get the wrong results. Including ORDER BY in the query doesn't cause a performance hit. Why not do it?

From a query performance perspective, the index that you want depends on how many rows you return at a time and how many columns are actually needed from the table.

First I'll throw around 6.5 million rows into a table with six tenants:

CREATE TABLE dbo.Users2 (
    IsDeleted Bit NOT NULL,
    [AccountId] UNIQUEIDENTIFIER NOT NULL,
    [LastName] NVARCHAR(50) NOT NULL,
    [UsefulColumn] NVARCHAR(20) NOT NULL,
    [OtherColumns] NVARCHAR(100) NOT NULL
);

CREATE CLUSTERED INDEX [idx] ON [Users2]
(
     IsDeleted ASC,
     [AccountId] ASC,
     [LastName] ASC
);

CREATE TABLE #ids (id INT NOT NULL IDENTITY (0, 1), [AccountId] UNIQUEIDENTIFIER NOT NULL);
INSERT INTO #ids
SELECT TOP 6 NEWID()
FROM master..spt_values;

INSERT INTO [Users2] WITH (TABLOCK)
SELECT 
CASE WHEN t1.number % 10 = 1 THEN 1 ELSE 0 END
, #ids.[AccountId]
, LEFT(REPLACE(CONVERT(NVARCHAR(50), NEWID()), '-', ''), 12)
, REPLICATE(N'Z', 20)
, REPLICATE(N'Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
LEFT OUTER JOIN #ids ON ABS(t1.number % 6) = #ids.id;

DROP TABLE #ids;

-- get an ID: FFA7D6D8-63E8-422B-B5E7-F7020871CDB4
SELECT TOP 1 [AccountId] FROM Users2
WHERE IsDeleted = 0
ORDER BY [AccountId] DESC;

When running a query similar to yours:

SELECT TOP (20) * 
FROM Users2
WHERE IsDeleted = 0 
AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

I get a clustered index seek as expected:

query plan

Is using the clustered index the best option? It depends. If you don't need to select every column from the table then you can define a smaller covering index which returns the data you need without an explicit sort. Having a smaller covering index is good for performance as you page out further into the data. Suppose that you only need UsefulColumn and not the OtherColumns column. You could define the following index:

CREATE NONCLUSTERED INDEX [idx_1] ON [Users2]
(
     [AccountId] ASC,
     [LastName] ASC
) 
INCLUDE ([UsefulColumn]) 
WHERE IsDeleted = 0;

It's pretty large. For my test case it's around 28% of the size of the data. For this index, it's important to note that changing the clustered key of the table will not have a big impact on its size. SQL Server stores the clustered key columns in the leaf node of the index unless they are already included in the index. This can be demonstrated with a simple test:

CREATE TABLE dbo.IX_TEST (
    COL1 BIGINT NOT NULL,
    COL2 BIGINT NOT NULL,
    FILLER VARCHAR(6) NOT NULL,
    PRIMARY KEY (COL1)
);

INSERT INTO dbo.IX_TEST WITH (TABLOCK)
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('Z', 6)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

EXEC sp_spaceused 'IX_TEST'; -- 96 KB

CREATE INDEX COL1 ON dbo.IX_TEST (COL1)
EXEC sp_spaceused 'IX_TEST'; -- 14032 KB

CREATE INDEX COL2 ON dbo.IX_TEST (COL2)
EXEC sp_spaceused 'IX_TEST'; --- 35920 KB

Going back to our table, if I created an index on just the UsefulColumn column there would be an overhead (with no compression) of approximately 1 byte for the IsDeleted column, 16 bytes for the AccountId, 2 * the average length of the LastName column, and 0 or 4 bytes for the internal uniquifier (only happens for duplicate last names). For my test data that's quite a bit of overhead:

1 + 16 + 2 * 12 + 0 = 41 bytes

However for the idx_1 index I defined above it's around only 1 bytes (1 for IsDeleted and 0 for the uniquifier, assuming not very many duplicate last names). The index is mainly large because I'm using wide columns as key columns. Keep in mind that the indexes will be the same size with your current clustering key, but changing the clustering key of the table to a thinner set of columns will greatly decrease the size of the index only defined on UsefulColumn.

For any TOP value I should get a nice index seek on the covering index. This query:

SELECT TOP (200000) [LastName], [UsefulColumn] 
FROM Users2
WHERE IsDeleted = 0 AND AccountId = 'FFA7D6D8-63E8-422B-B5E7-F7020871CDB4'
ORDER BY [LastName];

Has the following plan:

covering plan

Even if you need every single column from the table the index defined above can still give good enough performance. You'll get a key lookup for every row that you return. For a single end user, doing key lookup on 20 rows it shouldn't be noticeable. In your test you saw execution times of 3 ms and 18 ms. However, if you have a highly concurrent workload it could make a difference. Only you can evaluate that properly.

Even without the caveats above, when selecting many rows you may notice a large performance difference:

clust compare

The first query has an index hint to force the use of the index. The query optimizer thinks that plan will be much more expensive than the plan that uses the clustered index.

If I had to guess, I would say that you probably don't need to use a clustered index to perform your paging. If you have a lot of indexes on the table you might benefit from creating a new covering index for your pagination queries and by defining the clustered index on a smaller, unique set of columns.

2

First: The reasons why a wide clustered index are a bad idea are:

  • it makes other indexes larger, because they have to include the clustered index value; and
  • It can make it more likely that you will see page splits, since data is not added to the end of the table on an insert, and data can change position in the table on an update. This both adds time to the inserts and updates, and causes the index to become fragmented.

So, in purely practical terms, maybe the first thing to ask yourself is - is this a problem for you?

While it's always best to resolve potential problems before they become actual problems, I'd guess that your DB is not a new one. If you're not expected significant growth, and everything is performing adequately today; you're not running into a situation where disk space is tight, and increasing it is costly; and, if you expect a redesign to cause problems with performance, consider if making a change is the best solution at this time.

If there are other drivers to change, especially anything that would make a general refactoring effort make sense, then experimenting with alternatives is something that is a logical task, and should probably be done. However, if the only motivation for change is "doing it this way is bad", then (no matter how correct the statement may be), you risk investing a lot of time and effort in making a change that has no obvious visible benefit. Unless you're the boss on this, your boss is likely to think that's a bad idea.


That aside: Your primary concern about the change is that you currently use clustered indexes to basically sort your data in the most convenient order. And, that you currently save (a notable amount of) time because you don't put an ORDER BY on your queries, and rely on the clustered indexes to return the data in the correct order, because that's how it's stored. And, so far, that's working for you.

First, note that this could change. I haven't found an actual Microsoft page that says, "data returned from a table with a clustered index can be returned in something other than the clustered index order," but I have found multiple pages that explain that NOT using an ORDER BY means there's no guarantee of the order of your data, clustered index or not. Currently, the SQL Server engine happens to be optimizing its query in a way that happens to return he data in sorted order; changes to your data (a large influx of new data), your server (moving your DB to a server with more CPUs), or your maintenance routines (rebuilding the clustered index more or less frequently) could all result in a change to this behavior.

However, I also found one article that may provide a tip as to why ORDER BY LastName performs so poorly for you. At first glance, it may sound like it supports what you're doing now. It doesn't - it specifically mentions that a clustered index can help sort performance with an ORDER BY clause.

However, ORDER BY LastName doesn't match the ordering in your clustered index. I encourage you to try adding ORDER BY Is_Deleted, AccountID, LastName to your existing query. Since the ORDER BY matches the index, including it shouldn't impose a notable penalty on the query. I may be wrong - the optimizer may be recognizing that Is_Deleted and AccountID will be the same for all rows returned, and have used the index order in your past tests, but the best way to guarantee the use of an index is to make sure that you include all the columns, and that may be true here as well.

Next step - if you really want to consider changing your clustered index to a narrower one, I'd try this out, and see how it performs for you (in your test system, of course). Change the clustered index (to UserId, I'll assume0, and add a non-clustered index with the definition of the current clustered one. Test the same query - the one where the ORDER BY matches the columns (and sort direction, that's important) of the new index. Now, see if your query performs well or not.

The results probably will depend on how you're doing the pagination. If that's ultimately built into the query (for SQL 2012 and later, the OFFSET/FETCH method gets rave reviews), then there's a good chance the optimizer will realize that it's going to be faster to find the right rows with the non-clustered index than with some other method, and will look up the remaining data on the 20-30 rows you actually want when it finds which ones they are.

If the query doesn't use the index, you can use table hints to try to force it to and see if that performs better than the optimizer's best guess - however, overriding the optimizer's call one the best way to bring back your data is something that should be done with care, as it looks things in no matter how your data changes....

You would also want to see the actual impact on your bulk updates. However, in most cases, the queries that the user has to wait for are more critical than the ones that happen off-hours, time-wise; if it those are not as fast, unless they force your normal nightly work to push out beyond its available windows, that's usually OK.

2

As this question relates to a multi-tenant system, please see the more detailed answered I provided to this DBA.SE question:

Composite Primary Key in multi-tenant SQL Server database

But a few quick notes:

  1. It is probably best to not have a column in the Clustered Index that can change value due IF you have any non-Clustered Indexes (and if this Clustered Index is a PK that has FKs pointing to it). Since the Clustered Index key columns are copied into all Non-Clustered Indexes, changing the value in one of those columns requires a write operation to all Non-Clustered Indexes on this tables (not to mention the ripple effect if this is a PK that has FKs pointing to it as they would also need to be updated). Not only is this an issue for I/O, but it means pulling in data pages for indexes into the Buffer Pool (i.e. memory) that you might not have even been using. It also increases the scope of blocking and deadlocks as more items will be locked during this operation.

  2. For multi-tenant I prefer to use the TenantID first since ALL of your app queries should have that column in the WHERE clause. Only queries that wouldn't are back-end maintenance or internal company (i.e. cross-client) reports, and you can either deal with slightly slower queries there OR create a non-Clustered Index to deal with those.

  3. If you don't have a small number of non-changing columns to make a Unique Clustered Index / PK, then add an IDENTITY column of type INT or if need be, BIGINT. Rarely is there a need to use UNIQUEIDENTIFIER for such things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.