I've encountered a bizarre problem with a cross-database query in production under SQL Server 2012 Express Edition. I am not able to create this in my development environments, so I'm thinking it's something with the server's configuration, which was installed with default settings as a named instance.
This is not a query execution performance issue; it is a database connection issue.
Take the following query (MyTable has around 100 rows in it):
SELECT COUNT(*) FROM MyDatabase..MyTable
Execution time runs around 4 seconds. According to Client Statistics, Client Processing time is 0 with "Wait time on server replies" making up the other four seconds of the execution time.
If I first execute USE MyDatabase
and then execute my query, it's instant. If I execute them in sequence:
USE MyDatabase
GO
SELECT COUNT(*) FROM MyDatabase..MyTable
It still takes the extended amount of time as the USE MyDatabase
statement takes a long time to execute. It does not matter whether or not the table name is fully qualified with the database name, nor does using synonyms change things. In the past I ran into something similar and DBCC CHECKDB(<dbname>)
seemed to repair it, but this time around I am unable to get it to behave appropriately. The wait_type
in sys.dm_exec_requests
for the connection is the typical IO_COMPLETION
with a suspended
SPID status. I have attempted to repair and restart the system with no luck. This sort of thing strikes me as having its roots in an authentication issue, but I have no idea what to do to fix it.
Added:
I occasionally get a Database 'MyDatabase' is being recovered. Waiting until recovery is finished
message. I have executed a full repair using the SQL Server 2012 installation tools as well. Any ideas?
It looks like the cause was the AUTO_CLOSE property of the databases. Turning this off ALTER DATABASE MyDatabase SET AUTO_CLOSE OFF WITH NO_WAIT
seems to have fixed the issue.