Is there guidelines, suggestion or could somebody point me to articles which 
discuss how to determine the optimum degree of parallelism?

I suppose the biggest factor is going to be cxpacket waits.  My problem is 
how do I determine when cxpacket waits exceed the treshold.

Thanks in advance

RG (RG@discussions.microsoft.com) writes:
> Is there guidelines, suggestion or could somebody point me to articles
> which discuss how to determine the optimum degree of parallelism? 
> 
> I suppose the biggest factor is going to be cxpacket waits.  My problem is 
> how do I determine when cxpacket waits exceed the treshold.
 
It depends largely on what kind of system you have. Many claim that for
a pure OLTP system the optimal number is 1. For an OLAP system, the
number might be 0, that is use all processors.

I think the most decisive is not the CXPACKET waits, but how many CPU:s
you want to give to a single user. A perfectly parallel query, with
no CXPACKET waits that runs over all processors is on fun the other
users of the system.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Do you mean physical or in case of quad core, it woudl be 4 processors?

Are you saying you don't even need to multiplex the drives or have drives 
striped?

If so, wouldn't be a bottle neck.

Thanks again
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9D37983D61BYazorman@127.0.0.1...
> RG (RG@discussions.microsoft.com) writes:
>> Is there guidelines, suggestion or could somebody point me to articles
>> which discuss how to determine the optimum degree of parallelism?
>>
>> I suppose the biggest factor is going to be cxpacket waits.  My problem 
>> is
>> how do I determine when cxpacket waits exceed the treshold.
>
> It depends largely on what kind of system you have. Many claim that for
> a pure OLTP system the optimal number is 1. For an OLAP system, the
> number might be 0, that is use all processors.
>
> I think the most decisive is not the CXPACKET waits, but how many CPU:s
> you want to give to a single user. A perfectly parallel query, with
> no CXPACKET waits that runs over all processors is on fun the other
> users of the system.
>
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 

A Quad core processor will count a 4 CPU's by SQL Server (kinda cool huh).

I'm not sure what you mean by multiplex, but striping will not gain you 
anything from the multi-threading perspective.

If you can put tables being joined on different drives, or even better, 
partition them across different drives, then the Query Optimizer can run 
more threads on your query and spread the I/O out even further. Perhaps this 
is what you mean by multiplex?

-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"RG" <nobody@nowhere.com> wrote in message 
news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
> Do you mean physical or in case of quad core, it woudl be 4 processors?
>
> Are you saying you don't even need to multiplex the drives or have drives 
> striped?
>
> If so, wouldn't be a bottle neck.
>
> Thanks again
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
> news:Xns9D37983D61BYazorman@127.0.0.1...
>> RG (RG@discussions.microsoft.com) writes:
>>> Is there guidelines, suggestion or could somebody point me to articles
>>> which discuss how to determine the optimum degree of parallelism?
>>>
>>> I suppose the biggest factor is going to be cxpacket waits.  My problem 
>>> is
>>> how do I determine when cxpacket waits exceed the treshold.
>>
>> It depends largely on what kind of system you have. Many claim that for
>> a pure OLTP system the optimal number is 1. For an OLAP system, the
>> number might be 0, that is use all processors.
>>
>> I think the most decisive is not the CXPACKET waits, but how many CPU:s
>> you want to give to a single user. A perfectly parallel query, with
>> no CXPACKET waits that runs over all processors is on fun the other
>> users of the system.
>>
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: 
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
> 

Are you saying that in sql server it is better to have, as an example, 2 db 
files on two separate physical drives rather than having one db file on raid 
drive which is striped across 2 physical drives?
"Jay Konigsberg" <spam@nospam.org> wrote in message 
news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
>A Quad core processor will count a 4 CPU's by SQL Server (kinda cool huh).
>
> I'm not sure what you mean by multiplex, but striping will not gain you 
> anything from the multi-threading perspective.
>
> If you can put tables being joined on different drives, or even better, 
> partition them across different drives, then the Query Optimizer can run 
> more threads on your query and spread the I/O out even further. Perhaps 
> this is what you mean by multiplex?
>
> -- 
> Jay Konigsberg
> SQL Server DBA in Sacramento, CA
> http://www.linkedin.com/in/jaykonigsberg
>
> Live in Sacramento, CA?
> Join the Sacramento SQL Server User Group on LinkedIn
> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>
>
>
> "RG" <nobody@nowhere.com> wrote in message 
> news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
>> Do you mean physical or in case of quad core, it woudl be 4 processors?
>>
>> Are you saying you don't even need to multiplex the drives or have drives 
>> striped?
>>
>> If so, wouldn't be a bottle neck.
>>
>> Thanks again
>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
>> news:Xns9D37983D61BYazorman@127.0.0.1...
>>> RG (RG@discussions.microsoft.com) writes:
>>>> Is there guidelines, suggestion or could somebody point me to articles
>>>> which discuss how to determine the optimum degree of parallelism?
>>>>
>>>> I suppose the biggest factor is going to be cxpacket waits.  My problem 
>>>> is
>>>> how do I determine when cxpacket waits exceed the treshold.
>>>
>>> It depends largely on what kind of system you have. Many claim that for
>>> a pure OLTP system the optimal number is 1. For an OLAP system, the
>>> number might be 0, that is use all processors.
>>>
>>> I think the most decisive is not the CXPACKET waits, but how many CPU:s
>>> you want to give to a single user. A perfectly parallel query, with
>>> no CXPACKET waits that runs over all processors is on fun the other
>>> users of the system.
>>>
>>>
>>> -- 
>>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>>
>>> Links for SQL Server Books Online:
>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>> SQL 2000: 
>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>>
>>
>
> 

