Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Location
    Canada
    Posts
    2

    Angry Unanswered: Confused with 1 query performance in code

    SQL 2005 SP3 ( i know, upgrade... )

    This is driving me nuts, I think it is an issue with the developer code, but can't figure out how to prove it to them.

    We have a simple query that is executed via a prepared statement with no parameters. The result set is 56,000 rows.

    When the SQL is executed via SSMS it takes about 3 seconds to return. When the SQL is executed via java, it always takes 180 seconds to return, when I look at the activity, its always in SUSPENDED STATUS

    I've setup a few profiler sessions to capture whats happening and the same execution plan is fired, the same values are returned in read/writes/cpu.

    Would them reading the result set in funny cause sql server to report a slow duration?

    Anyone got any tips on what I should look at or suggest the developers to look at?


    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by scottyb1239 View Post

    Would them reading the result set in funny cause sql server to report a slow duration?

    Anyone got any tips on what I should look at or suggest the developers to look at?
    Yes the rate at which a data sets gets consumed can effect performance. I do not know java well enough to help you out with suggestions.

    I suppose you can have a look at the execution related dynamic management views and demonstrate that both forms of execution produce the same reads, writes and cpu time, and the only measurable difference is in the way the data is being consumed. if this is a stored procedure that does not get executed 50000 times a second on your database server, try adding WITH RECOMPILE in case your getting stuck with a bad plan issue. Otherwise post your code DDL and indices.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I would have expected a different plan to be generated by the JAVA call, and the SSMS call. If the JAVA program was slow in consuming the result set, you would see a wait_type of ASYNCHRONOUS_NETWORK_IO, I believe. Have a look at the results of this query, which is what Thrasymachus is getting at. This will look in the procedure cache, so you will need recent runs of both calls to be able to compare them:
    Code:
    select top 100  s.creation_time, s.last_execution_time, s.execution_count, 
    	s.total_worker_time, s.total_physical_reads, s.total_logical_reads, s.total_clr_time, s.last_logical_reads, s.last_worker_time,
    --	s.last_rows, -- SQL 2012 +
    	t.text, p.query_plan
    from sys.dm_exec_query_stats s cross apply
    	sys.dm_exec_sql_text (s.sql_handle) t cross apply
    	sys.dm_exec_query_plan (s.plan_handle) p
    order by s.total_logical_reads desc
    Compare the reads (average reads, really), between the two, and see if the query plans are significantly different.

  4. #4
    Join Date
    May 2006
    Location
    Canada
    Posts
    2
    Quote Originally Posted by MCrowley View Post
    I would have expected a different plan to be generated by the JAVA call, and the SSMS call. If the JAVA program was slow in consuming the result set, you would see a wait_type of ASYNCHRONOUS_NETWORK_IO, I believe. Have a look at the results of this query, which is what Thrasymachus is getting at. This will look in the procedure cache, so you will need recent runs of both calls to be able to compare them:
    Code:
    select top 100  s.creation_time, s.last_execution_time, s.execution_count, 
    	s.total_worker_time, s.total_physical_reads, s.total_logical_reads, s.total_clr_time, s.last_logical_reads, s.last_worker_time,
    --	s.last_rows, -- SQL 2012 +
    	t.text, p.query_plan
    from sys.dm_exec_query_stats s cross apply
    	sys.dm_exec_sql_text (s.sql_handle) t cross apply
    	sys.dm_exec_query_plan (s.plan_handle) p
    order by s.total_logical_reads desc
    Compare the reads (average reads, really), between the two, and see if the query plans are significantly different.


    I think you pointed me in the right direction, I do see all the locks are made of ASYNC_NETWORK_IO and the CXPACKET.

    {code}

    waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
    0x00000000003EF828 58 5 15281 CXPACKET 0x000000008012AEF0 0x0000000000FCB828 58 0 exchangeEvent id=port80129be0 nodeId=0
    0x0000000000FB5048 58 3 7390 CXPACKET 0x000000008012BD90 0x0000000000FCB828 58 0 exchangeEvent id=port80129be0 nodeId=0
    0x0000000000FCB828 58 0 9718 ASYNC_NETWORK_IO NULL NULL NULL NULL NULL
    0x0000000000FCA5C8 58 4 7390 CXPACKET 0x000000008012AFC0 0x0000000000FCB828 58 0 exchangeEvent id=port80129be0 nodeId=0
    0x0000000000FF12E8 58 2 1718 CXPACKET 0x000000008012B4A0 0x0000000000FCB828 58 0 exchangeEvent id=port80129be0 nodeId=0
    {code}

    Thanks for the help

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    CXPACKET is usually a parallel operation. The system is waiting for all of the threads dedicated to one step to complete before processing the next step.

Posting Permissions

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