parallelism

Question

Post reply

parallelism

MarvinTheAndriod

SSCarpal Tunnel

Points: 4972
More actions
December 16, 2008 at 3:01 am

#155028

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?

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

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

Mr James Howard Ten Centuries Points: 1158 More actions · Answer 1

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

James Howard

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 2

Thanks. I was wondering though is it possible to set a different level of parrellism at database level rather than server level ?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

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 SSChasing Mays Points: 655 More actions · Answer 4

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 SSChasing Mays Points: 655 More actions · Answer 5

Here is a good article from SSC on MAXDOP

http://www.sqlservercentral.com/articles/Configuring/managingmaxdegreeofparallelism/1029/

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

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[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

bruce.l.pettus Old Hand Points: 366 More actions · Answer 7

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%.