It depends on exactly how the tables are setup and how they are used. 
However, if you architect the tables right and you have the multiple 
volumes, then yes, it can make a huge difference in performance when your 
server is under load. Not so much on an idle system though.


-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"RG" <nobody@nowhere.com> wrote in message 
news:4AEDDA6F-FFEB-4194-9C8A-BE11AC4D5EF5@microsoft.com...
> Are you saying that in sql server it is better to have, as an example, 2 
> db files on two separate physical drives rather than having one db file on 
> raid drive which is striped across 2 physical drives?
> "Jay Konigsberg" <spam@nospam.org> wrote in message 
> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
>>A Quad core processor will count a 4 CPU's by SQL Server (kinda cool huh).
>>
>> I'm not sure what you mean by multiplex, but striping will not gain you 
>> anything from the multi-threading perspective.
>>
>> If you can put tables being joined on different drives, or even better, 
>> partition them across different drives, then the Query Optimizer can run 
>> more threads on your query and spread the I/O out even further. Perhaps 
>> this is what you mean by multiplex?
>>
>> -- 
>> Jay Konigsberg
>> SQL Server DBA in Sacramento, CA
>> http://www.linkedin.com/in/jaykonigsberg
>>
>> Live in Sacramento, CA?
>> Join the Sacramento SQL Server User Group on LinkedIn
>> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>>
>>
>>
>> "RG" <nobody@nowhere.com> wrote in message 
>> news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
>>> Do you mean physical or in case of quad core, it woudl be 4 processors?
>>>
>>> Are you saying you don't even need to multiplex the drives or have 
>>> drives striped?
>>>
>>> If so, wouldn't be a bottle neck.
>>>
>>> Thanks again
>>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
>>> news:Xns9D37983D61BYazorman@127.0.0.1...
>>>> RG (RG@discussions.microsoft.com) writes:
>>>>> Is there guidelines, suggestion or could somebody point me to articles
>>>>> which discuss how to determine the optimum degree of parallelism?
>>>>>
>>>>> I suppose the biggest factor is going to be cxpacket waits.  My 
>>>>> problem is
>>>>> how do I determine when cxpacket waits exceed the treshold.
>>>>
>>>> It depends largely on what kind of system you have. Many claim that for
>>>> a pure OLTP system the optimal number is 1. For an OLAP system, the
>>>> number might be 0, that is use all processors.
>>>>
>>>> I think the most decisive is not the CXPACKET waits, but how many CPU:s
>>>> you want to give to a single user. A perfectly parallel query, with
>>>> no CXPACKET waits that runs over all processors is on fun the other
>>>> users of the system.
>>>>
>>>>
>>>> -- 
>>>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>>>
>>>> Links for SQL Server Books Online:
>>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>>> SQL 2000: 
>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>>>
>>>
>>
>>
> 

RG,

It depends. Specifically what you want to optimize.

If there is one big query (OLAP type scenario) that trumps the
importance of all other queries, and you are running in single user
mode, and the query selects from two table (for example with a Merge
Join or Hash Join) where the tables are about the same size, then two
files on two separate drives, each with one of the tables would be the
ideal situation, because each drive would work separately and can
maximize sequential reads.

If any of the "ifs" is not present, the situation gets more complicated,
and the chance increases that you are not balancing the I/O equally over
all available drives. If the I/O is not balanced, you will reach the
point of suboptimal performance very fast, because it is only the cost
difference between random and sequential read that can be gained.
Otherwise, you are probably better off striping over the available
drives. At least for your general purpose data files.

If you have sufficient I/O, then it is a good idea to create multiple
files. The rule of thumb is to create one file for each CPU core that is
available to SQL Server. That allows better parallellism.

-- 
Gert-Jan


RG wrote:
> 
> Are you saying that in sql server it is better to have, as an example, 2 db
> files on two separate physical drives rather than having one db file on raid
> drive which is striped across 2 physical drives?
> "Jay Konigsberg" <spam@nospam.org> wrote in message
> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
> >A Quad core processor will count a 4 CPU's by SQL Server (kinda cool huh).
> >
> > I'm not sure what you mean by multiplex, but striping will not gain you
> > anything from the multi-threading perspective.
> >
> > If you can put tables being joined on different drives, or even better,
> > partition them across different drives, then the Query Optimizer can run
> > more threads on your query and spread the I/O out even further. Perhaps
> > this is what you mean by multiplex?
> >
> > --
> > Jay Konigsberg
> > SQL Server DBA in Sacramento, CA
> > http://www.linkedin.com/in/jaykonigsberg
> >
> > Live in Sacramento, CA?
> > Join the Sacramento SQL Server User Group on LinkedIn
> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
> >
> >
> >
> > "RG" <nobody@nowhere.com> wrote in message
> > news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
> >> Do you mean physical or in case of quad core, it woudl be 4 processors?
> >>
> >> Are you saying you don't even need to multiplex the drives or have drives
> >> striped?
> >>
> >> If so, wouldn't be a bottle neck.
> >>
> >> Thanks again
> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> >> news:Xns9D37983D61BYazorman@127.0.0.1...
> >>> RG (RG@discussions.microsoft.com) writes:
> >>>> Is there guidelines, suggestion or could somebody point me to articles
> >>>> which discuss how to determine the optimum degree of parallelism?
> >>>>
> >>>> I suppose the biggest factor is going to be cxpacket waits.  My problem
> >>>> is
> >>>> how do I determine when cxpacket waits exceed the treshold.
> >>>
> >>> It depends largely on what kind of system you have. Many claim that for
> >>> a pure OLTP system the optimal number is 1. For an OLAP system, the
> >>> number might be 0, that is use all processors.
> >>>
> >>> I think the most decisive is not the CXPACKET waits, but how many CPU:s
> >>> you want to give to a single user. A perfectly parallel query, with
> >>> no CXPACKET waits that runs over all processors is on fun the other
> >>> users of the system.
> >>>
> >>>
> >>> --
> >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >>>
> >>> Links for SQL Server Books Online:
> >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> >>> SQL 2000:
> >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> >>>
> >>
> >
> >

