parallelism

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » parallelism

parallelism

Rate Topic

Display Mode

Topic Options

Author

Message

MarvinTheAndriod

Posted Tuesday, December 16, 2008 3:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:13 AM
Points: 284, Visits: 1,200

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?

Post #620250

Mr James Howard

Posted Tuesday, December 16, 2008 3:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 17, 2012 1:13 AM
Points: 190, Visits: 9,356

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

James Howard

Post #620274

MarvinTheAndriod

Posted Tuesday, December 16, 2008 3:59 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:13 AM
Points: 284, Visits: 1,200

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

Post #620277

MANU-J.

Posted Tuesday, December 16, 2008 5:22 AM

SSCommitted

Group: General Forum Members
Last Login: Monday, June 10, 2013 5:07 AM
Points: 1,658, Visits: 8,570

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

Post #620321

rshafer

Posted Wednesday, December 17, 2008 11:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 1:25 PM
Points: 105, 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)

Post #621476

rshafer

Posted Wednesday, December 17, 2008 11:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 1:25 PM
Points: 105, Visits: 136

Here is a good article from SSC on MAXDOP

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

Post #621483

Marios Philippopoulos

Posted Wednesday, December 17, 2008 11:17 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:25 PM
Points: 1,825, Visits: 3,484

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.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #621490

bruce.l.pettus

Posted Wednesday, December 17, 2008 12:27 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 11, 2012 4:29 AM
Points: 12, Visits: 79

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

Post #621542

« Prev Topic | Next Topic »

Permissions