none
Profiler shows duration INSERT times very long

    Question

  • Using SQL 2000, I run profiler for 15 min and record 15,000 INSERTS that have duration times of 0-156MS. Then suddenly, there are INSERTS with durations of 6000-8000MS.

    After approx 10 secs the durations drop again to 0-156MS, but soon are back to 6000-8000MS. And, the pattern continues on and on.

    These are client login INSERTS that must cause a delay on their end for those who are experiencing this.

    The INSERTS are from applications on remote servers and are not a stored proc on the SQL server.

    I've been using profiler and perfmon and trying to find a correlation.

    I don't know if this is a SQL issue or a Disk IO problem or what. I have gone through a lot of counters from articles on the web, but it is almost too overwhelming.

    Is there a recommended list of counters and processes that can be recommended to figure out what is causing this issue or does anyone, based on the pattern, have an idea about where to start?

    Thank you,
    Don

    Monday, December 01, 2008 11:43 PM

All replies

  • Monitor for page splits using the SQL Server Access Methods object: Page Splits/sec counter.  If this is high during the inserts with long duration then you may need to rebuild the indexes on this table with a fillfactor specification that increases the free space in the pages to reduce the splits.

     

    Also look at the output of DBCC SQLPERF(WAITSTATS) when you see this pattern of long duration occuring in the trace and see what waits are occuring in the database.  Look for blocking processes in the sysprocesses view as well, and see if you have a blocking spid problem.  If you do, use DBCC INPUTBUFFER(spid#) to get the executing statement for the blocking spid.  You have to run this for the spid at the head of the blocking chain for it to be accurate for solving the problem as blocking can backup multiple spids.

     

    This kind of problem is much easier to resolve in SQL Server 2005, but you can use the above information in 2000 to figure out the problem as well.  It just takes a little bit more work since the DMV's are not available.

     

    Tuesday, December 02, 2008 12:08 AM
    Moderator
  • Nothing to report on Page splits or  WAITSTATS.

    However, the sysprocesses shows some blocking.

    select * from sysprocesses where blocked > 0 shows sometimes 23 records during the long durations. Is there a way to capture the spid and insert it as a variable in the INPUTBUFFER statement? I'm a little unclear on how to make this work. The spid seems to change from run to run.

    I have been putting in a QA

    dbcc inputbuffer(77)
    dbcc inputbuffer(99)
    etc
    select * from sysprocesses where blocked > 0

    and after each run, the spids change from the select statement and I add a new dbcc line, but it's like shooting at a moving target.


    Tuesday, December 02, 2008 6:06 PM
  • I'd recommend that you read the following KB article:

     

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;271509

     

    In it the PSS team provides a stored procedure for monitoring blocking and lock contention in the db engine.  This is likely going to be one of the best ways of attacking this. 

    Tuesday, December 02, 2008 6:10 PM
    Moderator