> If you have sufficient I/O, then it is a good idea to create multiple
> files. The rule of thumb is to create one file for each CPU core that is
> available to SQL Server. That allows better parallelism.

Unless you partition the file across multiple drives where a query will only 
be accessing data on a particular partition. Then making more partitions 
than the number of CPU's also makes sense. However, partitioning is probably 
out of the OP's scope.

-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4B97EC60.6B82D4B4@xs4all.nl...
> RG,
>
> It depends. Specifically what you want to optimize.
>
> If there is one big query (OLAP type scenario) that trumps the
> importance of all other queries, and you are running in single user
> mode, and the query selects from two table (for example with a Merge
> Join or Hash Join) where the tables are about the same size, then two
> files on two separate drives, each with one of the tables would be the
> ideal situation, because each drive would work separately and can
> maximize sequential reads.
>
> If any of the "ifs" is not present, the situation gets more complicated,
> and the chance increases that you are not balancing the I/O equally over
> all available drives. If the I/O is not balanced, you will reach the
> point of suboptimal performance very fast, because it is only the cost
> difference between random and sequential read that can be gained.
> Otherwise, you are probably better off striping over the available
> drives. At least for your general purpose data files.
>
> If you have sufficient I/O, then it is a good idea to create multiple
> files. The rule of thumb is to create one file for each CPU core that is
> available to SQL Server. That allows better parallellism.
>
> -- 
> Gert-Jan
>
>
> RG wrote:
>>
>> Are you saying that in sql server it is better to have, as an example, 2 
>> db
>> files on two separate physical drives rather than having one db file on 
>> raid
>> drive which is striped across 2 physical drives?
>> "Jay Konigsberg" <spam@nospam.org> wrote in message
>> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
>> >A Quad core processor will count a 4 CPU's by SQL Server (kinda cool 
>> >huh).
>> >
>> > I'm not sure what you mean by multiplex, but striping will not gain you
>> > anything from the multi-threading perspective.
>> >
>> > If you can put tables being joined on different drives, or even better,
>> > partition them across different drives, then the Query Optimizer can 
>> > run
>> > more threads on your query and spread the I/O out even further. Perhaps
>> > this is what you mean by multiplex?
>> >
>> > --
>> > Jay Konigsberg
>> > SQL Server DBA in Sacramento, CA
>> > http://www.linkedin.com/in/jaykonigsberg
>> >
>> > Live in Sacramento, CA?
>> > Join the Sacramento SQL Server User Group on LinkedIn
>> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>> >
>> >
>> >
>> > "RG" <nobody@nowhere.com> wrote in message
>> > news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
>> >> Do you mean physical or in case of quad core, it woudl be 4 
>> >> processors?
>> >>
>> >> Are you saying you don't even need to multiplex the drives or have 
>> >> drives
>> >> striped?
>> >>
>> >> If so, wouldn't be a bottle neck.
>> >>
>> >> Thanks again
>> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
>> >> news:Xns9D37983D61BYazorman@127.0.0.1...
>> >>> RG (RG@discussions.microsoft.com) writes:
>> >>>> Is there guidelines, suggestion or could somebody point me to 
>> >>>> articles
>> >>>> which discuss how to determine the optimum degree of parallelism?
>> >>>>
>> >>>> I suppose the biggest factor is going to be cxpacket waits.  My 
>> >>>> problem
>> >>>> is
>> >>>> how do I determine when cxpacket waits exceed the treshold.
>> >>>
>> >>> It depends largely on what kind of system you have. Many claim that 
>> >>> for
>> >>> a pure OLTP system the optimal number is 1. For an OLAP system, the
>> >>> number might be 0, that is use all processors.
>> >>>
>> >>> I think the most decisive is not the CXPACKET waits, but how many 
>> >>> CPU:s
>> >>> you want to give to a single user. A perfectly parallel query, with
>> >>> no CXPACKET waits that runs over all processors is on fun the other
>> >>> users of the system.
>> >>>
>> >>>
>> >>> --
>> >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>> >>>
>> >>> Links for SQL Server Books Online:
>> >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> >>> SQL 2000:
>> >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> >>>
>> >>
>> >
>> > 

I solved the problem by myself (probably not the best way, but it works...)

// Jocke


Select		ID,
			Category = min(Category),
			from_Date = Min(from_Date),
			to_Date = max(to_Date)
from
(SELECT		ID,
			Category,
			from_Date,
			to_Date,
			Brejk = coalesce((select min(from_Date) from Role x where x.ID = a.ID 
																and x.from_Date > a.from_Date
																and	x.Category <> a.Category),
							(select max(to_Date) from Role x where x.ID = a.ID))
						
FROM		Role a) z
group by ID, Brejk
order by 1,3

"RG" wrote:

