ID first: This is the most selective (i.e. most unique) field. But by being an auto increment field (or random if still using GUIDs), each customer's data is spread out throughout each table. This means that there are times when a customer needs 100 rows, and that requires almost 100 data pages read from disk (not fast) into the Buffer Pool (taking up more space than 10 data pages). It also increases contention on the data pages since it will be more frequent that multiple customers will need to update the same data page.
However, you typically do not run into as many parameter sniffing / bad cached plan issues as much since the statistics across the different ID values is fairly consistent. You might not get the most optimal plans, but you will be less likely to get horrible ones. This method essentially sacrifices performance (slightly) across all customers to gain the benefit of less frequent problems.
TenantID first: This is very much not selective at all. There might be very little variation across 1 million rows if you only have 100 TenantIDs. But the statistics for these queries is more accurate since SQL Server will know that a query for Tenant A will pull back 500,000 rows but that same query for Tenant B is only 50 rows. This is where the main pain-point is. This method greatly increases the chances of having parameter sniffing issues where the first run of a Stored Procedure is for Tenant A and acts appropriately based on the Query Optimizer seeing those statistics and knowing it needs to be efficient getting 500k rows. But when Tenant B, with only 50 rows, runs, that execution plan is no longer appropriate, and in fact, is quite inappropriate. AND, since the data is not being inserted in the order of the leading field, this method creates a lot more page splits (hence more fragmentation) in a short period of time than the other approach.
However, for the first TenantID to run a Stored Procedure, the performance should be better than in the other approach since the data (at least after doing index maintenance) will be physically and logically organized such that far fewer data pages are needed to satisfy the queries. This means less physical I/O, fewer logical reads, less contention between Tenants for the same data pages, less wasted space taken up in the Buffer Pool (hence improved Page Life Expectancy) etc.
There are two main costs to getting this improved performance. The first is not so difficult: you must do regular index maintenance to counteract the increased fragmentation. The second is a bit less fun.
In order to counteract the increased parameter sniffing issues, you need to separate the execution plans between Tenants. The simplistic approach is to use WITH RECOMPILE on procs or the OPTION (RECOMPILE) query hint, but that is a hit on performance that could wipe away all of the gains made by putting TenantID first. The method that I found worked best is to use parameterized Dynamic SQL via sp_executesql. The reason for needing the Dynamic SQL is to allow for concatenating the TenantID into the text of the query, while all other predicates that would normally be parameters are still parameters. For example, if you were looking for a particular Order, you would do something like:
DECLARE @GetOrderSQL NVARCHAR(MAX);
SET @GetOrderSQL = N'
SELECT ord.field1, ord.field2, etc.
FROM dbo.Orders ord
WHERE ord.TenantID = ' + CONVERT(NVARCHAR(10), @TenantID) + N'
AND ord.OrderID = @OrderID_dyn;
';
EXEC sp_executesql
@GetOrderSQL,
N'@OrderID_dyn INT',
@OrderID_dyn = @OrderID;
The effect this has is to create a reusable query plan for just that TenantID that will match the data volume of that particular Tenant. If that same Tenant A executes the stored procedure again for another @OrderID then it will reuse that cached query plan. A different Tenant running that same Stored Procedure would generate a query text that was different only in the value of the TenantID, but any difference in the query text is enough to generate a different plan. And the plan generated for Tenant B will not only match the data volume for Tenant B, but it will also be reusable for Tenant B for different values of @OrderID (since that predicate is still parameterized).
The downsides to this approach are:
- It is a little more work than just typing in a simple query (but not all queries need to be Dynamic SQL, just the ones that end up having the parameter sniffing problem).
- Depending on how many Tenants are on a system, it does increase the size of the plan cache since each query now requires 1 plan per TenantID that is calling it. This might not be an issue, but is at least something to be aware of.
Dynamic SQL breaks the ownership chain, which means read/write access to tables cannot be assumed by having EXECUTE permission on the Stored Procedure. The easy but less secure fix is just to give the User direct access to the tables. This is certainly not ideal, but that is usually the trade-off for quick and easy. The more secure approach is to use Certificate-based security. Meaning, create a Certificate, then create a User from that Certificate, grant that User the desired permissions (a Certificate-based User or Login cannot connect to SQL Server on its own), and then sign the Stored Procedures that use Dynamic SQL with that same Certificate via ADD SIGNATURE.
For more information on module signing and Certificates, please see: ModuleSigning.Info
Best Answer
Yes, that single index would serve both queries. But you cannot get rid of the index on
id, since it is necessary to implement the primary key constraint.I would research if the indexes are really the cause of your performance problem.