Sql-server – Improve performance on SQL Server Query

optimizationperformancequery-performancesql server

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

Best Answer

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.

Related Question