Improve performance on SQL Server Query

Question

I'm trying to increase the performance of a query and I'm out of ideas:

The query does this: gets a 'Process' with specific priorities on selection namely:

Picks up a process that has been waiting for over 15 minutes, AND has either no 'sleep until' value set, or the 'sleep until' is less than the time NOW AND ProcessState = 1.

Possible values for ProcessState are 1,2,3,4

The Query I'm trying to optimize is below: it runs very frequently:

SELECT TOP (1) [t0].[ProcessID],
 [t0].[MaterialID],
 [t0].[VideoVersionID],
 [t0].[AudioVersionID],
 [t0].[TXVersionID],
 [t0].[XMLVersionID],
 [t0].[SubtitleVersionID],
 [t0].[Progress],
 [t0].[ProcessStatusDescription],
 [t0].[WorkflowProcessState],
 [t0].[WorkflowProcessSubState],
 [t0].[ProcessState],
 [t0].[ProcessStateDateLastModified],
 [t0].[DateCreated],
 [t0].[DateLastChecked],
 [t0].[SleepUntil],
 [t0].[LongRunningProcessID]
FROM [dbo].[WF_Process] AS [t0]
WHERE ([t0].[ProcessStateDateLastModified] <= '2015-06-18 09:48:31.597') 
AND (([t0].[SleepUntil] IS NULL) OR ([t0].[SleepUntil] <= '2015-06-18 10:03:31.597')) 
AND ([t0].[ProcessState] = 1) 
ORDER BY [t0].[ProcessStateDateLastModified]

The only things that change are the two date time fields, which operate a sliding window from DateTime.Now(). First paramter DateTime.Now()- 15 minutes, second date time paramater is DateTime.Now().

Execution plan here: https://gist.github.com/anonymous/fd56ead25d726d247518 Or execution plan diagram here: execution plan diagram

The table looks like this:

CREATE TABLE [dbo].[WF_Process](
 [ProcessID] [bigint] IDENTITY(1,1) NOT NULL,
 [MaterialID] [bigint] NOT NULL,
 [Progress] [decimal](18, 2) NULL,
 [ProcessStatusDescription] [varchar](900) NOT NULL,
 [WorkflowProcessState] [int] NOT NULL,
 [WorkflowProcessSubState] [int] NOT NULL,
 [ProcessState] [int] NOT NULL,
 [ProcessStateDateLastModified] [datetime] NOT NULL,
 [DateCreated] [datetime] NOT NULL,
 [DateLastChecked] [datetime] NULL,
 [SleepUntil] [datetime] NULL,
 [LongRunningProcessID] [varchar](65) NULL,
 CONSTRAINT [PK_Process] PRIMARY KEY CLUSTERED 
(
 [ProcessID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[WF_Process] SET (LOCK_ESCALATION = DISABLE)
GO
ALTER TABLE [dbo].[WF_Process] WITH CHECK ADD CONSTRAINT [FK_Process_GB_Material] FOREIGN KEY([MaterialID])
REFERENCES [dbo].[GB_Material] ([MaterialID])
GO
ALTER TABLE [dbo].[WF_Process] CHECK CONSTRAINT [FK_Process_GB_Material]
GO

I have a combined index on all the columns which are within the query: ( and I have other indexes on the table too )

enter image description here

There are 187,851 rows in the database with a data space of 50MB. It has a total Index space of 483MB.

The Query IO Statistics look like this:

(1 row(s) affected)
Table 'WF_Process'. Scan count 1, logical reads 27566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

The client statistics looks like this: enter image description here

The DB server is meaty. DL360 gen8 ( 32 GB ram, 30+ cores etc ).

This query is very heavily used, and so I'm trying to optimize it as much as I can, any help/information to speed up this query would be appreciated. Anything over 500 ms isn't really acceptable.

EDIT:

Did as Zohar said and I now have [ProcessStateDateLastModified] as Key index column, and the other two columns [SleepUntil] and [ProcessState] as included columns. This reduced the number of logical rads ( expected ), but did not reduce the overall timings of the query:

(1 row(s) affected)
Table 'WF_Process'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

Execution plan: enter image description here

Client stats: enter image description here

• James Anderson: The key lookup is the main cause of IO. Take another look at your index.
• user3585420: Hello, thanks for your reply, what do you mean by take another look?
• Zohar Peled: Add the columns in the select list to the index as included columns. this will eliminate the need for key lookup. (the price is, of course, a larger size index)
• user3585420: Hmm, can you briefly explain to me the difference between Index key columns and Included columns and what performance implications this has?
• Zohar Peled: I probably can, but MSDN does it better...

Answer 1

I would suggest to try an index like this:

CREATE NONCLUSTERED INDEX NCI_ProcessState_ProcessStateDateLastModified_SleepUntil 
ON [TABLE](ProcessState,ProcessStateDateLastModified,SleepUntil)

This way you can filter sharp on the state as all others will result in bigger result sets due to the <= filter.

You can check the index usage easily with this query:

SELECT obj.*,usage.*
FROM sys.dm_db_index_usage_stats AS usage
INNER JOIN sys.indexes as obj
 ON usage.object_id = obj.object_id
WHERE database_id = db_id()

Just filter it on your index and take a look a the search and update columns.

• user3585420: Hmm, but doesn't the low carnality make it an inefficient index?
• Ionic: Well if you use the ProcessState later your SQL Server needs to filter your query by date <= which will produce a huge amount of possible datapages. Even if you have just state 0 and 1, the possible pages will be halfed for the second part of the index. I've added additional info in the answer.
• Ionic: Nice to read. :-)