English 中文(简体)
SQL server timeout 2000 from C# .NET
原标题:

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 ...

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.

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.

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.

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?

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.

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.

问题回答

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).

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.

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





相关问题
Anyone feel like passing it forward?

I m the only developer in my company, and am getting along well as an autodidact, but I know I m missing out on the education one gets from working with and having code reviewed by more senior devs. ...

NSArray s, Primitive types and Boxing Oh My!

I m pretty new to the Objective-C world and I have a long history with .net/C# so naturally I m inclined to use my C# wits. Now here s the question: I feel really inclined to create some type of ...

C# Marshal / Pinvoke CBitmap?

I cannot figure out how to marshal a C++ CBitmap to a C# Bitmap or Image class. My import looks like this: [DllImport(@"test.dll", CharSet = CharSet.Unicode)] public static extern IntPtr ...

How to Use Ghostscript DLL to convert PDF to PDF/A

How to user GhostScript DLL to convert PDF to PDF/A. I know I kind of have to call the exported function of gsdll32.dll whose name is gsapi_init_with_args, but how do i pass the right arguments? BTW, ...

Linqy no matchy

Maybe it s something I m doing wrong. I m just learning Linq because I m bored. And so far so good. I made a little program and it basically just outputs all matches (foreach) into a label control. ...

热门标签