SQL Server Performance Forum – Threads Archive

Stored proc slow from app, fast from Query Analyse

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.

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

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.

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.

Thanks for the tips. 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=’

‘ /> ). 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. 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. Mike.

It happens [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]… Thanks for the feed back…. MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

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.

]]>