SQL Server Performance Forum – Threads Archive
Load Errors
Hi, Looking for help regarding errors that only occur underload. (Which makes them a pain to debug) I currently have a my app set-up over a 3tier environment, all running win2000 sp3, & sql2000 sp2, mdac2.7 sp1, IIS5.0 respectively. (using MTS) I have gone though all database scripts optimising as much as I can…(ie, nolocks,rowlocks,etc…) Using MS stress tester I can successfully run the application with 20 consistent simultaneous users (error free) but when I up this to 25 i get the following errors. (and lots of them) -=> ADO error: -2147217871, Timeout expired-=> ADO error: -2147168246, New transaction cannot enlist in the specified transaction coordinator.
-=> ADO error: -2147217900, Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
-=> ADO error: -2147217900, Could not find stored procedure ‘proc_read_pref’. (heavy error handling – so assume no other errors thrown) Yet these only occur underload, yet the database server never gets anywhere near maxed out… avg cpu loads: web svr : 30-50
db svr : 30-40
app svr : 80-100 any help on any of these would be appreciated as my goal is 50 simultaneous users.
Are your components running in the context of transactions in MTS? (not sql server transactions but MTS transactions). From all of your error messages it’s obvious that there is a timeout but with only 25 users I wouldn’t spend too much time looking on the database, it’s probably the application and MTS is notorius for these kinds of things. I’ve seen similar error messages appear when COM components running under MTS are enlisted in transactions and make calls to the database for the same data.
How are your setting up your ADO recordsets? Are you using forward only, static or keyset cursors for your recordsets? In some cases this can occur using forward only cursors (firehose), especially if the connection to the database isn’t closed soon after you use it and other components enlisted in transactions trying to get the same data can fail in a transaction. Let us know about how you’re setting up your ADO recordsets. Cheers
Shaun World Domination Through Superior Software
I remember reading that, this error occurs due to a timeout. If you are using stored procedures, make sure your transaction does not time out (Use ADO command timeout property, if you can from your DLL) or re-write code to divide transaction into managable transactions in bits. If infinite timeout does not help you I can remember some other possible reasons for this.
a)Does system work on one application or many applications involved?
b)Do you have any triggers making ROLLBACK TRANSACTION or COMMIT TRANSACTION?
c) Don’t use Named Pipes anywhere at all as communication protocol for MSDTC or SQL Server. Use TCP/IP stack only. Make sure all machines defaulted to use TCP/IP. HTH
_________
Satya SKJ
Shaun, I agree, definitly a timeout issue, even more so since normally when i run the test with 25+ users about 1/2 way through all cpu lvls drop to near zero, and it’s only when they kick back in that all the errors are written to the log. All transactions are done at MTS level. I am using static cursors and closing all connections. Satya, I have played with the tineout property but it just delays the timeout if i make it longer and if i make it infinite then the app never completes the process. a) don’t quite understand your question
b) i have many triggers but no transactions at db level.
c) i had actually already read this somewhere on the net and made sure this was the case.
With MTS (or COM+ as it is called in Windows 2000) alla your queries and are issued in serializable isolation mode. This is not good in a multi user environment. It can lead to frequent blocking if users are working with the same data or tables, and thus lead to timeouts. So make sure that serializable isolation level is what you really need. If you have any stored procedures that do read only queries, say generating reports, you shouldn’t put them in COM+ since it will be very slow when you have many users. If you can’t put the read only queries outside your COM+ object for some reason, say both read and wrtie queries are embedded in the same DLL, then issue SET TRANSACTION ISOLATION LEVEL READ COMMITTED at the top of all your read only stored procedures. Or maybe even read uncommitted. The "cannot enlist" and "null transaction" errors can be caused by the timeout that occur. They can also be caused by bad network performance between the involved servers or that name lookup doesn’t work or is slow between the servers. MSDTC traffic needs to be able to do name lookup between all involved servers to work. Also any slow firewalls between the servers can disturb this. And of course all involved servers need to have the MSDTC service running. So I would recommend focusing on trying to eliminate serializable isloation mode where possible if you want to target more simultaneous users. /Argyle
Only necessary data calls are made using MTS transactions, all other database calls are made outside of these. Currently no firewall issues as I am testing internally. I have analysed & refined both DB & APP as much I can, and have come to the conclusion that it’s either a major rewrite or throw more hardware at it, the latter which I have done successfully. thanks for all your help anyway guys.
]]>