SQL Server Express (localdb) The wait operation timed out - query timing out at random?

Question

I have plain SELECT query that is returning relatively large dataset (11k rows) and it is randomly timing out when ran by my app. This is happening on my own dev laptop using localdb. When it works it returns data almost instantly, other times - Microsoft.Data.SqlClient.SqlException: The wait operation timed out.. When it fails using SSMS also feels sluggish but it always finishes queries.

SQL is as follows

(@CurrentUserId int)
-- First, select Draft issues created by current user
SELECT [I].[IssueId]
      ,[I].[IssueGuid]
      ,[I].[IssueNumber]
      ,[I].[DateCreated]
      ,[I].[DateOpened]
      ,[I].[DateLastModified]
      ,[I].[DateClosed]
      ,[I].[Title]
      ,[I].[Type]
      ,[I].[Status]
      ,[I].[CreatedByUserId]
      ,1 AS [OrderKey]
FROM [cm].[IssuesTbl] [I]
WHERE [I].[Status] = 0 AND [I].[CreatedByUserId] = @CurrentUserId

UNION ALL

-- Last, select Open issues
SELECT [I].[IssueId]
      ,[I].[IssueGuid]
      ,[I].[IssueNumber]
      ,[I].[DateCreated]
      ,[I].[DateOpened]
      ,[I].[DateLastModified]
      ,[I].[DateClosed]
      ,[I].[Title]
      ,[I].[Type]
      ,[I].[Status]
      ,[I].[CreatedByUserId]
      ,2 AS [OrderKey]
FROM [cm].[IssuesTbl] [I]
WHERE [I].[Status] = 1

ORDER BY [OrderKey] ASC, [I].[DateOpened] DESC, [I].[DateCreated] DESC

Execution plan is here.

I installed Blitz and ran sp_BlitzFirst to give me some idea but I'm not sure what to look for and how to fix the problem. Please help.

@@version:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 17134: )

sp_BlitzFirst:

10  Server Performance  Poison Wait Detected: RESOURCE_SEMAPHORE
For 4 seconds over the last 5 seconds, SQL Server was waiting on this particular bottleneck.

200 Wait Stats  RESOURCE_SEMAPHORE
For 4 seconds over the last 5 seconds, SQL Server was waiting on this particular bottleneck.

Answer 1

A few things you may try

• The query plan has two full clustered index scans on the table. Given that you'll probably need to read it all at least once (to get the Open issues), you may as well use a single SELECT instead of the UNION.
  • The UNION approach works really well if you can use indexes to do seeks and avoid full table/clustered index scans. If you don't have indexes it can use, then you may as well limit the number of times you need to read the data.
• The order-by eats memory (ordering takes a lot of CPU and memory). Is it possible to order by, say, ID rather than DateOpened?
• Depending on what statuses are available and how many rows of each status there are, you put a non-clustered index on [cm].[IssuesTbl].[Status]

Here's my suggested code. I changed the order by to start with Status because it will already be in order, but I haven't changed the second field to be ID - but do this if you can.

SELECT [I].[IssueId]
      ,[I].[IssueGuid]
      ,[I].[IssueNumber]
      ,[I].[DateCreated]
      ,[I].[DateOpened]
      ,[I].[DateLastModified]
      ,[I].[DateClosed]
      ,[I].[Title]
      ,[I].[Type]
      ,[I].[Status]
      ,[I].[CreatedByUserId]
      ,CASE WHEN [I].[Status]=1 THEN 2 ELSE 1 END AS [OrderKey]
FROM [cm].[IssuesTbl] [I]
WHERE ([I].[Status] = 1)
      OR ([I].[Status] = 0 AND [I].[CreatedByUserId] = @CurrentUserId)
ORDER BY [I].[Status] ASC, [I].[DateOpened] DESC, [I].[DateCreated] DESC