SQL Server Performance

Tuning advice - long duration values from profiler

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Trond, Feb 3, 2009.

  1. Trond New Member

    <p>&nbsp;<img src="http://www.elinogtrond.com/wp-content/uploads/result.png" title="Profiler result" alt="Profiler result" mce_src="http://www.elinogtrond.com/wp-content/uploads/result.png" width="647" height="208"></p><p>This is a part of the result after running the SQL Server Profiler against a database for a couple of hours. Running on Microsoft SQL Server 2005 Workgroup Edition with around 20 pc connected using an MS-Access application (using ODBC). </p><p>I’m wondering why the duration got so large values, when the CPU and the reads values are much lower. Is it possible that this is caused by network-problems?</p><p>&nbsp; <br></p>
  2. Elisabeth Redei New Member

    Hej Trond!
    You can start your investigation by running the statements in SQL Server Management Studio. If you consistently get shorter durations there, and consistently long durations when the same statements are run from your Access application (even with just one user) it could be some severe network problem but also check if you see any sp_cursoropen and sp_cursorfetch operations in Profiler (in case Access opens a client side cursor, I don't know the default behaviour off the top of my head). If you get cursors it might be that the rowfetchsize is small (like 1 row at a time) and then you will have an extra network roundtrip for each row. What versions of Access and couldn't you use a newer OLEDB driver?
    If your get high durations intermittently in a multi-user scenario I would suspect blocking or some hardware bottleneck on the SQL Server. You can troubleshoot blocking with sp_configure and the "blocked process threshold" option (documented in BoL) and then run another trace and include the Blocked process event.
  3. Trond New Member

    Thanks for your help Elisabeth.
    Running the statements in SSMS gives much shorter duration. We have earlier changed the CursorTypeEnum in Access (2003) to avoid Access from using cursors. I look like cursors are used in less than 1% of the queries, and not connected to the troubled ones.
    The tips about blocking really helped me. After turning the on the “blocked process threshold”, the profiler started to report entries with the blocked-process-report. Looks like a complicated select and an insert statement get into a deadlock. This select statement is also on the top of the duration list. We will try to fix this problem, and see if this helps on the overall system performance.
  4. Elisabeth Redei New Member

    Sounds good, Trond! What you might also want to do is to make sure that your statistics are up to date (so that the optimizer will take advantage of existing indexes) and then take one of your workload files (traces) and run through the Data Tuning Advisor (not on the production server).
    Good luck!

Share This Page