Monday, March 12, 2012

Query parallelism/ACCESS and DUAL PROCESSORS?

We have a large operation - SQL server with lots of users attaching via VB p
rograms. Most operations to the database occur within stored procedures.
The SQL server is also used by an ACCESS front end for another application.
The ACCESS queries started getting this error:
Server: Msg 8650, Level 13, State 127, Line 1
Intra-query parallelism caused your server command (process ID #79) to deadl
ock. Rerun the query without intra-query parallelism by using the query hint
option (maxdop 1).
The ACCESS developer found some KB articles indicating that turning off the
DUAL PROCESSOR would fix the problem.
We have concerns about how the rest of the server usage will react to turnin
g off the DUAL PROCESSOR option.
Anyone have experience with this type of problem?Usually the work-around states that you can disable the use of multiple
processors at the server level OR you can disable parallelism for the
specific query causing the problem. Assuming that you can identify the
specific queries and can modify them, then it is far better to change the
queries. Otherwise you lose the benefit of multiple processors which can be
very significant!
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:08A84C96-7E31-4937-A419-4EBB06E10082@.microsoft.com...
> We have a large operation - SQL server with lots of users attaching via VB
programs. Most operations to the database occur within stored procedures.
> The SQL server is also used by an ACCESS front end for another
application.
> The ACCESS queries started getting this error:
> Server: Msg 8650, Level 13, State 127, Line 1
> Intra-query parallelism caused your server command (process ID #79) to
deadlock. Rerun the query without intra-query parallelism by using the query
hint option (maxdop 1).
>
> The ACCESS developer found some KB articles indicating that turning off
the DUAL PROCESSOR would fix the problem.
> We have concerns about how the rest of the server usage will react to
turning off the DUAL PROCESSOR option.
> Anyone have experience with this type of problem?|||get rid of the access clients.
Greg Jackson
PDX, Oregon

No comments:

Post a Comment