question

Raj More avatar image
Raj More asked

Change MaxDop of a running query

My 8 Proc 32 GB server has the MAXDOP has been set to `0`. I am rebuilding indexes on this server and now I cannot even expand the list of tables in SSMS. **Question Edited** Is there any way for me to force this query to relinquish resources while it is running?
sql-server-2008-r2maxdop
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
My solution still stands. Resource governor will throttle the command if configured correctly, even if the command is currently running.
0 Likes 0 ·
Raj More avatar image Raj More commented ·
Yes it absolutely does.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
The only way that I know of that could help you out would be to configure resource governor to limit the CPU and RAM resources that a connection can use. It wouldn't change the MAXDOP setting, but you could throttle CPU usage for that connection and thus free up resources for everyone else. Take a look at [how resource governor works in Books Online][1]. This is an Enterprise Edition feature though, so may not be available to you. [1]: http://msdn.microsoft.com/en-us/library/bb933866.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
Does this work: "select * from sys.tables with (nolock)" ?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.