parallelism

  • 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?

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

    James Howard

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

  • 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

  • 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)

  • Here is a good article from SSC on MAXDOP

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

  • 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]

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

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

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