Profiler 2000 question

[katbear]

Hi,

I am running a trace on a 2000 server.

It is tracing a stored procedure, called proc1. Proc1 also calls other stored procedures within it.

Currently proc1 takes over 50 minutes to run... trying to find out why.

However, when I run the trace, it gets "stuck" on a particular UPDATE statement for about 20 MINUTES. That is, the "text data" field shows the UPDATE statement, and just hangs for about 20 minutes.

However, if I run this UPDATE statement by itself, it only takes about 20 SECONDS to run.

So it seems that profiler is not showing all the information as to why this part is taking 20 minutes to run. That is, there must be other things going on besides just the UPDATE statement, right? Since the UPDATE statement really only takes 20 seconds, not 20 minutes.

Is there a way to dig down deeper into the issue and have it show up in profiler??

Thanks

 

[katbear]

A little more information. There is a Hash Warning right after the update statement, and now it's hanging on the Hash Warning.

What is this Hash Warning and what can be done about it?

When I run an execution plan on this UPDATE statement by itself, I do see expensive Hash operations. However, the UPDATE statement only takes 20 seconds.

??

 

[mrdenny]

What does the execution plan look like when you run the procedure? The execution plans may be different.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com