Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

parallelism Expand / Collapse
Author
Message
Posted Tuesday, December 16, 2008 3:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:51 AM
Points: 295, Visits: 1,253
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
Posted Tuesday, December 16, 2008 3:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, December 16, 2008 3:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 9:51 AM
Points: 295, Visits: 1,253

Thanks. I was wondering though is it possible to set a different level of parrellism at database level rather than server level ?
Post #620277
Posted Tuesday, December 16, 2008 5:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Posted Wednesday, December 17, 2008 11:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 4, 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
Posted Wednesday, December 17, 2008 11:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 4, 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
Posted Wednesday, December 17, 2008 11:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 1,863, Visits: 3,611
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 2
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
Posted Wednesday, December 17, 2008 12:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 3:37 PM
Points: 12, 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%.

Post #621542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse