I need to pull data from many different systems, all on remote, LIVE SQL Servers into one place for a one-off piece of work that will last a few weeks. This has to be run multiple times during the day and MUST come from LIVE systems.

I am just running multiple:

SELECT * INTO #temp_table FROM [linked_server].CRM.dbo.[table];

queries using a linked server to get all the data I need. Then I process it.

The one system that is causing me issues is Microsoft Dynamics CRM, as there seem to be a lot of background processes running constantly. I have found that I mostly get a deadlock after 1 minute of execution, but other times my query completes with no deadlocks in around 1.5 minutes. So I have to run my query, wait a minute, run it again, wait a minute, etc. until it finally works.

At some point I can see this causing problems for users, trying to access a live system at the same time I am attempting to bulk-extract data multiple times.

I had a think about why I would be getting deadlocks on SELECT queries in the first place and my (non-DBA) thought was that this was probably due to indexes being changed under my feet as I pull data out. So I tried removing any WHERE clauses on the tables I needed data from.

The net effect of this is that my script runs much more consistently without deadlocks, but it also takes a lot longer to execute (as it is pulling over a lot of data I don't want).

I had the following ideas to improve upon this, but I don't want to start developing a whole load of work if these aren't going to help at all:

  • write a stored procedure on the CRM server to extract the data I want. Then call the stored procedure to get at the data (would this resolve the deadlock issue though?);
  • stage the data locally into a permanent table, and poll it when it has updated;
  • use the CRM server as the "master" server, and pull all the other data into this (but there are space issues here);
  • use a different mechanism to get at the data, e.g. OPENQUERY, OPENROWSET, etc.;
  • add a temporary covering index to the tables I need to extract data from (as I have heard this might help);
  • I could also write this as a .NET application, where I would basically connect directly to each server in turn, pull out the data I want and store it in memory until I have processed it.

Any experience on resolving these sort of issues?

  • Instead of Linked Servers and .NET customization, use SSIS. You could also try running queries with hint NO LOCK or ISOLATION LEVEL READ UNCOMMITTED to prevent shared locks. – Dave Oct 5 '15 at 16:26
  • The WHERE clause can also cause a key lookup operation (which is basically guaranteed if you use SELECT * and the WHERE is not covered by the CI), and key lookups can lead to deadlocks since it's technically two operations. – JNK Oct 5 '15 at 16:33
  • SSIS would be a good call, but the company I am working for don't use it and haven't got it installed anywhere. I don't want to READ UNCOMMITTED as this data must be 100% accurate and we will be audited on that in a year's time. – Richard Hansell Oct 5 '15 at 16:35
  • 100% accurate as of when? If you lock the entire table and it takes a minute to read all of the data, anyone who was waiting to write to that table (and hence change the data) is just queued up behind you. As soon as your SELECT finishes, they can process their writes. How accurate is your data now? – Aaron Bertrand Oct 5 '15 at 16:46
  • If you are constantly pulling data from some remote server, why not do something designed for this, like replication, mirroring, log shipping, etc? Moving a copy of the data locally - even if not in its final form - will allow you to do any work with it without all of the issues involved with cross-server latency. – Aaron Bertrand Oct 5 '15 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.