SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


parallelism


parallelism

Author
Message
MarvinTheAndriod
MarvinTheAndriod
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2644 Visits: 1291
I have been asked to restrict the number of processors a database uses on a server through parallelism (a term I am not familiar with). Is it possible to change a setting at database level to make this possible?
Mr James Howard
Mr James Howard
Mr or Mrs. 500
Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)Mr or Mrs. 500 (588 reputation)

Group: General Forum Members
Points: 588 Visits: 9356
http://msdn.microsoft.com/en-us/library/ms189094.aspx

James Howard
MarvinTheAndriod
MarvinTheAndriod
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2644 Visits: 1291
Thanks. I was wondering though is it possible to set a different level of parrellism at database level rather than server level ?
MANU-J.
MANU-J.
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17602 Visits: 8766
You can't specify it at database level but you can do that at query level by making use of QueryHints.

http://msdn.microsoft.com/en-us/library/ms181714.aspx


MJ
rshafer
rshafer
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 136
You could also use the MAXDOP option at the end of your queries. This will set the number of physical processors used for that particular query. MAXDOP is short for Maximum Degree of Paralellism.

OPTION (MAXDOP 1)
rshafer
rshafer
SSC Veteran
SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)SSC Veteran (239 reputation)

Group: General Forum Members
Points: 239 Visits: 136
Here is a good article from SSC on MAXDOP

http://www.sqlservercentral.com/articles/Configuring/managingmaxdegreeofparallelism/1029/
Marios Philippopoulos
Marios Philippopoulos
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33090 Visits: 3784
If multiple processors being used by your system's workload is a problem, it might be indicative of badly written queries and/or missing indexes.

On the other hand, if your system functions mainly as an OLAP/reporting environment, then parallel processing might actually be of benefit.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
bruce.l.pettus
bruce.l.pettus
SSC Veteran
SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)SSC Veteran (242 reputation)

Group: General Forum Members
Points: 242 Visits: 84
I've had two cases in which default parallelism has resulted in poor performance. The first was in a DBREINDEX on an index with a datetime column and the other was some performance testing of a particular query. Without going into too much detail, the end result was that both operations were faster when MAXDOP was set to 8 or less (systems were running with 16 CPUs). The DBREINDEX operation would actually peg all CPUs at 100%.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search