Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Posts
    6

    Unanswered: Response times increase as read-only load increases

    We have several customers who are migrating from Oracle 10g to SQL Server 2005, and all of them are having performance problems, especially when the system has increased load.

    To work out what's going on we've written a harness program in Java that runs a query that causes a full table scan on one particular table (it uses a "column LIKE '%value%' " condition). With the harness program we can ramp up the number of concurrent threads running the query to test the load.

    When we run this test on Oracle 10g the response time remains the same whether we've got 1 request, or 20 concurrent requests (the response time starts to rise as we get up to 30 concurrent requests).

    On SQL Server, on the other hand, the response time increases linearly as we increase the number of concurrent threads, so by the time you've got 20 concurrent threads the response is significantly slower.

    Can anybody explain this behaviour?

    The usual explanation for concurrency problems in SQL Server is locking isolation levels. But in this test we're only doing reads, so why would locking cause a problem? (And anyway we're tried using SNAPSHOT isolation and READ UNCOMMITTED isolation and these don't seem to have any effect).

    Any theories welcome. Solutions would be even more welcome .

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    have you done a trace?

    my guess however will be cursors
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by Brett Kaiser
    have you done a trace?

    my guess however will be cursors
    What do you mean? How will cursors affect concurrency?

    I've used SQL Profiler to do a trace. All the SQL queries start being processed at the same time in SQL Server, but they just take longer and longer to be processed the more there are.

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    My Good GOD .. taking oracle vs sql servr .

    my good fellow first " (it uses a "column LIKE '%value%' " condition) "
    in oracle , the query is run and stored in cache .. and the %value% is some waht turned into a bind variable ( ':x' ) ... where as there are no concept of bind variables in sqlserver.
    force your query to search for numerics instead of strings.


    (the response time starts to rise as we get up to 30 concurrent requests).

    cache again


    now be more good and become a great fellow by proving me your query .

    so we can have a surgery upon it .

  5. #5
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by mishaalsy
    My Good GOD .. taking oracle vs sql servr .

    my good fellow first " (it uses a "column LIKE '%value%' " condition) "
    in oracle , the query is run and stored in cache .. and the %value% is some waht turned into a bind variable ( ':x' ) ... where as there are no concept of bind variables in sqlserver.
    force your query to search for numerics instead of strings.


    (the response time starts to rise as we get up to 30 concurrent requests).

    cache again


    now be more good and become a great fellow by proving me your query .

    so we can have a surgery upon it .
    The query isn't the issue here. I've deliberately written a query that takes a long time to execute. In fact, the query actually executes quicker on SQL Server than on Oracle when there's only a single thread. What I want to understand is why increasing the number of concurrent queries causes the response time to degrade on SQL Server but doesn't on Oracle.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Echo...Echo...Echo...

    Quote Originally Posted by Brett Kaiser
    have you done a trace?

    What's a "Harness" Program

    Are theseapplications we are talking about?

    Do they used stored procedures?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by ncurrie
    We have several customers who are migrating from Oracle 10g to SQL Server 2005
    Would you like some contract DBA's???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by Brett Kaiser
    Echo...Echo...Echo...




    What's a "Harness" Program

    Are theseapplications we are talking about?

    Do they used stored procedures?
    A test harness. A small program that does nothing but run the test. It's a Java program.

  9. #9
    Join Date
    Nov 2005
    Posts
    6
    Quote Originally Posted by Brett Kaiser
    Would you like some contract DBA's???
    Don't talk to me about DBAs . We have a whole shedful of them. They just avoid the question and talk about something else.

    I was hoping someone here might understand why SQL Server behaves like this for the kind of query I'm running. You said something about cursors in your original reply. Why do you think cursors might be the problem?

  10. #10
    Join Date
    Aug 2009
    Posts
    262
    without the query i cant say anything .

    it will be like talking about the surface of Pluto which i havent seen


    provided I manage/tune/backup/everything 11g real application clusters . and sqlserver2008 ( mirrored server with webservices) . a some what dba kind of a thing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •