segunda-feira, 6 de agosto de 2012 13:13
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
Todas as Respostas
segunda-feira, 6 de agosto de 2012 13:24Usuário que respondeTry creating SSIS package and using OLEDB provider which has FastLoad feature...
Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/
segunda-feira, 6 de agosto de 2012 13:47You need to check the amount of data/rows that gets transferred so that the timing differences can be answered.
terça-feira, 7 de agosto de 2012 08:04
the amount of data almost the same each month ..
- Editado SQL Kitchen terça-feira, 7 de agosto de 2012 08:06
terça-feira, 7 de agosto de 2012 08:05Usuário que responde
terça-feira, 7 de agosto de 2012 08:06
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
terça-feira, 7 de agosto de 2012 08:18
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.
terça-feira, 7 de agosto de 2012 11:04
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.
terça-feira, 7 de agosto de 2012 11:04may be .. but how i can be 100% suer
quinta-feira, 9 de agosto de 2012 09:21
While the transfer is in progress you can check the destination and source database for any blocking for your SQL Queries.
Query to check blockingSELECT
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
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
s.spid > 50 and s.blocked ! =0
quarta-feira, 15 de agosto de 2012 12:05
Thanks for your valubale replies
I have checked the type of locking which is LCK_M_S..
what do you think?