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 (AT) discussions (DOT) microsoft.com) writes:
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 (AT) sommarskog (DOT) 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/prodinf...ons/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 (AT) sommarskog (DOT) se> wrote

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=...oback=%2E myg



"RG" <nobody (AT) nowhere (DOT) com> 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 (AT) nospam (DOT) org> wrote

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=...oback=%2E myg



"RG" <nobody (AT) nowhere (DOT) com> wrote

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:

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=...oback=%2E myg



"Gert-Jan Strik" <sorrytoomuchspamalready (AT) xs4all (DOT) nl> wrote

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:

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...discussed.aspx

"Jay Konigsberg" <spam (AT) nospam (DOT) org> wrote