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?
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.
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
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.
I'd recommend that you read the following KB article:
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.
- Proposed as answer by Jonathan KehayiasMVP, Moderator Saturday, January 31, 2009 4:23 PM