none
Perfomance Issue with NVarchar(MAX) in SSIS

    Question

  • Hi--

    I have a package where couple of source columns are of NVARCHAR(MAX). The Data Flow Task with OLEDB Destination is taking ~ 50 min to load 23Million records with No transformation placed. Could you please suggest what alternate I can do to boost my package performance?

    Source Details:

    Total No of Columns - 12
    Total Count - 23021295

    SELECT * FROM Source_Table is giving me the result in 7 min. I believe it is because of the Nvarchar(Max) columns which is creating the bottleneck!!


    Unknown

    Friday, February 21, 2014 12:00 PM

Answers

All replies

  • Could Someone please comment something on it??? 

    Unknown

    Monday, February 24, 2014 9:15 AM
  • Hello,

    Is the destination also SQL server table with nvarchar(max) ? Can you run a sql trace on both the source and on the destination to see how much time each part takes.

    another thing to check : For destination are you using an oledb connection with 'table or view' or 'table or view - fast load'?


    Jan D'Hondt - SQL server BI development


    • Edited by jandho Monday, February 24, 2014 2:12 PM
    Monday, February 24, 2014 9:50 AM
  • Did you check the DefaultBufferMaxSize and also the properties BLOBTempStoragePath and BufferTempStoragePath setting them to an optimal value might increase the performance but it is a double edged sword so configure them accordingly

    Refer  http://technet.microsoft.com/en-us/library/ms141031(v=sql.90).aspx


    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Tuesday, February 25, 2014 2:37 PM
  • Always using Nolock is a good idea. like select * from table(nolock)

    Trying using Nolock.. I meant uncheck the lock option, show as below. It is by default checked, just uncheck it. 

    Thanks.


    • Edited by Murali dhar Thursday, February 27, 2014 4:41 PM
    Tuesday, February 25, 2014 9:31 PM
  • Hi Roger,

    You are getting loading speed of 460000/min. This doesn't look like bad performance. Also you speculate the NVARCHAR(MAX) columns slow you down. Have you tried to do a load without them and see what performance you get?


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Thursday, February 27, 2014 3:44 PM
  • NVARCHAR(MAX) will cost a lot of memory and will fill up your buffers quickly. Check out the BLOBTempStoragePath and BufferTempStoragePath properties that Surender Singh Bhadauria mentioned.

    SELECT * FROM Source_Table is giving me the result in 7 min. I believe it is because of the Nvarchar(Max) columns which is creating the bottleneck!!


    Is SSIS on the same server as the source database? If not, the network could also be an issue because all data has to to from your SQL server via the network to your SSIS server. You could try changing the Network Packet Size.

    Other options to rule out other problems is to run and time without that nvarchar(max) column like COZYROC mentioned or to remove the destination and add a rowcount transformation... and then run and time the package...


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, February 27, 2014 4:32 PM
    Moderator