SQL Server Performance Forum – Threads Archive
Slow population of database list
I have noticed that it takes a very long time on one of my Sql Servers, when using Enterprise Manager, for the list of databases to refresh. The same problem also occurs when using SQL Query Analyser, if you click on the database list drop-down in the toolbar. The server is a dedicated sql server, dual P2 1Ghz with 512 Mb RAM. There are 112 databases on the server, and none of them are particularly large. Tracing the problem to its source, I found the function "hasdbaccess()" being called in the sp_databases stored procedure, which is the one I assumed is being used by QA and EM to get the list of available databases. When I ran hasdbaccess() for any of the databases on the server, it took a very long time to run. Any ideas on how I can speed this up?With 512Mb I don’t thik so.
What it happens with EM is razonable because GUI, but with SQL Analyzer I believe is because memory or because there is a huge use of disk and processor.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
How about database options for AUTO_CLOSE or AUTO_SHRINK etc. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
quote:Originally posted by LuisMartin
With 512Mb I don’t thik so.
What it happens with EM is razonable because GUI, but with SQL Analyzer I believe is because memory or because there is a huge use of disk and processor.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
What it happens with EM is razonable because GUI, but with SQL Analyzer I believe is because memory or because there is a huge use of disk and processor.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Thanks for the advice, but I think that the problem lies with the HasDBAccess() function. It takes a very long time to run against any of the databases on this particular server. The server is more than capable of dealing with complex and demanding queries in other instances. I’m starting to wonder if it’s a problem with the master database. Any further thoughts?
Have you looked at the hasdbaccess function? What does it do? Which tables does it go against etc.
Have you done any work in your master database?
Right, how about service pack on SQL and OS?
I beleive the server h/w should cope up the load and same as the client. Check sql error log, event viewer log for any information.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>