SQL Server Performance

COM+ and SLOW SQL TIMES

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by Dick Donny, Oct 22, 2004.

  1. Dick Donny New Member

    Hi,<br /><br />It's a general question to see if anyone has come across this problem (and hopefully knows how to solve it).<br /><br />We interact with the DB via components registered within COM+ using ADO (MDAC2.<img src='/community/emoticons/emotion-11.gif' alt='8)' />. The DB and APP servers are different machines.<br /><br />We have noticed a slow response from sending a query to the DB through ADO (using Microsoft SQL Server OLEDB provider - SQLOLEDB) in comparison to the times reported by the DB in a trace file (network latency is negligible). Some of these times vary by as much as a second or more.<br /><br />I ran a simple )albeit not very scientific test) via a noddy application. Outside of COM+ the times reported by the DB were around 16ms and 50-60ms to get back to the application. When the application was placed in the COM+ environment, the ADO response time increased to around half a second.<br /><br />Although we use COM+ as a host for the components, we actually don't use most of it's services (used really for the connection pooling and security). We use IMPERSONATION for the security level.<br /><br />I haven't managed to find much on the internet on this subject <a target="_blank" href=http://www.winnetmag.com/SQLServer/Article/ArticleID/8839/8839.html>http://www.winnetmag.com/SQLServer/Article/ArticleID/8839/8839.html</a>) so presume it is some crazy configuration that we have.<br /><br />Does anybody have any knowledge on this or maybe any suggestions as to what may be the cause.<br /><br />Thank you in anticipation of any help you can offer.<br /><br />Richard
  2. Argyle New Member

    This is because with COM+ your transaction isolation level is changed from Read Comitted to Serializable. This has a huge impact on performance. Generally you do not put code in COM+ that don't need transactions.

    With Windows 2000 there is no way to change this in COM+. You have to add SET TRANSACTION ISOLATION LEVEL READ COMMITTED in the beginning of your stored procedures. With Windows 2003 you can now change the isolation level that COM+ use.

    More info:
    http://vyaskn.tripod.com/com_isolation_level.htm

    http://msdn.microsoft.com/library/d...s/cossdk/htm/pgservices_transactions_654j.asp
  3. Dick Donny New Member

    Thank you very much ... I really appreciate your help
  4. Dick Donny New Member

    I've just had a read of the links and it suggests that the serializable isolation level is only enforced if you utilise the MSDTC transaction processing inherent in COM+. At other times the ReadCommitted level should be used.

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;215520

    Our applications handle their own transaction processing.

    I've put a trace on the locks and have yet to see a range lock being generated, therefore I presume the isolation level to be ReadCommitted (is it possible to trace the isolation levels ?).

    I'll keep an eye on it because your suggestion is a very good one, but I wondered if anyone had any other ideas worth investigation too ?

    Thanks for all your help

    Richard
  5. Argyle New Member

    COM+ itself will enlist any command in MSDTC, it's not something you specify. So the isolation level on your queries will be serializable.
  6. Dick Donny New Member

    Once again thank you for your help.<br /><br />I've checked the documentation and written a test noddy and am now certain that the transactions are not enlisted under MSDTC and that the isolation level has not been overridden.<br /><br />I've checked dbcc useroptions etc, put a trace on the locks and am 99% sure it is using readcommitted (ado certainly reports so too).<br /><br />Back to square one <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />Thanks for your suggestion though ... it was definitely worth the time looking into it.<br /><br />Richard
  7. Twan New Member

    Hi ya,

    so you have a very simple sql statement (do you have an example?)

    when fired from sql it is quick
    when fired from a 'fat client' it is quick
    when fired through com+ on the SQL box it is quick/slow?
    when fired through com+ on another box it is quick/slow?

    during the run you also have a sql trace file with duration/cpu right?


    is it possible for you to also calculate the duration (or start/end times) in the com+ component?

    then post the com+ code you wrote, the trace file result and the com+ trace result. This should give us more to go on from here

    Cheers
    Twan
  8. Dick Donny New Member

    Hi<br /><br />The issue arose from an investigation into slow response times reported by a client running a fat client on a 3 tier system with a dedicated application and database server (different machines on the same LAN). The database had been badly configured in terms of indexing etc, however after correcting this and achieving very quick response times through query analyzer, it was noted the application still ran slow. The application by nature issues adhoc queries and there is very little caching actually going on, however it is still very slow even considering this.<br /><br />After spending some time watching performance monitor and reading the results of a trace on locks, execution times etc it became apparent that the db/machine was performing well, so I wrote a simple noddy application that literally connects to the database, fires off 10 of the slower adhoc SELECT queries via the ado.connection.execute method (ensuring none of them returned data) with a client side cursor and everything else left at it's defaults (eg isolation level of readcommitted). I used the SQLOLEDB provider.<br /><br />When run outside COM+ I was seeing trace times on the db of around 16ms and ado reporting times of between 50 and 60ms. Placing the same binary into a COM+ environment on the Win200 db server and with only my application connected, times of around half a second were reported.<br /><br />I then ran the same thing using transactions and the times were roughly the same. The original application handles its own transaction processing, thus bypassing MSDTC.<br /><br />I then ran the same tests using bound sql, again with little effect on the actual results.<br /><br />Tracing the locks, there were no range locks taken (ensured statements used &gt; etc) confirming that MSDTC was not setting the isolation level.<br /><br />I ran the same test against an Oracle database and surprisingly found very little difference between the times recorded inside and outside of COM+. I was beginning to think this was not a SQLServer / SQLOLEDB issue (MDAC2.<img src='/community/emoticons/emotion-11.gif' alt='8)' /> but the Oracle thing has opened it back up again.<br /><br />Please don't waste a lot of time (albeit your help/suggestions are always appreciated) on this as I suspect this will turn out to be some crazy COM+ setup or machine specific issue ....... but it is interesting .. well, kind of <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  9. Dick Donny New Member

    In case anyone is interested ... we finally tracked it down.

    The time is taken up by the .net to com interop. Outside COM+ it is barely noticeable, but once in that environment, it slows to a snails pace.

    Thanks for all your help with this, it's appreciated.

    Richard
  10. marcosol New Member

    Hi,

    you can read the topics with this hyperlink

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13219

    i think that your problem is related to isolation level of com+ application. I had the same problem and i resolved without change the isolation level but increase the polling of application.

    can you read

    http://msdn.microsoft.com/library/d...html/bf98318b-4d87-44cc-85a1-68faf5547e06.asp


    and set the polling on 5 ( example ).
    Im sure that your application will be faster than now..

    write me toyr experience...

    Bye
    Marco Solari
  11. Adriaan New Member

    Hi Marco,<br /><br />You are aware that you're responding to a thread that ended in November 2004? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  12. marcosol New Member

    opssssss<br /><br />oh my god...<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />im sorry...<br />

Share This Page