MaxDOP Query Hint

Question

Post reply

MaxDOP Query Hint

GregoryF

SSCertifiable

Points: 6296
More actions
March 4, 2013 at 8:04 am

#271091

Is it possible to pass a variable for MAXDop as a query hint? My query below works when I set a constant for MAXDop, but as soon as I use a variable, I get a syntax error.
declare @MAXDoParallelism as int
set @MAXDoParallelism = 1
select object_id as ObjectID, index_id as IndexID, partition_number as PartitionNum, avg_fragmentation_in_percent as Frag, page_count as Pages, alloc_unit_type_desc
into #Fragmentation
from sys.dm_db_index_physical_stats (db_id('sysutility_mdw'), null, null , null, 'sampled')
where avg_fragmentation_in_percent >= 1 and avg_fragmentation_in_percent <= 100
and index_id > 0 and Page_Count >= 0 and Page_Count <= 1000000000000000000000000
and avg_fragmentation_in_percent <> 0 and avg_fragmentation_in_percent is not null
order by avg_fragmentation_in_percent desc
option (maxdop @MAXDoParallelism)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

GregoryF (3/4/2013)
as soon as I use a variable, I get a syntax error

I think you've answered your own question there 🙂

A way around it would be to use dynamic sql and sp_executesql :

declare @MAXDoParallelism as int

set @MAXDoParallelism = 1

declare @sql nvarchar(4000)

set @sql =

N'select object_id as ObjectID, index_id as IndexID, partition_number as PartitionNum, avg_fragmentation_in_percent as Frag, page_count as Pages, alloc_unit_type_desc

into #Fragmentation

from sys.dm_db_index_physical_stats (db_id(''sysutility_mdw''), null, null , null, ''sampled'')

where avg_fragmentation_in_percent >= 1 and avg_fragmentation_in_percent <= 100

and index_id > 0 and Page_Count >= 0 and Page_Count <= 1000000000000000000000000

and avg_fragmentation_in_percent <> 0 and avg_fragmentation_in_percent is not null

order by avg_fragmentation_in_percent desc

option (maxdop ' + cast(@MAXDoParallelism as nvarchar(4) + ')'

exec sp_executesql @sql

Note you'll need to be aware of caveats when using concatenated variables like this (i.e. sql injection), and that the #Fragmentation table will be out of scope from the calling statement - you can get around this by creating the temp table outside the dynamic sql and inserting into it via sp_executesql.

Cheers

Gaz

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

GregoryF (3/4/2013)
Is it possible to pass a variable for MAXDop as a query hint?

As mentioned above... no.

However, why would you want to do this? MAXDOP is typically something you want to tune to a particular query/execution plan, not something you want to flip on the fly. What (besides the obvious) are you trying to fix by making maxdop a parameterizable setting?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

GregoryF SSCertifiable Points: 6296 More actions · Answer 3

Evil Kraig F (3/4/2013)
GregoryF (3/4/2013)
Is it possible to pass a variable for MAXDop as a query hint?
As mentioned above... no.
However, why would you want to do this? MAXDOP is typically something you want to tune to a particular query/execution plan, not something you want to flip on the fly. What (besides the obvious) are you trying to fix by making maxdop a parameterizable setting?

We have found that reindexing works best (sic fastest) with MaxDOP set to one. I also wanted to use it as a query hint in the query that populates the table of indexes that need rebuilt

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Jeff Moden SSC Guru Points: 1004430 More actions · Answer 4

GregoryF (3/4/2013)
Evil Kraig F (3/4/2013)
GregoryF (3/4/2013)
Is it possible to pass a variable for MAXDop as a query hint?
As mentioned above... no.
However, why would you want to do this? MAXDOP is typically something you want to tune to a particular query/execution plan, not something you want to flip on the fly. What (besides the obvious) are you trying to fix by making maxdop a parameterizable setting?
We have found that reindexing works best (sic fastest) with MaxDOP set to one. I also wanted to use it as a query hint in the query that populates the table of indexes that need rebuilt

So why not just hardcode the "1" into your index routines?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GregoryF SSCertifiable Points: 6296 More actions · Answer 5

Jeff Moden (3/4/2013)
GregoryF (3/4/2013)
Evil Kraig F (3/4/2013)
GregoryF (3/4/2013)
Is it possible to pass a variable for MAXDop as a query hint?
As mentioned above... no.
However, why would you want to do this? MAXDOP is typically something you want to tune to a particular query/execution plan, not something you want to flip on the fly. What (besides the obvious) are you trying to fix by making maxdop a parameterizable setting?
We have found that reindexing works best (sic fastest) with MaxDOP set to one. I also wanted to use it as a query hint in the query that populates the table of indexes that need rebuilt
So why not just hardcode the "1" into your index routines?

Flexability, for now I have hard coded the "1", but I want the option to go upto 8, depending on the server. The rebuild index statement has that flexability built in, I just wanted to get fancy and have the fragmentation table query to have the same.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Jeff Moden SSC Guru Points: 1004430 More actions · Answer 6

Then I believe you're pretty much stuck with dynamic SQL in one form or another.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)