Post reply

Use or not use all available CPUs or a single CPU

  • June 2, 2009 at 3:32 am

    #134726

    Dear All,

    Is there any setting in sql server to use or not use all available CPUs or a single CPU .

    Can I change the default setting in SQL Server to limit SQL Server's ability to use all of the CPUs in the server.

    Thanks

  • June 2, 2009 at 3:48 am

    #1003258

    Yup. The setting is 'max degree of parallelism'. If it's 0, SQL will use as many of the CPUs as it feels necessary for a query. If you set it to some other number (up to the number of processors in the system), then SQL will only use up to that number for queries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 2, 2009 at 3:52 am

    #1003261

    Thanx....Gila.....You are near to my bests.

    Should I leave it Or it could give me better performance....My servers have 2 cpus of Intel Xeon..

    Thanks

  • June 2, 2009 at 4:03 am

    #1003270

    Hello Ajay,

    First of all How many CPU SQL Server should use on your machine it depends on the Licence that you have. As you mentioned you have 2 CPU on the box so if you have licence of 2 CPU then you can run SQL Server on both of them.

    Now when SQL Server processes a query then it decided whether to use both of the CPU or a single only. This setting you can change using MAX DEGREE OF PARALLELISM. By default its 0 which means default/unlimited

    which means it will use CPU which OS reports and SQL uses it. If you don't want to use both of CPU for Parallem processing then you can set this value to 1 whcih means ommit Parallelism.

    I hope this will answer your thing else please let me know.

    Regards

    GURSETHI

    +91-9900113618

  • June 2, 2009 at 4:10 am

    #1003278

    Dear That's True,But How will I decide that When I should use it?

    Thanks

  • June 2, 2009 at 4:25 am

    #1003284

    This is the real question :

    When to decide that I have to touch MAX DOP settings?

    Now normally when we saw that in SYSPROCESSES output continously we are seeing that queries are waiting on CXPACKET (and its ongoing process...) that means your queries are hitting Parallelism. Now even in these situations we need to identify that whether it is happening for only one of the query, a handfull of queries or for the over all system.

    As MAXDOP change using SP_CONFIGURE has a Instance wide effect (all database in that instance) so no one will suggest to make these changes without testing/investigating.

    If we are seeing couple of queries are having the issue then we can try passing MAXDOP hint to that specific query/Queries.

    Regards

    GURSETHI

    +91-9900113618

  • June 2, 2009 at 4:45 am

    #1003289

    Thanx sethi u have given a valuable answer

    Thanks

  • June 3, 2009 at 6:43 am

    #1003938

    Just wanted to add that the MAXDOP setting is per process. If that's what you're looking for - Great !. There's another setting that tells SQL to always use only x-number of processors all the time no matter what. That's the cpu affinity mask.

    You can turn a cpu on/off in the Server - Properties - Processors

    or

    use the "affinity I/O mask" in sp_configure.

    If this is strictly a Database server I doubt you'd want to do that though.

    Tim White

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

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