SQL Server – Why OPTION (MAXDOP 1) Appears in Actual Query Plan

performanceperformance-testingperformance-tuningsql serversql-server-2012

SQL Server is set with MAXDOP 0 by default. In the stored procedure I'm testing I keep seeing WITH (MAXDOP 1) in the Actual Execution Plans even though I've not specified any hints other than SORT_IN_TEMPDB = 1. The main Stored Procedure calls another procedure which grabs the table, generates scripts on each disabled index, then executes that series of scripts (with the afformentioned query hint). The funny thing is I'm seeing a high CPU cost for the operation so I imagine there's benefits to parallelism.

The table has a few million rows and is very wide.

Unfortunately, the database is file and TempDB file are on the same datastore and the TempDB is just 1 file as opposed to several. This configuration is out of my control but perhaps it's related to the reason for no parallelism.

The server is on a VM with 4 cores and 64 GB RAM.

Any thoughts on why SQL Server insists on dropping parallelism on such a large operation on a large table?

EDIT: Added script for the Inner stored procedure that gets called during Upserts:

CREATE PROCEDURE etl_Index_EnableDisable
@DatabaseName NVARCHAR(50)
,@TableName NVARCHAR(50)
,@EnableDisable NVARCHAR(50)

AS
BEGIN

 IF @DatabaseName IS NULL
   RAISERROR('Null values not allowed for @DatabaseName', 16, 1)
 IF @TableName IS NULL
   RAISERROR('Null values not allowed for @TableName', 16, 1)
 IF @EnableDisable IS NULL
   RAISERROR('Null values not allowed for @EnableDisable', 16, 1)

 DECLARE @SQL NVARCHAR(4000)

 -- Interpret 1 as enable and 0 as disable. Also set to Upper case
 SET @EnableDisable = CASE UPPER(@EnableDisable) WHEN '1' THEN 'ENABLE' 
                                                 WHEN 'REBUILD' THEN 'ENABLE' 
                                                 WHEN '0' THEN 'DISABLE' 
                                                 ELSE UPPER(@EnableDisable) 
                      END


 SET @SQL = ' DECLARE @SQLIn NVARCHAR(4000)  
              SELECT @SQLIn = COALESCE(@SQLIn,'' '') 
              +  SQL1 FROM ( (SELECT '' ALTER INDEX '' + si.name + '' ON ' + @DatabaseName + ' .dbo.'' + st.name + ' + CASE @EnableDisable WHEN 'DISABLE' THEN ''' DISABLE ''' 
                                                                                                                     WHEN 'ENABLE' THEN ''' REBUILD WITH (SORT_IN_TEMPDB = ON) ''' END + '
                 AS SQL1
                  FROM ' + @DatabaseName + '.Sys.indexes si
                       JOIN  ' + @DatabaseName + '.Sys.tables st ON si.object_id = st.object_id
                  WHERE st.name = ''' + @TableName + '''
                        AND (si.is_unique = 0
                             AND si.is_unique = 0
                             AND si.is_primary_key = 0
                            AND si.[type] = 2
                            )
                       AND si.is_disabled = ' + CASE @EnableDisable WHEN 'DISABLE' THEN '0'
                                                                    WHEN 'ENABLE' THEN '1' 
                                                END + '
                       AND si.Name IS NOT NULL
               ) ) tb

             EXEC sp_sqlexec @SQLIn '

 EXEC sp_sqlexec @SQL

END`

Best Answer

You are performing an index rebuild on Standard edition.

As documented here you would need Enterprise or Developer edition to see parallelism for this.

Related Solutions

Sql-server – SQL Server – Must be first statement in query batch – what and why

From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

Rules for Using Batches

The following rules apply to using batches:

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined with other statements in a batch. The CREATE statement must start the batch. All other statements that follow in that batch will be interpreted as part of the definition of the first CREATE statement.

A table cannot be changed and then the new columns referenced in the same batch.

If an EXECUTE statement is the first statement in a batch, the EXECUTE keyword is not required. The EXECUTE keyword is required if the EXECUTE statement is not the first statement in the batch.

Sql-server – Execution Plan not matching Stored Proc

Your code says you are doing a LEFT OUTER JOIN, but are you really? Your very first WHERE clause (as well as each that follow) filters rows for inclusion from the outer table:

WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')

This turns your LEFT OUTER JOIN into an INNER JOIN, whether you meant it or not. Perhaps you meant to move those filters to the ON clause.

Of course I would probably remove the non-sargeable MONTH() function from the JOIN, since this is going to force a complete scan on the remote table, and write it this way instead - also eliminating the join to your local months table:

DECLARE 
  @OName      VARCHAR(50) = 'John', 
  @Start_Date DATE = NULL,--'20120101', -- use safe date formats 
  @End_Date   DATE = '20121231'; -- use semi-colons

-- deal with NULLs here so the query is simpler:

SELECT @Start_Date = COALESCE(@Start_Date, '20010101'),
       @End_Date   = COALESCE(@End_Date, CURRENT_TIMESTAMP);

;WITH n(n) AS 
(
  -- get the # of months you need instead of relying on your Connector table:
  SELECT TOP (DATEDIFF(MONTH, @Start_Date, @End_Date)+1) Number
    FROM master..spt_values
    WHERE [type] = N'P' AND Number >= 0
    ORDER BY Number
), m(m) AS
(
  -- convert those to months:
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date), 0))
  FROM n
)
SELECT 
  m.m, 
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
FROM m
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
ON 
  w.Widget_Date >= m.m
  AND w.Widget_Date < DATEADD(MONTH, 1, m.m) -- open-ended date range query
  AND w.Operator_Name LIKE '%' + @OName + '%'
GROUP BY m.m
ORDER BY m.m;

Just ignore the m column in the output (you can't order by it without including it in the output, and it doesn't make sense to order by the name).

Another suggestion: don't use 'single quotes' as alias delimiters. Forms of this are deprecated and it also makes column alias look like string literals. Use [square brackets] instead.

Best Answer

Related Solutions

Sql-server – SQL Server – Must be first statement in query batch – what and why

Sql-server – Execution Plan not matching Stored Proc

Related Question