Reduce Parallelism Without Setting MAXDOP

2 votes
1 answer
158 views
sql-server azure-sql-database parallelism
                          I am trying to reduce parallelism in my query without explicitly setting MAXDOP. I have been informed that MAXDOP can hinder throughput as the platform grows and query executions increase (happy to be corrected on this).

The following query has been observed hitting MAXDOP 8, causing spikes of workers in our Azure SQL database. The result is the error "the request limit for the database is 800 and has been reached"

The following has been applied to try and make this query less parallel:
1. Split the queries into #ErIds and #BaseTable
2. Change a big CASE statement into individual IFs for the sort

If anyone has any ideas on how to reduce parallelism that would be really appreciated.

I can provide more info if needed.

Thank you.

```
declare @uniqueIds dbo.GuidList
  , @ids dbo.IntegerList
  , @searchTerm nvarchar(max) = null
  , @pageNum int
  , @pageSize int
  , @sortBy nvarchar(50) = 'Name'
  , @sortDescending bit = 0

set nocount on;

  -- creating these local variables to avoid parameter sniffing
  declare @pageNumLocal int = case 
      when @pageNum is null
        or @pageNum <= 0
        then 0
      else @pageNum
      end
    , @pageSizeLocal int = isnull(@pageSize, 999999) -- If pageSize is null, return all records
    , @sortByLocal nvarchar(50) = isnull(@sortBy, 'Name')
    , @sortOrderLocal nvarchar(20) = 'asc'
    , @searchTermLocal nvarchar(50) = '%' + lower(trim(isnull(@searchTerm, ''))) + '%';

  if @sortDescending = 1
    set @sortOrderLocal = 'desc'
  else
    set @sortOrderLocal = 'asc';

  create table #ErIds(id integer primary key);
  insert into #ErIds
  select distinct e.Id
  from [TableA] e
  where e.IsSoftDeleted = 0
    and (
      e.Id in (
        select intVal
        from @ids
        )
      or e.UniqueId in (
        select guidVal
        from @uniqueIds
        )
    )

  create table #BaseTable (
    Id int,
    UniqueId uniqueidentifier,
    [Name] nvarchar(200),
    EmployeeCount int,
	ProcessorName nvarchar(255)
  );

  insert into #BaseTable
  select e.Id,
    e.UniqueId,
    e.[Name],
	e.EmployeeCount,
	ProcessorName = isnull(u.FirstName + ' ' + u.LastName, u.EmailAddress),
  from #ErIds erids
  inner join [TableA] e
    on erids.id = e.id
  left join [TableB] bs
    on bs.EmployerId = e.Id
  left join [TableC] u
    on u.Id = bs.ProcessorId
  where 
    (
      isnull(@searchTermLocal, '') = ''
      or lower(trim(e.[Name])) like @searchTermLocal
      or lower(trim(isnull(u.FirstName + ' ' + u.LastName, u.EmailAddress))) like @searchTermLocal
      or lower(trim(convert(nvarchar(36), e.UniqueId))) like @searchTermLocal
    )

  if @sortByLocal = 'Name'
  begin
    if @sortOrderLocal = 'asc'
      select * from #BaseTable order by [Name] asc offset @pageNumLocal * @pageSizeLocal rows fetch next @pageSizeLocal rows only;
    else
      select * from #BaseTable order by [Name] desc offset @pageNumLocal * @pageSizeLocal rows fetch next @pageSizeLocal rows only;
  end
  else if @sortByLocal = 'ProcessorName'
  begin
    if @sortOrderLocal = 'asc'
      select * from #BaseTable order by ProcessorName asc offset @pageNumLocal * @pageSizeLocal rows fetch next @pageSizeLocal rows only;
    else
      select * from #BaseTable order by ProcessorName desc offset @pageNumLocal * @pageSizeLocal rows fetch next @pageSizeLocal rows only;
  end
  else if @sortByLocal = 'EmployeeCount'
  begin
    if @sortOrderLocal = 'asc'
      select * from #BaseTable order by EmployeeCount asc offset @pageNumLocal * @pageSizeLocal rows fetch next @pageSizeLocal rows only;
    else
      select * from #BaseTable order by EmployeeCount desc offset @pageNumLocal * @pageSizeLocal rows fetch next @pageSizeLocal rows only;
  end
  
  drop table #ErIds;
  drop table #BaseTable;
                        
Asked by SkelDave (163 rep)
Jul 7, 2025, 09:25 AM
Last activity: Jul 8, 2025, 07:45 PM
Reduce Parallelism Without Setting MAXDOP

Related Questions
