"select * from sysdatabases" SLOW (SQL 7)

I have two SQL Server 7 instances that within the past three weeks started having performance problems with opening up the list of databases in Enter. Mgr. I narrowed it down to the call to "select * from sysdatabases" which is extremely slow.

I have verified that ALL databases on both instances have the autoclose property off, so that's not the problem. I have also logged onto the box and have the same problem, so it's not network related.

When I look for specific database by name ("select * from sysdatabases where name = 'xxx'", some of them return quickly and others are slow. But I've found nothing to correlate the quicker ones and the slower ones. Their physical drive location is not the issue either.

Any suggestions on things to look for?

Thanks
Carol

Hi ya,

Do you by chance create and drop databases on a 'more than usual' basis? i.e. do you have applications which frequently create/drop databases? I seem to recall something about that, but it was a long time ago...

what does sp_spaceused sysdatabases return?

Cheers
Twan

Twan,
Interesting idea. I haven't created/dropped an unusual number of db's yet in the past month or so I have marked some as readonly and others as offline. When this problem was brought to my attention, I went and deleted those db's that were ready to be deleted anyway. But the ones that are still readonly or offline are not necessarily those that I have a problem with when I say "select * from sysdatabases where name = 'xx'"
Per you question on what is returned from "sp_spaceused sysdatabases":
name: sysdatabases
rows: 21
reserved: 40 KB
data: 8 KB
index size: 32 KB
unused: 0 KB
Does this show anything unusual to you?

Thanks
Carol

Luis,
I executed the "sp_spaceused @updateusage = 'TRUE'" for each database but still no luck. It takes 29 seconds to pull back 27 rows in the sysdatabases table. Do you have any additional things I can try?

Thanks
Carol

Do you have ODBC Tracing Enabled?

This setting may sometimes cause performance problems with accessing databases.

Check out this KB and see if that is the case:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;268591]




Nathan H.O.
Moderator
SQL-Server-Performance.com

Nathan,
Wow, I thought for sure I had responded to your last idea. ODBC trace is NOT on in any fashion. Thanks for the idea. Any more suggestions would be most appreciated.

Carol

What do you see in Current Activity when you run select * from sysdatabases (if anything)? Is that process waiting for something (if yes, what is it waiting for ), any unusual locking , etc
Also, what is the server connected like in EM - domain or local account (like sa)?

simas