March 4, 2013 at 8:04 am
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!
March 4, 2013 at 8:15 am
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
March 4, 2013 at 12:36 pm
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?
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
March 4, 2013 at 1:47 pm
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!
March 4, 2013 at 4:00 pm
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
Change is inevitable... Change for the better is not.
March 4, 2013 at 4:45 pm
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!
March 4, 2013 at 7:24 pm
Then I believe you're pretty much stuck with dynamic SQL in one form or another.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy