topic MAXDOP (parallelism on MS SQL) in Application Development and Automation Discussions

MAXDOP (parallelism on MS SQL)

Former Member — Fri, 25 Jun 2010 18:49:57 GMT

Hello guys,

Colleagues from SQL Server team suggested we look into parallel processing for heavy SQL Server queries, using more than one CPU/worker thread to retrieve large amounts of data quickly.

By default, and on SAPu2019s recommendation, parallelism is turned off on all systems, due to the overhead it causes on queries that would not benefit from it anyway.

The parameter that controls this behavior in the SQL Server engine is called u2018maximum degree of parallelismu2019, or MAXDOP.

Since SAP supports u201C%_HINTSu201D in their Open SQL implementation in ABAP, we can activate parallelism per query as needed. So I tried this hint on approximately 10-15 SQL statements in the system with a lot of data, but no significant change.

Of course I know that DB-specific things like hints are not recommended by SAP, but stillu2026

Just wanted to know if anyone had good experience of applying this MAXDOP hint and if yes u2013 on what kind of SELECTs it was? (like SELECT JOIN, SELECT GROUP BY, SELECT SUM( DISTINCT col ) etc)

Thanks a lot,

Vlad

Re: MAXDOP (parallelism on MS SQL)

ravi_lanjewar — Sat, 26 Jun 2010 10:22:40 GMT

Hi,

Refer the following thred it will give idea how to used the parallal ABAP Sql execution.

Rgds

Ravi Lanjewar

Re: MAXDOP (parallelism on MS SQL)

HermannGahm — Sun, 27 Jun 2010 20:27:10 GMT

Hi Vlad,

i tried it on ORACLE and it worked quite well. Usually

for SELCTS that do multiblock I/O (or work with partitioned segments).

Once you "specified" parallel execution with a hint in MSSQL

(how) did you check that it is actually executed in parallel?

I don't know such details about MSSQL but maybe you have

activate parall processes on system level as well.

Kind regards,

Hermann