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:
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 )
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:
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.
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)