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?