Creating an Index to optimize MS SQL Query

Question

I have a database with millions of rows and I want to create an index which fixes performance issues as the query is over-using the processors. The database is hosted on MS Azure.

if I have a query similar to the following, for example to get employees who are not manager:

SELECT name, position, job title WHERE name LIKE '{0}%' AND manager = 0 AND employee = 1  ORDER BY senior DESC  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

manager, employee and senior are all booleans (senior employees should show first in results)

We have tried something similar to the following but the server is still unable to handle the traffic:

CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[Employees]
(
    [Manager] ASC,
    [Employee] ASC,
    [Name] ASC
)
INCLUDE (   [Position],
    [JobTitle],
    [Senior]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

The execution plan shows:

47% on index seek and 53% on top N sort if I search from SSMS

17% index seek, 26% parallelism, 56% sort on tracked queries from webapp

(both webapp and database are hosted on Azure)

What would be a proper index?

Edit:

I have received a suggestion to use a filtered index (like where manager = 0 and employee = 1)

but the sql server won't select it and I'm not able to force it. Any suggestions?

Answer 1

You say senior is a boolean. This is the only thing you are sorting on. Your current order by is not specific enough to use paging reliably. You need to add secondary items such as name to make it deterministic

To optimise this query you can first add this index

CREATE NONCLUSTERED INDEX [IX_Index]
  ON [dbo].[Employees] ( [Senior] ASC, [Name] ASC )
  INCLUDE ( [Position], [JobTitle], [Manager], [Employee])
WHERE Manager = 0 AND Employee = 1;

Then rewrite it to break it into two queries. One retrieving the seniors and one retrieving the non seniors.

The following uses efficient seeks and no sort operators.

DECLARE @Offset INT = 0, 
        @Fetch INT = 10,
        @SeniorCount INT;

DECLARE @Result TABLE (
    ResultId INT IDENTITY PRIMARY KEY,
    Name varchar(50) NULL,
    Position varchar(50) NULL,
    JobTitle varchar(50) NULL
) 


INSERT INTO @Result
SELECT TOP (@Fetch + @Offset) name,
                position,
                JobTitle
FROM   dbo.Employees
WHERE  name LIKE '{0}%'
       AND manager = 0
       AND employee = 1
       AND Senior = 1
order by name;

SET @SeniorCount = @@ROWCOUNT;


INSERT INTO @Result
SELECT TOP (@Fetch + @Offset - @SeniorCount) name,
                position,
                JobTitle
FROM   dbo.Employees 
WHERE  name LIKE '{0}%'
       AND manager = 0
       AND employee = 1
       AND Senior = 0
order by name
OPTION (RECOMPILE);

SELECT *
FROM @Result
WHERE ResultId > @Offset AND ResultId<= @Offset + @Fetch
ORDER BY ResultId;

Answer 2

Your where clause structure isn't well-suited for index use. This is going to get complicated so bear with me:

Manager and employee are low variability columns. A b-tree+ index won't help. B-tree+ indexes work well when a very small number of rows are returned. Every row in your table will either be an employee or manager so you're potentially searching every row. Your highest variability candidate is name. It should be first in the index. However, in this particular query, the like statement may cause the optimizer to skip the index. If the optimizer recognizes the prefix of the name is known, it may use the index for a partial search. Either way, I'd structure the index as follows:

    CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[Employees]
    (
     [Name] ASC,
     [Manager] ASC,
     [Senior] DESC
    )
    INCLUDE ([Employee], [Position], [JobTitle])

The best index structure for low variability data like manager and employee is a bitmap which SQL Server doesn't provide at this time. I only include Manager and Senior in the index because you're using an offset-fetch. I'm assuming if the row is not a manager, it is an employee so I only need one of them in the index. I'm not sure how the results are being used. I'm going to guess that you're paging them back into a user's interface. In that case, consider the distribution of your data. I counted the number of names in my own names table and got the following distribution:

 First         Percent of
 Letter Count  Total
    A   22911   3.31
    B   58989   8.5
    BA  13442   1.94
    ...

My table is much smaller than yours but I believe what I'm saying will scale. You need to reduce the amount of rows "found". In your query, make sure the name string {0} has enough characters so the percent found is low, say 5% of your total number of rows. If {0} is null, change it to an 'A', if {0} is a 'B', change it to 'BA'. If you really want to return all employees when {0} is null then I don't think an index will help you. Indexes are used to reduce the result set.