none
Reg:Degree of Parallelism...

    Question

  • Hi Team,

    In the following picture what does Degree of Parallisim indicate for?

    Thanks in Advance.

    Wednesday, July 11, 2018 11:42 AM

All replies

  • The Degree of Parallelism is the number of workers, or the number of processors, that are assigned for the parallel plan to accomplish the worker task.

    https://www.mssqltips.com/sqlservertip/5404/parallelism-in-sql-server-execution-plan/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, July 11, 2018 11:47 AM
    Answerer
  • It just indicates number of processors (this case 1) used for executing the query

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 11, 2018 11:48 AM
  • The optimizer always tries to go for a serial plan (using single thread) but if the cost of a serial plan is higher than the value set for cost threshold for parallelism (default is 5 seconds (not clock based)), it generates a parallel plan (using more than one thread). Both plans are then compared and the cheaper one of the two is selected.

    In your example above, it looks like it went with a serial plan. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Wednesday, July 11, 2018 12:58 PM
  • Hi Visakh,

     thanq for your reply.

    can i know is this related to core or processor .as per my understanding one processor can contain multiple cores.

    in sql server how can i see number of cores assigned for sql server?

                                       and

    in sql server how can i see number of processors assigned for sql server ?

    Thanks in Advance,

    Ramesh

    Wednesday, July 11, 2018 1:25 PM
  • Hi Mohsin,

    thanq for your reply.

    can i know what is meant by single thread and multi thread in sql server ?

    Thanks in Advance,

    Ramesh

    Wednesday, July 11, 2018 1:26 PM
  • Hi Visakh,

     thanq for your reply.

    can i know is this related to core or processor .as per my understanding one processor can contain multiple cores.

    in sql server how can i see number of cores assigned for sql server?

                                       and

    in sql server how can i see number of processors assigned for sql server ?

    Thanks in Advance,

    Ramesh

    you can check this from instance level properties by right clicking on servername in SSMS and choosing properties

    core represents logical cpus

    so dual core means 2 CPUs, quad core 4 CPUs etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, July 11, 2018 1:40 PM
  • Hi Visakh,

     thanq for your reply.

    can i know is this related to core or processor .as per my understanding one processor can contain multiple cores.

    in sql server how can i see number of cores assigned for sql server?

                                       and

    in sql server how can i see number of processors assigned for sql server ?

    Thanks in Advance,

    Ramesh

    1) One processor-->multiple cores-->each physical core has two logical cores (if hyperthreading is enabled)-->each logical processor has one scheduler-->each scheduler has x number of threads. SQL properly distributes the threads among all schedulers.

    2) To see the number of cores on the server, a) Start task manager-->performance tab OR b) enter "msinfo32.exe" in run.  c) right click "My Computer"-->properties

    SQL can use all the available cores to the OS (Of course unless you have processor affinity set etc.)

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, July 11, 2018 1:47 PM
  • Hi Mohsin,

    thanq for your reply.

    can i know what is meant by single thread and multi thread in sql server ?

    Thanks in Advance,

    Ramesh

    Here's a great article explaining those terms and much more. I'd suggest going through it for a better understanding. 

    https://www.red-gate.com/simple-talk/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, July 11, 2018 1:58 PM
  • Hi Mohsin,

    thanq for your reply.

    can i know what is meant by single thread and multi thread in sql server ?

    Thanks in Advance,

    Ramesh

    Its simple single thread mean one thread is used to do a work and multiple thread means multiple threads. If you want to know what is thread in sql server read Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests – what does it all mean?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, July 12, 2018 7:22 AM
    Moderator