Transfer data

Question

Hello,

i have monthly task aim to Transfer data from a staging machine to production over the linked server and my problem is the Transfer process most of times takes around 2 hours while sometimes taking around 22 second .. actually i don't know what's the diff

but i captuerd shared locking while transfer process is running .. my transfer statemnt is very simple INSERT INTO operation ..

• Source SQL 2008 x32 SP3
• Destinatin SQL 2008 x64 SP2

Answer 1

Try creating SSIS package and using OLEDB provider which has FastLoad  feature...

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

You need to check the amount of data/rows that gets transferred so that the timing differences can be answered.

Answer 3

the amount of data almost the same each month ..

Answer 4

Networking issue ????

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 5

Thanks Uri for your reply

but my problem isn't pick a correct way to tranfer data , becouse my technique working fine "sometimes" my problem is how to discover the root cause make my transfer process sometimes take 50 Sec and some other time take 2 hours 

Answer 6

Hello SQL Kitchen,

I would recommend you to capture the PSSDIAG and then analyze using sqlnexus. Check this article : -  http://sql-blogs.com/2012/02/18/configure-pssdiag-to-capture-high-cpu-issues/

It will help you to narrow down the root cause of the issue. I had written this for high CPU scenarios, but can be used for this scenario as well. Please check the performance impact on the test environment and if it's major then you could disable the profiler traces or configure it with minimal configuration.

 Let me know If you need any help.

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 7

Link server usually work fast, as you are facing trouble transferring data through link server then trying SSIS can be good.

if you still face same issue then use your select statements with [nolock] this will prevent any blocking on source machine.

then comes the network if above does not work.

Answer 8

may be .. but how i can be 100% suer

Answer 9

While the transfer is in progress you can check the destination and source database for any blocking for your SQL Queries.

Query to check blocking

SELECT
             s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
             s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid), 
                   Definition = CAST(text AS VARCHAR(MAX)),s.login_time [LoginTime],
                   s.last_batch [LastBatch],lastwaittype,datediff(second,s.last_batch,getdate()) "Duration", [HostName] = s.hostname,[CPU Time] = s.cpu,[Physical IO] = s.physical_io
 --INTO        #Processes
 FROM      sys.sysprocesses s
 CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 WHERE
 s.spid > 50 and  s.blocked ! =0

Answer 10

Thanks for your valubale replies

I have checked the type of locking which is LCK_M_S..

what do you think?