none
maxdop linked server question

    Question

  • Does anyone know how MAXDOP works when you have a linked server query?

    I have a linked server query that is reading a table on a remote server and inserting it into a table on the local server. Does MAXDOP apply to both servers or just the local or remote? Or is there a way of re-writing the query to control how it works?

    Example query

    Select * into dbo.temptbl from rmtsvr.srcdb.dbo.mytable OPTION (MAXDOP 1)

    Thanks


    Brian Searle

    Tuesday, September 24, 2013 8:16 AM

Answers

  • Hi

    1. faster to check then to ask

    just open profiler on the remote sqrver and check the query.

    2. if you find that it is only control the local server then you can move to control resources in remote server using "Resource Governor" :-)


    signature

    Wednesday, September 25, 2013 7:06 AM
    Moderator
  • Alternatively, if you find that MAXDOP only applies to the local server, you could consider writing a stored procedure on the remote server and apply your desired MAXDOP value in there, and call that SP locally to populate you table.

    The disadvantage is, that you would probably have to change your SELECT .. INTO to a INSERT INTO .. SELECT, but you could limit the impact of that if you run SELECT TOP 0 .. INTO first.


    Gert-Jan

    Wednesday, September 25, 2013 9:39 AM

All replies