我遇到了一个奇怪的问题，使用SQL Server 2000和两个链接的服务器。两年来我们的解决方案已运行顺利，但昨天突然一个查询从数据库到其他的一个数据同步启动超时。

I have run into a strange problem using SQL Server 2000 and two linked server. For two years now our solution has run without a hitch, but suddenly yesterday a query synchronizing data from one of the databases to the other started timing out.

我连接到生产网络中的服务器，它链接到包含一台服务器的订单我需要的数据。

I connect to a server in the production network, which is linked to a server containing orders I need data from.

查询中包含了一些连接，但是基本上这个总结做了什么：

The query contains a few joins, but basically this summarizes what is done:

INSERT INTO ProductionDataCache
   (column1, column2, ...)
   SELECT tab1.column1, tab1.column2, tab2.column1, tab3.column1 ...
       FROM linkedserver.database.dbo.Table1 AS tab1
       JOIN linkedserver.database.dbo.Table2 AS tab2 ON (...)
       JOIN linkedserver.database.dbo.Tabl32 AS tab3 ON (...)
       ...
       WHERE tab1.productionOrderId = @id
       ORDER BY ...

显然，我的来解决这个问题的首次尝试是从原来的5分钟延长超时限制。但是当我到达30分钟，仍然有一个超时，我开始怀疑别的东西是怎么回事。查询只是不从不到5分钟，以执行在30分钟内过夜去了。

Obviously my first attempt to fix the problem was to increase the timeout limit from the original 5 minutes. But when I arrived at 30 minutes and still got a timeout, I started to suspect something else was going on. A query just does not go from executing in less than 5 minutes to over 30 minutes over night.

我输出的SQL查询（这原本是在C＃code）在我的日志，并决定直接在数据库服务器上执行的查询分析器中查询。为了我的大惊喜，查询在不到10秒的正确执行。

I outputted the SQL query (which was originally in the C# code) to my logs, and decided to execute the query in the Query Analyzer directly on the database server. To my big surprise, the query executed correctly in less than 10 seconds.

所以我隔绝了SQL执行一个简单的测试程序，并观察到同样的查询超时无论是在服务器最初运行此解决方案和数据库服务器上运行时，它在本地。此外，我试图创建一个存储过程，并从程序中执行这一点，但是这也超时。在查询分析器中运行正常工作，在不到几秒钟。

So I isolated the SQL execution in a simple test program, and observed the same query time out both on the server originally running this solution AND when running it locally on the database server. Also I have tried to create a Stored Procedure and execute this from the program, but this also times out. Running it in Query Analyzer works fine in less than a few seconds.

看来，问题只当我执行从C＃程序此查询时。有没有人见过这样的行为之前，并找到了解决办法呢？

It seems that the problem only occurs when I execute this query from the C# program. Has anyone seen such behavior before, and found a solution for it?

更新： 现在我已经使用SQL事件探查器在服务器上。明显的区别在于，在执行从.NET程序的查询时，它显示在日志如EXEC sp_executesql的N'INSERT INTO ..​​.'，而是从查询分析器执行时发生作为日志在一个正常的查询

UPDATE: I have now used SQL Profiler on the server. The obvious difference is that when executing the query from the .NET program, it shows up in the log as "exec sp_executesql N'INSERT INTO ...'", but when executing from Query Analyzer it occurs as a normal query in the log.

此外我尝试使用相同的SQL用户的程序来连接SQL查询分析器，这引发在查询分析器中的问题，以及。因此，通过TCP / IP连接使用SQL的用户时，似乎只出现问题。

Further I tried to connect the SQL Query Analyzer using the same SQL user as the program, and this triggered the problem in Query Analyzer as well. So it seems the problem only occurs when connecting via TCP/IP using a sql user.

推荐答案

有几件事情进行调查;首先是设置选项;这些可以使一个巨大差一些查询。看设置在跟踪选项，并重复这些，当你正在测试在Management Studio（或查询分析器）。请注意，你必须看一下实际的配置文件会真正看到这些（而不仅是您 code日志）。

There are a few things to investigate; the first is SET options; these can make a huge difference to some queries. Look at the SET options in the trace, and repeat these when you are testing in Management Studio (or Query Analyzer). Note that you'd have to look at an actual profile session to really see these (not just what your code logs).

接下来的事情我想看看是交易;什么样的交易模式是您使用在C＃？任何？没有？什么隔离？序列化？也许分配？这可能是有一些模糊的锁被关押在桌子上。

The next thing I'd look at is transactions; what transaction model are you using in your C#? Any? None? What isolation? Serializable? Perhaps distributed? It could be there is some obscure lock being held on the table.

当然，如果锁定是您可能会看到通过的sp_who / sp_who2 一些问题的查询运行时

Of course, if locking is an issue you might see something via sp_who / sp_who2 while the query is running.