Why is my select statement so slow? SELECT name FROM sys.databases 
WHERE 
name 
not in
('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC')
AND
CASE WHEN state_desc = 'ONLINE' 
THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') 
END IS NOT NULL


I'm using the above to populate a combobox in a c# winforms project. -The reason I'm filtering out databases that don't include the table is for security purposes.

I added the WHERE clause as before I did that it was taking up to 21 seconds!? 
(This is on a local dev machine with 4 databases(not including system DB's), only one of which has the table I was looking for.)

Now that I have added the where clause it only takes nine seconds.

I can't have it running this slowly, as the program I am writing is going to be in use on machines that can have as many as 20 databases on them. -All with around 35 tables in.
(These aren't servers, these are Support Monkey Machines!)

From the Actual Execution Plan I can see that there are three Clustered Index Seek's taking up 22%, 24% and 48%. -Because this is a 'system' query is there anything I can actually do about that?!

And finally, the bombshell to make this a tad bit harder:
I have no control over the indexes of the databases and I can't change them in anyway, shape or form.
The query is pretty simple and ought to run fast enough.

My guess is that you have a long running transaction performing DDL in one of the databases and your query is getting blocked waiting on a lock.

Unfortunately the metadata functions such as OBJECT_ID don't take account of the transaction isolation level of the outer transaction so even setting the isolation level to allow dirty reads wouldn't help.

Can you try this alternative method of doing the same operation and report back? (If still slow try adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to the beginning)

DECLARE @Databases TABLE(name SYSNAME PRIMARY KEY);

DECLARE @dbname    SYSNAME,
        @tablename SYSNAME = '_LOOK_UP_TABLE'

DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT name
    FROM   sys.databases
    WHERE  state_desc = 'ONLINE'
           AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                             'ReportServer', 'ReportServerTempDB', '1303VISBOC' )

OPEN @C1;

FETCH NEXT FROM @C1 INTO @dbname;

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @nsql NVARCHAR(max) = 'USE ' + QUOTENAME(@dbname) + ';
                                     SELECT @dbname 
                                     FROM sys.tables WITH (READPAST) 
                                     WHERE schema_id=1 
                                           AND Name = @tablename';

      INSERT INTO @Databases
      EXEC sp_executesql
        @nsql,
        N'@dbname SYSNAME, @tablename SYSNAME',
        @dbname,
        @tablename

      FETCH NEXT FROM @C1 INTO @dbname;
  END 

SELECT name
FROM @Databases