Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Distributed SELECT... INTO... blocked by sp_ calls

Author  Topic  jpsidell Starting Member 1 Post Posted - 2006-03-17 : 18:49:56 I'll try to describe this without pictures... I have one MAJOR db server and 8 MINOR db servers. The servers are managed by our application, which is transforming data to load into a data mart. Almost all of the queries are SELECT... INTO... (since these are not logged). Some of the queries execute locally on MAJOR while some are intended to pull data from MAJOR to the MINOR servers to be processed in parallel. The data-pull queries are executed on the MINOR servers and look like this: SELECT * INTO <table> FROM MAJOR.DB.SCHEMA.<table> WHERE ... The problem is the following: When there are queries running on MAJOR, all of the data-pull queries are blocked waiting for a series of stored-procedure calls to complete on the MAJOR server. The purpose of these appears to be to get as much information about the source table as possible, in order to estimate the size of the destination. One of the stored procedures, which seems to be the one causing the blocking most frequently, is sp_table_statistics_rowset. The lock being held is a single key lock on the sysindexes table on the MAJOR server. I've opened a trouble ticket w/ Microsoft tech support, but they haven't been able to come up w/ a solution so far. Possible approaches that I've thought of include: 1. Defining the data source as something other than SQL Server, since those stored procedures would presumably not be available on a different DBMS. However, the MS folk tried defining the source as an MS OLE DB Provider, and the stored procedures were still called. 2. Redefining the stored procedures. 3. Setting a different transaction isolation level on the system catalogs, so that inserts, etc, into them won't block. I realize that both #2 and #3 are probably too dangerous to consider, but I'm really stuck here. Any help would be greatly appreciated.