August 1, 2005 at 10:34 am
I am seeing an odd problem in query analyzer. I connect to a server, then click on the dropdown list of databases. When you are first connected, it usually takes a few seconds to show the list the first time, then if you click on a database to USE it, it is almost instantaneous. But now, either QA or the server wants to refresh the list of databases every time you click on it, which takes about 12 seconds. If you USE a database in code, it is almost instantaneous. There are 22 databases on this server, counting model and msdb, the same number there were before this problem popped up. Any ideas? Thanks.
August 1, 2005 at 2:00 pm
Check the "Auto Close" property on your databases. If auto close is enabled it will have to reopen every database everytime browse the databases (use use the database droplist in QA or expand the database tree in EM, etc.) . If you have a lot of databases this can be very slow.
If Auto Close is enabled disabled it on all of your DBs.
Mark
August 1, 2005 at 3:08 pm
Mark: Thanks for your reply, but sp_dboption returns OFF for all databases on that server.
August 1, 2005 at 3:13 pm
Can you fire up the profiler and see that queries take a long time to run?
August 1, 2005 at 5:31 pm
This is interesting. Profiler says that sp_MShasdbaccess is taking a long time. This presumably is the proc that populates the dropdown list of databases in QA. It returns a resultset from sysdatabases that includes this column:
owner = substring(suser_sname(sid), 1, 24)
If I run it directly from a QA window, it takes 13 seconds on the one server, less than a second on another. So I went down the list of all sids that it returns, and determined that the one it slows down on is where the sid is NULL. I also noticed that there are no databases on any server owned by this one particular guy who used to be a DBA here, but quit at the end of April. We think that our (only) network guy finally got around to deleting his NT login over the weekend. This makes suser_sname look hither and yon for him, and slows things down. I ran sp_changedbowner to a generic domain sqladmin account we have, and now sp_MShasdbaccess runs in less than a second. Interesting, in a trivial pursuit kinda way.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply