When Danny finished isolating the databases that were slower to return results for has_dbaccess(), we examined the properties of those databases by running sp_helpdb and looking at the status column in the results. We immediately noticed a similarity: All the slow databases had the AUTOCLOSE option set to ON. We hadn't yet discussed AUTOCLOSE in class, so Danny didn't know why this setting would cause a performance problem. According to SQL Server Books Online (BOL), if a database has this option on, the database shuts down cleanly and frees up its resources after the last user exits. At first, I rejected this setting as the cause of the problem because Danny had said the slowdown happened only when running Enterprise Manager from one client machine. But he explained that he'd meant that it happened on all clients that were accessing one server. Other servers, which had different database lists, didn't display this behavior.
The AUTOCLOSE option setting caused a problem because when SQL Server needs to check the value of has_dbaccess() for a user, it must access the database to examine the sysusers table in that database. If the database is closed (i.e., not in use), SQL Server must take time to open it. Of Danny's 40 databases, 15 had this property set to ON. When he used a simple SELECT statement that called getdate() before and after the SELECT has_dbaccess() statement, he discovered that accessing a closed database on his server took about 2 seconds. Running the complete command to check the has_dbaccess() status in all databases took more than 30 seconds because 15 databases had this option set to ON. And because of the bug, his version of Enterprise Manager ran the 30-second statement for each of his 40 databases! Setting the AUTOCLOSE option to OFF in all his databases reduced the time SQL Server took to expand his list of databases to less than 1 minute.
Postmortem
In this case, a combination of problems was causing slow performance. One problem was a bug with Enterprise Manager (or SQL-DMO, which runs under Enterprise Manager). Another was that the databases were set to AUTOCLOSE—an option not intended for use on a production SQL Server with many users. Note that miscommunication and confusion about the exact problem can hinder you in investigating problems such as this one, so make sure you understand your system's configuration fully.
Finally, to reward Danny for his hard work, I showed him an undocumented command that would let him write one statement to test the time SQL Server took to access each database through the has_dbaccess() function. The undocumented procedure sp_MSforeachdb automatically sets up a loop that lets you list each database name or use each database name in a query, using a question mark as a placeholder for the database name. For example, the following statement prints the name of each database on your SQL Server:
EXEC sp_MSforeachdb 'PRINT ''?'''
You can get the same results just by selecting all rows from the sysdatabases table, but I wanted to show you a simple example of how to use the procedure. This procedure is especially helpful when you want to use each database name as an argument to a function or procedure.
The following code returns the result of the has_dbaccess() function for each database, then selects the current date and time:
EXEC sp_MSforeachdb 'SELECT
''?'', has_dbaccess(''?'')
SELECT datepart(ms,getdate())
SELECT getdate()'
If you compare the milliseconds values in the date/time data for each database, you can tell which databases took longer than others to return the value of has_dbaccess(). Although using the undocumented procedure isn't necessary, it lets you learn something new about SQL Server and stretch your brain in figuring out other places where the procedure might be useful.