jeremylim2008 wrote:

> I got a standard system with 3, 000 customer(s) using it. But some
> customers with big transaction when they doing a complicate
> store procedure select statement it will running endless cannot
> finish and will hang there but when I use Task manager checking it is
> still running with near to 100% Usage.
> But when I off all trigger(s) the store procedure will complete
> running.

That's odd. A select statement shouldn't fire triggers. Are you
sure that the procedure isn't doing some sort of data changes?

>
> But this only occur for 2 to 3 database out of 3, 000 databases.
>
> How can I trouble shoot this.

I'd suggest getting gstat -a -r reports on a couple of databases
that aren't having problems and one from each of the databases
that are. Look for data distribution anomalies (e.g. the "good"
databases tend to have 16 records in one table, while the "bad"
databases have 16,000,000 records in that table). And also
look for missing indexes in the anomalous tables.

But first, figure out why triggers seem to be firing if the
procedure is read only.


Regards,


Ann