> Are you saying that in sql server it is better to have, as an example, 2 db 
> files on two separate physical drives rather than having one db file on raid 
> drive which is striped across 2 physical drives?
> "Jay Konigsberg" <spam@nospam.org> wrote in message 
> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
> >A Quad core processor will count a 4 CPU's by SQL Server (kinda cool huh).
> >
> > I'm not sure what you mean by multiplex, but striping will not gain you 
> > anything from the multi-threading perspective.
> >
> > If you can put tables being joined on different drives, or even better, 
> > partition them across different drives, then the Query Optimizer can run 
> > more threads on your query and spread the I/O out even further. Perhaps 
> > this is what you mean by multiplex?
> >
> > -- 
> > Jay Konigsberg
> > SQL Server DBA in Sacramento, CA
> > http://www.linkedin.com/in/jaykonigsberg
> >
> > Live in Sacramento, CA?
> > Join the Sacramento SQL Server User Group on LinkedIn
> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
> >
> >
> >
> > "RG" <nobody@nowhere.com> wrote in message 
> > news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
> >> Do you mean physical or in case of quad core, it woudl be 4 processors?
> >>
> >> Are you saying you don't even need to multiplex the drives or have drives 
> >> striped?
> >>
> >> If so, wouldn't be a bottle neck.
> >>
> >> Thanks again
> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
> >> news:Xns9D37983D61BYazorman@127.0.0.1...
> >>> RG (RG@discussions.microsoft.com) writes:
> >>>> Is there guidelines, suggestion or could somebody point me to articles
> >>>> which discuss how to determine the optimum degree of parallelism?
> >>>>
> >>>> I suppose the biggest factor is going to be cxpacket waits.  My problem 
> >>>> is
> >>>> how do I determine when cxpacket waits exceed the treshold.
> >>>
> >>> It depends largely on what kind of system you have. Many claim that for
> >>> a pure OLTP system the optimal number is 1. For an OLAP system, the
> >>> number might be 0, that is use all processors.
> >>>
> >>> I think the most decisive is not the CXPACKET waits, but how many CPU:s
> >>> you want to give to a single user. A perfectly parallel query, with
> >>> no CXPACKET waits that runs over all processors is on fun the other
> >>> users of the system.
> >>>
> >>>
> >>> -- 
> >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >>>
> >>> Links for SQL Server Books Online:
> >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> >>> SQL 2000: 
> >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> >>>
> >>
> >
> > 
> 

I hear this all the time, and still can't believe it is being stated this 
way.  You should review the following:

http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

"Jay Konigsberg" <spam@nospam.org> wrote in message 
news:ODRBNPJwKHA.3764@TK2MSFTNGP04.phx.gbl...
>> If you have sufficient I/O, then it is a good idea to create multiple
>> files. The rule of thumb is to create one file for each CPU core that is
>> available to SQL Server. That allows better parallelism.
>
> Unless you partition the file across multiple drives where a query will 
> only be accessing data on a particular partition. Then making more 
> partitions than the number of CPU's also makes sense. However, 
> partitioning is probably out of the OP's scope.
>
> -- 
> Jay Konigsberg
> SQL Server DBA in Sacramento, CA
> http://www.linkedin.com/in/jaykonigsberg
>
> Live in Sacramento, CA?
> Join the Sacramento SQL Server User Group on LinkedIn
> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>
>
>
> "Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
> news:4B97EC60.6B82D4B4@xs4all.nl...
>> RG,
>>
>> It depends. Specifically what you want to optimize.
>>
>> If there is one big query (OLAP type scenario) that trumps the
>> importance of all other queries, and you are running in single user
>> mode, and the query selects from two table (for example with a Merge
>> Join or Hash Join) where the tables are about the same size, then two
>> files on two separate drives, each with one of the tables would be the
>> ideal situation, because each drive would work separately and can
>> maximize sequential reads.
>>
>> If any of the "ifs" is not present, the situation gets more complicated,
>> and the chance increases that you are not balancing the I/O equally over
>> all available drives. If the I/O is not balanced, you will reach the
>> point of suboptimal performance very fast, because it is only the cost
>> difference between random and sequential read that can be gained.
>> Otherwise, you are probably better off striping over the available
>> drives. At least for your general purpose data files.
>>
>> If you have sufficient I/O, then it is a good idea to create multiple
>> files. The rule of thumb is to create one file for each CPU core that is
>> available to SQL Server. That allows better parallellism.
>>
>> -- 
>> Gert-Jan
>>
>>
>> RG wrote:
>>>
>>> Are you saying that in sql server it is better to have, as an example, 2 
>>> db
>>> files on two separate physical drives rather than having one db file on 
>>> raid
>>> drive which is striped across 2 physical drives?
>>> "Jay Konigsberg" <spam@nospam.org> wrote in message
>>> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
>>> >A Quad core processor will count a 4 CPU's by SQL Server (kinda cool 
>>> >huh).
>>> >
>>> > I'm not sure what you mean by multiplex, but striping will not gain 
>>> > you
>>> > anything from the multi-threading perspective.
>>> >
>>> > If you can put tables being joined on different drives, or even 
>>> > better,
>>> > partition them across different drives, then the Query Optimizer can 
>>> > run
>>> > more threads on your query and spread the I/O out even further. 
>>> > Perhaps
>>> > this is what you mean by multiplex?
>>> >
>>> > --
>>> > Jay Konigsberg
>>> > SQL Server DBA in Sacramento, CA
>>> > http://www.linkedin.com/in/jaykonigsberg
>>> >
>>> > Live in Sacramento, CA?
>>> > Join the Sacramento SQL Server User Group on LinkedIn
>>> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>>> >
>>> >
>>> >
>>> > "RG" <nobody@nowhere.com> wrote in message
>>> > news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
>>> >> Do you mean physical or in case of quad core, it woudl be 4 
>>> >> processors?
>>> >>
>>> >> Are you saying you don't even need to multiplex the drives or have 
>>> >> drives
>>> >> striped?
>>> >>
>>> >> If so, wouldn't be a bottle neck.
>>> >>
>>> >> Thanks again
>>> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
>>> >> news:Xns9D37983D61BYazorman@127.0.0.1...
>>> >>> RG (RG@discussions.microsoft.com) writes:
>>> >>>> Is there guidelines, suggestion or could somebody point me to 
>>> >>>> articles
>>> >>>> which discuss how to determine the optimum degree of parallelism?
>>> >>>>
>>> >>>> I suppose the biggest factor is going to be cxpacket waits.  My 
>>> >>>> problem
>>> >>>> is
>>> >>>> how do I determine when cxpacket waits exceed the treshold.
>>> >>>
>>> >>> It depends largely on what kind of system you have. Many claim that 
>>> >>> for
>>> >>> a pure OLTP system the optimal number is 1. For an OLAP system, the
>>> >>> number might be 0, that is use all processors.
>>> >>>
>>> >>> I think the most decisive is not the CXPACKET waits, but how many 
>>> >>> CPU:s
>>> >>> you want to give to a single user. A perfectly parallel query, with
>>> >>> no CXPACKET waits that runs over all processors is on fun the other
>>> >>> users of the system.
>>> >>>
>>> >>>
>>> >>> --
>>> >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>> >>>
>>> >>> Links for SQL Server Books Online:
>>> >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>> >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>> >>> SQL 2000:
>>> >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>> >>>
>>> >>
>>> >
>>> >
>
> 

