SQL Server Performance

Stored proc slow from app, fast from Query Analyse

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by mikehey, Apr 9, 2007.

  1. mikehey New Member

    Hi troops.

    I recently tweaked a stored proc as it was running slow and doing far too many reads.

    Now, when I run the stored proc from the app (asp.net using standard SqlConnection, SQL authentication) it still runs slow.

    Run it from QA using same login, and it flies a factor of 100 faster. Instead of 400,000 reads it does 4,000 or sometimes a few hundred. Duration typically 300ms instead of 15 seconds.

    All 100% consistent ( I can run some from QA, some from the app, and always the same result - not likely to be a caching issue).

    Why?

    I am using the exact same SQL (as obtained from Profiler) that the app uses.

    I checked this FAQ:
    http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=32
    and the ARITHABORT makes no diff.

    Thanks for any tips!

    Mike.


  2. Olu New Member

    go into syscacheobjects and determine if the setops values, if these values are all the same for the proc and the TSQL then check if this is a case of parameter sniffing (as to why the proc is running slow) ie, possible cache of an atypical input variable has lead to bad plan in cache...

    use master
    select * from syscacheobjects(nolock)
    where dbid = db_id(N'your db')
    and sql like '%your sql%'
    order by setopts


    Olu Adedeji
  3. MohammedU New Member

    Also runt the profiler to capture the execution plan from QA and App and see the difference...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  4. satya Moderator

    Just a thought, have you recompiled after changing the SP schema?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. mikehey New Member

    Thanks for the tips.<br /><br />As it turns out my comment about the 100% repeatable was not entirely accurate (won't bore you with the slightly embarrassing reason <img src='/community/emoticons/emotion-1.gif' alt=':)' /> ).<br /><br />So, in the end it was a plain old poorly performing stored proc. Reason was data related, and our dev db was out of date.<br /><br />I tuned the sp a little further and it now performs much better from both QA and app... I also have an up to date dev db.<br /><br />Mike.
  6. MohammedU New Member

    It happens [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]...<br />Thanks for the feed back....<br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com<br /><br />All postings are provided “AS IS” with no warranties for accuracy.<br />
  7. satya Moderator

    Data related, well it will be a cause to take.
    I guess it takes on the cached plan and having such optimization tasks in hand after such tweaks will help a lot.q

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page