sql server odbc: Hi,

I've recently started experiencing an error in one of our development
environments whereby the user is pressing "submit" and their query is
timing out. Now, i've ran a trace (using the SQLProfilerTSQL_REPLAY
template in SQL Profiler) while they were performing this process, and
attempted to tune/re-index based on this. however, the resultant
trace log stated that there was no information in the log file which
could be used to re-index or improve performance.

I was wondering if anybody out there would know of a trace which could
be performed to try and locate the source of this problem.

Thanks

Hi,

I think that going after indexes (or the lack of them) isn't going to help
you much in your case. You're probably more likely facing a blocking
situation.

So what I would do is run a SQL Profiler trace (the standard template will
do), hit the SUBMIT button to start executing the query against the
database, and while this is happening, i.e. before the user gets the timeout
message, start monitoring the locks SQL Server has taken out. You can use
sp_lock for this. I'm fairly certain that you should find a blocking
situation which is contributing to your grief. Once you have identified
where the problem lies you need to change your T-SQL statements to minimize
the chance of blocking as much as possible.

Jonathan



[quoted text, click to view]