February 26, 2011 at 5:53 am
I read a blog on improving SSIS speed when retrieving data from an SQL table through an OLE DB source at http://henkvandervalk.com/reading-as-fast-as-possible-from-a-table-with-ssis-part-ii/comment-page-1#comment-10064. The one thing it mentioned that has me confused is that it says that multiple spids are started when you use multiple OLE DB soures with a query using mod arithmetic. Then says that to remove synch overhead and to stop the spids from being suspended and to run at the same time add the query hint OPTION(MAXDOP 1) to the query. My understanding is that this restricts the system to using only 1 CPU. Wouldn't it be better to use all CPU's available? If someone could shed some light on this it would be greatly appreciated.
March 1, 2011 at 12:20 pm
The MAXDOP settings affect how SQL Server generates the execution plan for a query. And depending on your system set up and the query you are running changing the MAXDOP setting can dramatically change the execution plan that is generated. You would need to check the specific query you are running with varying MAXDOP setting compared to the default and see if they are performing better or worse.
http://blogs.msdn.com/b/sqltips/archive/2005/09/14/466387.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply