sp_help taking 20 seconds?

John Brock · *nix forums beginner Joined: 08 Jun 2005 Posts: 13

My program accesses a Development database when I am developing,
and a Production database when in actual use. The Development
database is always lightning fast, but occasionally the Production
database can be excruciatingly slow. I attributed this partially
to contention, since other production jobs can be hitting the same
server at the same time as mine, but also perhaps to the fact that
my program wasn't always optimized for speed. (I never felt the
need to do so when writing it, since the Development database was
always fast!).

Recently though I noticed in one slow period that sp_help would
repeatedly hang for a full 20 seconds before listing the 50 or so
tables in the Production database. Clearly that can't be the fault
of our coding!

So can this ever be normal, even on a heavily stressed server? I'm
thinking that this might be evidence of some sort of configuration
problem, and if there is reason to believe this is the case I could
go to the database administrators and try to get the problem
resolved. Otherwise I'll just have to try to optimize my program
further, or see if I can get the Production database moved to a
different server.
--
John Brock
jbrock@panix.com

bret@sybase.com · *nix forums addict Joined: 24 May 2005 Posts: 66

Slow system stored procedures is often a sign of contention in tempdb
(since they often use temp tables). If that seems to be the
case, you might look into using the new multiple tempdb feature to
offload some of the tempdb processing.

-bret

John Brock wrote:

Google