Well, interesting link and definitely worth reading. However, it is not what 
I was talking about.

Gert-Jan pointed out that when using multiple CPUs that the optimum was when 
there was one CPU per file (when there is one table per file and the I/O was 
balanced). All I was saying is, that table can reside in many files, if 
partitioning is used. This is because, for the purpose of a query that can 
isolate a particular partition, the specific partition is acting like the 
file a whole table is stored in.

The I/O benefits of partitioning is a whole other topic and is mostly 
dependant on the fact that hard drives are the slowest component of any 
system. Partitioning can spread that load out over multiple platters. The 
rest is about how one goes about it and has little to do with worker 
threads.

Jay

-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Jeffrey Williams" <jeff.williams3188@verizon.net> wrote in message 
news:30EFEAC0-6A28-4A29-8DF9-456F6BD0FA1A@microsoft.com...
>I hear this all the time, and still can't believe it is being stated this 
>way.  You should review the following:
>
> http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
>
> "Jay Konigsberg" <spam@nospam.org> wrote in message 
> news:ODRBNPJwKHA.3764@TK2MSFTNGP04.phx.gbl...
>>> If you have sufficient I/O, then it is a good idea to create multiple
>>> files. The rule of thumb is to create one file for each CPU core that is
>>> available to SQL Server. That allows better parallelism.
>>
>> Unless you partition the file across multiple drives where a query will 
>> only be accessing data on a particular partition. Then making more 
>> partitions than the number of CPU's also makes sense. However, 
>> partitioning is probably out of the OP's scope.
>>
>> -- 
>> Jay Konigsberg
>> SQL Server DBA in Sacramento, CA
>> http://www.linkedin.com/in/jaykonigsberg
>>
>> Live in Sacramento, CA?
>> Join the Sacramento SQL Server User Group on LinkedIn
>> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>>
>>
>>
>> "Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
>> news:4B97EC60.6B82D4B4@xs4all.nl...
>>> RG,
>>>
>>> It depends. Specifically what you want to optimize.
>>>
>>> If there is one big query (OLAP type scenario) that trumps the
>>> importance of all other queries, and you are running in single user
>>> mode, and the query selects from two table (for example with a Merge
>>> Join or Hash Join) where the tables are about the same size, then two
>>> files on two separate drives, each with one of the tables would be the
>>> ideal situation, because each drive would work separately and can
>>> maximize sequential reads.
>>>
>>> If any of the "ifs" is not present, the situation gets more complicated,
>>> and the chance increases that you are not balancing the I/O equally over
>>> all available drives. If the I/O is not balanced, you will reach the
>>> point of suboptimal performance very fast, because it is only the cost
>>> difference between random and sequential read that can be gained.
>>> Otherwise, you are probably better off striping over the available
>>> drives. At least for your general purpose data files.
>>>
>>> If you have sufficient I/O, then it is a good idea to create multiple
>>> files. The rule of thumb is to create one file for each CPU core that is
>>> available to SQL Server. That allows better parallellism.
>>>
>>> -- 
>>> Gert-Jan
>>>
>>>
>>> RG wrote:
>>>>
>>>> Are you saying that in sql server it is better to have, as an example, 
>>>> 2 db
>>>> files on two separate physical drives rather than having one db file on 
>>>> raid
>>>> drive which is striped across 2 physical drives?
>>>> "Jay Konigsberg" <spam@nospam.org> wrote in message
>>>> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
>>>> >A Quad core processor will count a 4 CPU's by SQL Server (kinda cool 
>>>> >huh).
>>>> >
>>>> > I'm not sure what you mean by multiplex, but striping will not gain 
>>>> > you
>>>> > anything from the multi-threading perspective.
>>>> >
>>>> > If you can put tables being joined on different drives, or even 
>>>> > better,
>>>> > partition them across different drives, then the Query Optimizer can 
>>>> > run
>>>> > more threads on your query and spread the I/O out even further. 
>>>> > Perhaps
>>>> > this is what you mean by multiplex?
>>>> >
>>>> > --
>>>> > Jay Konigsberg
>>>> > SQL Server DBA in Sacramento, CA
>>>> > http://www.linkedin.com/in/jaykonigsberg
>>>> >
>>>> > Live in Sacramento, CA?
>>>> > Join the Sacramento SQL Server User Group on LinkedIn
>>>> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>>>> >
>>>> >
>>>> >
>>>> > "RG" <nobody@nowhere.com> wrote in message
>>>> > news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
>>>> >> Do you mean physical or in case of quad core, it woudl be 4 
>>>> >> processors?
>>>> >>
>>>> >> Are you saying you don't even need to multiplex the drives or have 
>>>> >> drives
>>>> >> striped?
>>>> >>
>>>> >> If so, wouldn't be a bottle neck.
>>>> >>
>>>> >> Thanks again
>>>> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
>>>> >> news:Xns9D37983D61BYazorman@127.0.0.1...
>>>> >>> RG (RG@discussions.microsoft.com) writes:
>>>> >>>> Is there guidelines, suggestion or could somebody point me to 
>>>> >>>> articles
>>>> >>>> which discuss how to determine the optimum degree of parallelism?
>>>> >>>>
>>>> >>>> I suppose the biggest factor is going to be cxpacket waits.  My 
>>>> >>>> problem
>>>> >>>> is
>>>> >>>> how do I determine when cxpacket waits exceed the treshold.
>>>> >>>
>>>> >>> It depends largely on what kind of system you have. Many claim that 
>>>> >>> for
>>>> >>> a pure OLTP system the optimal number is 1. For an OLAP system, the
>>>> >>> number might be 0, that is use all processors.
>>>> >>>
>>>> >>> I think the most decisive is not the CXPACKET waits, but how many 
>>>> >>> CPU:s
>>>> >>> you want to give to a single user. A perfectly parallel query, with
>>>> >>> no CXPACKET waits that runs over all processors is on fun the other
>>>> >>> users of the system.
>>>> >>>
>>>> >>>
>>>> >>> --
>>>> >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>>> >>>
>>>> >>> Links for SQL Server Books Online:
>>>> >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>>> >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>>> >>> SQL 2000:
>>>> >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>>> >>>
>>>> >>
>>>> >
>>>> >
>>
>> 

