February 23, 2011 at 11:00 am
Stored procs are performing poorly from our business application. I've been running a trace and noticing the results. In particular, Duration seems very high. Reads and Writes appear normal.
When I copy/paste the TextData from the trace into an SSMS window and execute, it runs quite fast. In SSMS, I SET STATISTICS IO ON. Total reads is about the same as what the trace shows when the sp is called from the business app.
Although I'm not ruling anything out, disk I/O doesn't appear to be an issue. The buffer cache hit ratio is 99.8%. IO stats indicate very few (if any) physical reads.
I started looking at sys.dm_os_wait_stats to see if there was anything glaringly wrong I began by resetting the contents of the view: DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)So far, I have not come to any conclusions. I don't have a lot of experience analyzing/interpreting the data in this view, though.
Any suggestions?
February 23, 2011 at 11:08 am
February 23, 2011 at 12:06 pm
If you are seeing a consistent disparity between SSMS and the app, check the connections settings to validate that the ANSI settings are the same between both. These can affect the execution plan created by the query optimizer and therefore the exeuction speed.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply