0

We have an MSSQL instance 2017 on version 14.0.3370 and there are issues with RESOURCE_SEMAPHORE waits when full or transaction backup job runs. DBs hosts over 9000 small dbs with used space up to approx. 10 MBs or so and dbs are not used so much. Maximum memory for the instance is 28 GB. Requested_memory_KB for running dbo.Database SP is around 4,5 GB and it is even when tr. log backup runs for system dbs.

EXECUTE dbo.DatabaseBackup
@Databases = 'SYSTEM_DATABASES',
@Directory = '',
@BackupType = 'LOG',
@Verify = 'Y',
@CheckSum = 'Y',
@LogToTable = 'Y',
@CleanupMode = 'BEFORE_BACKUP',
@ChangeBackupType = 'Y',
@CleanupTime = 156

I've used a cursor for backing up of tr. logs and full backups. New dbs are created daily, so using MS maintenance plan is not an option, because I haven't find any possibility how to change backup type when new dbs are created and tr. log backup will fail due to missing full backup of new dbs.

The same problem with resource_semaphore wait is from time to time for index maintenance and update statistics jobs with and I don't want to replace Ola's maintenance plans, because we used those as a default.

1
  • RESOURCE_SEMAPHORE waits occur when SQL Server is attempting to fullfill a memory grant for a query. What makes you believe RESOURCE_SEMAPHORE is a problem?
    – Hannah Vernon
    May 31, 2021 at 15:13

1 Answer 1

1

Problem with huge memory grant is related to ORDER BY in below statement with huge amount of databases and removing ORDER BY help and only 11 MB is requested_memory_kb needed now.

INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, AvailabilityGroup, [Order], Selected, Completed)
  SELECT [name] AS DatabaseName,
         LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'',''),'|',''))) AS DatabaseNameFS,
         CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
         NULL AS AvailabilityGroup,
         0 AS [Order],
         0 AS Selected,
         0 AS Completed
  FROM sys.databases
  WHERE [name]  'tempdb'
  AND source_database_id IS NULL
  ORDER BY [name] ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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