Jeffrey,

You are right to question this. I looked in my archive (and at the
article), and have to correct my previous remark about that.

Of course it doesn't hurt to have multiple files for a database. However
there is only a potential performance gain for databases that create
and/or drop many database objects. Typically this only applies to TempDB
and not to your regular user database.

If you want to optimize databases with high levels of creates/drops,
then create multiple files that are equally sized. For SQL Server 2005,
2 files would be sufficient for up to 4 cores. For up to 16 cores 4
files would be sufficient. On earlier versions you might want to create
about as much files as there are cores.

-- 
Gert-Jan


Jeffrey Williams wrote:
> 
> I hear this all the time, and still can't believe it is being stated this
> way.  You should review the following:
> 
> http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
> 
> "Jay Konigsberg" <spam@nospam.org> wrote in message
> news:ODRBNPJwKHA.3764@TK2MSFTNGP04.phx.gbl...
> >> If you have sufficient I/O, then it is a good idea to create multiple
> >> files. The rule of thumb is to create one file for each CPU core that is
> >> available to SQL Server. That allows better parallelism.
> >
> > Unless you partition the file across multiple drives where a query will
> > only be accessing data on a particular partition. Then making more
> > partitions than the number of CPU's also makes sense. However,
> > partitioning is probably out of the OP's scope.
> >
> > --
> > Jay Konigsberg
> > SQL Server DBA in Sacramento, CA
> > http://www.linkedin.com/in/jaykonigsberg
> >
> > Live in Sacramento, CA?
> > Join the Sacramento SQL Server User Group on LinkedIn
> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
> >
> >
> >
> > "Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message
> > news:4B97EC60.6B82D4B4@xs4all.nl...
> >> RG,
> >>
> >> It depends. Specifically what you want to optimize.
> >>
> >> If there is one big query (OLAP type scenario) that trumps the
> >> importance of all other queries, and you are running in single user
> >> mode, and the query selects from two table (for example with a Merge
> >> Join or Hash Join) where the tables are about the same size, then two
> >> files on two separate drives, each with one of the tables would be the
> >> ideal situation, because each drive would work separately and can
> >> maximize sequential reads.
> >>
> >> If any of the "ifs" is not present, the situation gets more complicated,
> >> and the chance increases that you are not balancing the I/O equally over
> >> all available drives. If the I/O is not balanced, you will reach the
> >> point of suboptimal performance very fast, because it is only the cost
> >> difference between random and sequential read that can be gained.
> >> Otherwise, you are probably better off striping over the available
> >> drives. At least for your general purpose data files.
> >>
> >> If you have sufficient I/O, then it is a good idea to create multiple
> >> files. The rule of thumb is to create one file for each CPU core that is
> >> available to SQL Server. That allows better parallellism.
> >>
> >> --
> >> Gert-Jan
> >>
> >>
> >> RG wrote:
> >>>
> >>> Are you saying that in sql server it is better to have, as an example, 2
> >>> db
> >>> files on two separate physical drives rather than having one db file on
> >>> raid
> >>> drive which is striped across 2 physical drives?
> >>> "Jay Konigsberg" <spam@nospam.org> wrote in message
> >>> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
> >>> >A Quad core processor will count a 4 CPU's by SQL Server (kinda cool
> >>> >huh).
> >>> >
> >>> > I'm not sure what you mean by multiplex, but striping will not gain
> >>> > you
> >>> > anything from the multi-threading perspective.
> >>> >
> >>> > If you can put tables being joined on different drives, or even
> >>> > better,
> >>> > partition them across different drives, then the Query Optimizer can
> >>> > run
> >>> > more threads on your query and spread the I/O out even further.
> >>> > Perhaps
> >>> > this is what you mean by multiplex?
> >>> >
> >>> > --
> >>> > Jay Konigsberg
> >>> > SQL Server DBA in Sacramento, CA
> >>> > http://www.linkedin.com/in/jaykonigsberg
> >>> >
> >>> > Live in Sacramento, CA?
> >>> > Join the Sacramento SQL Server User Group on LinkedIn
> >>> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
> >>> >
> >>> >
> >>> >
> >>> > "RG" <nobody@nowhere.com> wrote in message
> >>> > news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
> >>> >> Do you mean physical or in case of quad core, it woudl be 4
> >>> >> processors?
> >>> >>
> >>> >> Are you saying you don't even need to multiplex the drives or have
> >>> >> drives
> >>> >> striped?
> >>> >>
> >>> >> If so, wouldn't be a bottle neck.
> >>> >>
> >>> >> Thanks again
> >>> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> >>> >> news:Xns9D37983D61BYazorman@127.0.0.1...
> >>> >>> RG (RG@discussions.microsoft.com) writes:
> >>> >>>> Is there guidelines, suggestion or could somebody point me to
> >>> >>>> articles
> >>> >>>> which discuss how to determine the optimum degree of parallelism?
> >>> >>>>
> >>> >>>> I suppose the biggest factor is going to be cxpacket waits.  My
> >>> >>>> problem
> >>> >>>> is
> >>> >>>> how do I determine when cxpacket waits exceed the treshold.
> >>> >>>
> >>> >>> It depends largely on what kind of system you have. Many claim that
> >>> >>> for
> >>> >>> a pure OLTP system the optimal number is 1. For an OLAP system, the
> >>> >>> number might be 0, that is use all processors.
> >>> >>>
> >>> >>> I think the most decisive is not the CXPACKET waits, but how many
> >>> >>> CPU:s
> >>> >>> you want to give to a single user. A perfectly parallel query, with
> >>> >>> no CXPACKET waits that runs over all processors is on fun the other
> >>> >>> users of the system.
> >>> >>>
> >>> >>>
> >>> >>> --
> >>> >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> >>> >>>
> >>> >>> Links for SQL Server Books Online:
> >>> >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> >>> >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> >>> >>> SQL 2000:
> >>> >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> >>> >>>
> >>> >>
> >>> >
> >>> >
> >
> >

