Parallel query processing. (Max Processors)

[Former Member]

Hi Guys,

We have a System running on SQL 2000, and it configured to use all available processors.

(Server properties>Processor)

I am about to change it to use 1 processor as per microsoft recommendation, but there another option when you select fixed number of processor instead of all available processors, which is

"Minimum query plan threshold for considering queries for parallel execution(cost estimate)"

http://picasaweb.google.com/lh/photo/8N0-WQt3m23vEiupxdjtwQ?feat=directlink

can anybody suggest me, how do you estimate this number. or you guys can post what is current threshold value in your SQL 2000 server environment. (I dont see this option in SQL 2005,probably its handled automatically).

Thanks Guys

[Former Member]

Hi my friend

Could you elaborate that which microsoft recommendation you're following?

Generally we should take full advantage in all processor for DB host, and moreover, the quantity of processor determines the quantity of SQL server data file.

Regards,

[Former Member]

Hi Fan Yu

In the above document, page 44 under section Max degree of parallesim, microsoft recommends this value to be set to 1.

(Reasons are discussed in the white paper)

Like i said, SQL 2000 has one more option regarding max degree of parallelism, i.e "Minimum query plan threshold for considering queries for parallel execution(cost estimate)"

http://picasaweb.google.com/lh/photo/8N0-WQt3m23vEiupxdjtwQ?feat=directlink

Probably i was not clear in my question, this is not related to number of processors and data files, but number of processors used by the query engine at a given time.(max degree of parallelism).

[Former Member]

My friend, which SAP application is this SQL server you want to change running for?

The general recommendation for SQL Server Instances running SAP OLTP (ECC, SRM) type system workloads is to set the max degree of parallelism option to 1, can also be set to 0 for tasks like creating aggregates in OLAP (BW and APO) systems.

For offline database administration tasks, set max degree of parallelism to 0. This will speed up tasks like creating indexes, rebuilding indexes (if you want to perform those offline), checkdb, and other maintenance work. Such tasks run faster in this configuration because they can leverage more CPUs.

By the way, SQL Server 2008 and SQL Server 2005 check the available resources such as processors, available worker threads, and memory to determine the number of processors available to service the query. Based on those checks, a query might not be executed using all CPU threads available, instead using only a few CPU threads (or even just one CPU thread).

Regards,

[Former Member]

Hi

Thanks for your valuable comments.

I am using ECC 5.0/SQL 2000.

I was talking about this option:

http://msdn.microsoft.com/en-us/library/aa196716(v=SQL.80).aspx

but anyways i just went through the article again and its says "if the max degree of parallelism option is set to 1, SQL Server ignores cost threshold for parallelism."

Thanks for your help.