SQL Server Performance

"select * from sysdatabases" SLOW (SQL 7)

Discussion in 'Performance Tuning for DBAs' started by lietzc, Nov 2, 2004.

  1. lietzc New Member

    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
  2. Twan New Member


    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
  3. lietzc New Member

    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
  4. Luis Martin Moderator

    Run:

    USE Database
    sp_spaceused @updateusage = 'TRUE'

    for each database, and try again.




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  5. lietzc New Member

    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
  6. satya Moderator

    Run DBCC CHECKDB against MASTER database when the system usage is very low.
    Also refer to the event viewer for any information on h/w of server

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. vbkenya New Member

  8. lietzc New Member

    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
  9. simas New Member

    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
  10. satya Moderator

    In general using Enterprise Manager gives flaky results sometimes and if possible try to reboot the box which may subside the issue related to the resource usage.

    I presume the SQL & OS is on the latest service packs.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page