Gert-Jan,

I do not think there is any need to correct yourself. Amend, enhance and add 
depth sure, but not correct.

The original question we both responded to stated: "2 db files on two 
separate physical drives."

Neither one of us were suggesting putting multiple files on the same 
physical device and that there would be a performance gain from it.

Is the link useful information? Yes. I've enjoyed reading it an am thankful 
for it. Does it directly apply to the thread? No.

Jay

-- 
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message 
news:4B99416B.93E22016@xs4all.nl...
> Jeffrey,
>
> You are right to question this. I looked in my archive (and at the
> article), and have to correct my previous remark about that.
>
> Of course it doesn't hurt to have multiple files for a database. However
> there is only a potential performance gain for databases that create
> and/or drop many database objects. Typically this only applies to TempDB
> and not to your regular user database.
>
> If you want to optimize databases with high levels of creates/drops,
> then create multiple files that are equally sized. For SQL Server 2005,
> 2 files would be sufficient for up to 4 cores. For up to 16 cores 4
> files would be sufficient. On earlier versions you might want to create
> about as much files as there are cores.
>
> -- 
> Gert-Jan
>
>
> Jeffrey Williams wrote:
>>
>> I hear this all the time, and still can't believe it is being stated this
>> way.  You should review the following:
>>
>> http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
>>
>> "Jay Konigsberg" <spam@nospam.org> wrote in message
>> news:ODRBNPJwKHA.3764@TK2MSFTNGP04.phx.gbl...
>> >> If you have sufficient I/O, then it is a good idea to create multiple
>> >> files. The rule of thumb is to create one file for each CPU core that 
>> >> is
>> >> available to SQL Server. That allows better parallelism.
>> >
>> > Unless you partition the file across multiple drives where a query will
>> > only be accessing data on a particular partition. Then making more
>> > partitions than the number of CPU's also makes sense. However,
>> > partitioning is probably out of the OP's scope.
>> >
>> > --
>> > Jay Konigsberg
>> > SQL Server DBA in Sacramento, CA
>> > http://www.linkedin.com/in/jaykonigsberg
>> >
>> > Live in Sacramento, CA?
>> > Join the Sacramento SQL Server User Group on LinkedIn
>> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>> >
>> >
>> >
>> > "Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> wrote in message
>> > news:4B97EC60.6B82D4B4@xs4all.nl...
>> >> RG,
>> >>
>> >> It depends. Specifically what you want to optimize.
>> >>
>> >> If there is one big query (OLAP type scenario) that trumps the
>> >> importance of all other queries, and you are running in single user
>> >> mode, and the query selects from two table (for example with a Merge
>> >> Join or Hash Join) where the tables are about the same size, then two
>> >> files on two separate drives, each with one of the tables would be the
>> >> ideal situation, because each drive would work separately and can
>> >> maximize sequential reads.
>> >>
>> >> If any of the "ifs" is not present, the situation gets more 
>> >> complicated,
>> >> and the chance increases that you are not balancing the I/O equally 
>> >> over
>> >> all available drives. If the I/O is not balanced, you will reach the
>> >> point of suboptimal performance very fast, because it is only the cost
>> >> difference between random and sequential read that can be gained.
>> >> Otherwise, you are probably better off striping over the available
>> >> drives. At least for your general purpose data files.
>> >>
>> >> If you have sufficient I/O, then it is a good idea to create multiple
>> >> files. The rule of thumb is to create one file for each CPU core that 
>> >> is
>> >> available to SQL Server. That allows better parallellism.
>> >>
>> >> --
>> >> Gert-Jan
>> >>
>> >>
>> >> RG wrote:
>> >>>
>> >>> Are you saying that in sql server it is better to have, as an 
>> >>> example, 2
>> >>> db
>> >>> files on two separate physical drives rather than having one db file 
>> >>> on
>> >>> raid
>> >>> drive which is striped across 2 physical drives?
>> >>> "Jay Konigsberg" <spam@nospam.org> wrote in message
>> >>> news:eOO6Kv$vKHA.4752@TK2MSFTNGP04.phx.gbl...
>> >>> >A Quad core processor will count a 4 CPU's by SQL Server (kinda cool
>> >>> >huh).
>> >>> >
>> >>> > I'm not sure what you mean by multiplex, but striping will not gain
>> >>> > you
>> >>> > anything from the multi-threading perspective.
>> >>> >
>> >>> > If you can put tables being joined on different drives, or even
>> >>> > better,
>> >>> > partition them across different drives, then the Query Optimizer 
>> >>> > can
>> >>> > run
>> >>> > more threads on your query and spread the I/O out even further.
>> >>> > Perhaps
>> >>> > this is what you mean by multiplex?
>> >>> >
>> >>> > --
>> >>> > Jay Konigsberg
>> >>> > SQL Server DBA in Sacramento, CA
>> >>> > http://www.linkedin.com/in/jaykonigsberg
>> >>> >
>> >>> > Live in Sacramento, CA?
>> >>> > Join the Sacramento SQL Server User Group on LinkedIn
>> >>> > http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>> >>> >
>> >>> >
>> >>> >
>> >>> > "RG" <nobody@nowhere.com> wrote in message
>> >>> > news:B89C58DE-F69C-4623-AF5A-AEF2D6F17306@microsoft.com...
>> >>> >> Do you mean physical or in case of quad core, it woudl be 4
>> >>> >> processors?
>> >>> >>
>> >>> >> Are you saying you don't even need to multiplex the drives or have
>> >>> >> drives
>> >>> >> striped?
>> >>> >>
>> >>> >> If so, wouldn't be a bottle neck.
>> >>> >>
>> >>> >> Thanks again
>> >>> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
>> >>> >> news:Xns9D37983D61BYazorman@127.0.0.1...
>> >>> >>> RG (RG@discussions.microsoft.com) writes:
>> >>> >>>> Is there guidelines, suggestion or could somebody point me to
>> >>> >>>> articles
>> >>> >>>> which discuss how to determine the optimum degree of 
>> >>> >>>> parallelism?
>> >>> >>>>
>> >>> >>>> I suppose the biggest factor is going to be cxpacket waits.  My
>> >>> >>>> problem
>> >>> >>>> is
>> >>> >>>> how do I determine when cxpacket waits exceed the treshold.
>> >>> >>>
>> >>> >>> It depends largely on what kind of system you have. Many claim 
>> >>> >>> that
>> >>> >>> for
>> >>> >>> a pure OLTP system the optimal number is 1. For an OLAP system, 
>> >>> >>> the
>> >>> >>> number might be 0, that is use all processors.
>> >>> >>>
>> >>> >>> I think the most decisive is not the CXPACKET waits, but how many
>> >>> >>> CPU:s
>> >>> >>> you want to give to a single user. A perfectly parallel query, 
>> >>> >>> with
>> >>> >>> no CXPACKET waits that runs over all processors is on fun the 
>> >>> >>> other
>> >>> >>> users of the system.
>> >>> >>>
>> >>> >>>
>> >>> >>> --
>> >>> >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>> >>> >>>
>> >>> >>> Links for SQL Server Books Online:
>> >>> >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> >>> >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> >>> >>> SQL 2000:
>> >>> >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> >>> >>>
>> >>> >>
>> >>> >
>> >>> >
>> >
>> > 

To add to other's statements, you also must adjust the Cost Threshold for 
Parallelism to get optimal parallel executions on the server.  The default 
of 5 is almost universally too low.

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"RG" <RG@discussions.microsoft.com> wrote in message 
news:D36471B0-BFC5-407E-8241-0EB93B272FAF@microsoft.com...
> Is there guidelines, suggestion or could somebody point me to articles 
> which
> discuss how to determine the optimum degree of parallelism?
>
> I suppose the biggest factor is going to be cxpacket waits.  My problem is
> how do I determine when cxpacket waits exceed the treshold.
>
> Thanks in advance