Блог

У меня простой SELECT запрос, который возвращает относительно большой набор данных (11 тыс. строк), и при запуске моего приложения время ожидания истекает случайным образом. Это происходит на моем собственном ноутбуке разработчика с использованием localdb. Когда он работает, он возвращает данные почти мгновенно, в других случаях — Microsoft.Data.SqlClient.SqlException: The wait operation timed out. . При сбое использование SSMS также кажется вялым, но оно всегда завершает запросы.

SQL выглядит следующим образом

 (@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
  

План выполнения здесь.

Я установил Blitz и побежал sp_BlitzFirst , чтобы дать мне некоторое представление, но я не уверен, что искать и как решить проблему. Пожалуйста, помогите.

@@версия:

 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.
  

Несколько вещей, которые вы можете попробовать

• План запроса содержит два полных сканирования кластеризованных индексов в таблице. Учитывая, что вам, вероятно, потребуется прочитать все это хотя бы один раз (чтобы получить открытые проблемы), вы также можете использовать один ВЫБОР вместо ОБЪЕДИНЕНИЯ.
  • Подход ОБЪЕДИНЕНИЯ работает очень хорошо, если вы можете использовать индексы для поиска и избегать полного сканирования таблиц / кластеризованных индексов. Если у вас нет индексов, которые он может использовать, вы можете также ограничить количество раз, которое вам нужно прочитать данные.
• Порядок по порядку потребляет память (порядок занимает много процессора и памяти). Можно ли заказывать, скажем, по идентификатору, а не по дате открытия?
• В зависимости от того, какие статусы доступны и сколько строк каждого статуса существует, вы помещаете некластеризованный индекс в [cm] .[IssuesTbl].[Статус]

Вот мой предлагаемый код. Я изменил порядок для начала, Status потому что он уже будет в порядке, но я не изменил второе поле на ID — но сделайте это, если сможете.

 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
  

Кажется, у вас проблемы с памятью. Вы должны выполнить проверку своего статуса:

 SELECT 
  ((t1.requested_memory_kb)/1024.00) MemoryRequestedMB
  , CASE WHEN t1.grant_time IS NULL THEN 'Waiting' ELSE 'Granted' END AS RequestStatus
  , t1.timeout_sec SecondsToTerminate
FROM sys.dm_exec_query_memory_grants t1
  CROSS APPLY sys.dm_exec_sql_text(t1.sql_handle) t2
  

Вы не хотите ждать. Аналогично, вы должны запустить

 SELECT total_physical_memory_kb, available_physical_memory_kb, 
       total_page_file_kb, available_page_file_kb, 
       system_memory_state_desc
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);
  

Что вы хотите здесь Available physical memory is high . Если вы этого не сделаете, это означает, что у вас проблемы с памятью. Тогда возникает вопрос, есть ли у вас конкурирующие процессы.