3

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

  • The key lookup is the main cause of IO. Take another look at your index. – James Anderson Jun 18 '15 at 10:17
  • Hello, thanks for your reply, what do you mean by take another look? – user3585420 Jun 18 '15 at 10:26
  • 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) – Zohar Peled Jun 18 '15 at 10:34
  • Hmm, can you briefly explain to me the difference between Index key columns and Included columns and what performance implications this has? – user3585420 Jun 18 '15 at 10:44
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.

  • Hmm, but doesn't the low carnality make it an inefficient index? – user3585420 Jun 18 '15 at 11:30
  • 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 Jun 18 '15 at 11:34
  • Hello, thanks for this, that helped massively. – user3585420 Jun 18 '15 at 12:05
  • Nice to read. :-) – Ionic Jun 18 '15 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.