SELECT * WHERE VarCharColumn IN (...) Otimização

SELECT *invalidará qualquer uso ideal de um índice (não está cobrindo), mesmo que o hash seja indexado. Sua varredura de índice provavelmente está no índice clusterizado por causa disso.

Pessoalmente, eu começaria com

• colocando os 3000 valores de pesquisa em uma tabela com um índice
• Editar: de acordo com o comentário de Marian, isso pode ser passado em uma lista ou tabela já
• use isso em qualquer JOIN, IN, EXISTS (mesmo plano geralmente)
• garantir meu índice nos ternos MasterUrls usando Hash e abrange col1, col2, col3

Algo como

CREATE TABLE #foo (Hash ...)
INSERT #foo...
CREATE INDEX IX_FOO ON #foo (hash)

--either
CREATE NONCLUSTERED INDEX IX_Hash ON MasterUrls (hash) INCLUDE (col1, col2, col3)
--or    
CREATE CLUSTERED INDEX IXC_Hash ON MasterUrls (hash)

SELECT col1, col2, col3
FROM [ohb].[dbo].[MasterUrls] M
JOIN
#foo F ON M.Hash = F.Hash

Passe os valores por meio de um parâmetro de valor de tabela. Desta forma, eles já estão em forma de tabela. Em seguida, copie os valores do TVP para uma tabela temporária, que contém um índice clusterizado. Use esta tabela temporária como um membro JOIN de sua consulta.

Remova o SELECT * e altere-o para apenas as colunas necessárias, com as colunas adicionais incluídas. Se SELECT * for necessário, inclua todas as colunas adicionais como colunas incluídas no índice.

Sim: - Isso é possível, basta armazenar todos os id de passagem em uma string com valor separado por vírgula e fazer uma função:

Basta seguir o passo:

Primeiro faça uma função:

 ALTER FUNCTION [dbo].[UDF_IDListToTable]
 (
    @list          [varchar](MAX),
    @Seperator     CHAR(1)
  )
 RETURNS @tbl TABLE (ID INT)
 WITH 

 EXECUTE AS CALLER
 AS
  BEGIN
    DECLARE @position INT
    DECLARE @NewLine CHAR(2) 
    DECLARE @no INT
    SET @NewLine = CHAR(13) + CHAR(10)

    IF CHARINDEX(@Seperator, @list) = 0
    BEGIN
    INSERT INTO @tbl
    VALUES
      (
        @list
      )
END
ELSE
BEGIN
    SET @position = 1
    SET @list = @list + @Seperator
    WHILE CHARINDEX(@Seperator, @list, @position) <> 0
    BEGIN
        SELECT @no = SUBSTRING(
                   @list,
                   @position,
                   CHARINDEX(@Seperator, @list, @position) - @position
               )

        IF @no <> ''
            INSERT INTO @tbl
            VALUES
              (
                @no
              )

        SET @position = CHARINDEX(@Seperator, @list, @position) + 1
    END
END
RETURN
END

depois disso, basta usar com junção interna: -

SELECT  *
FROM    [ohb].[dbo].[MasterUrls] AS mul WITH ( NOLOCK )
        INNER JOIN dbo.UDF_IDListToTable(@IDString, ',') udtl ON mul.hash = udtl.ID; 

Na verdade, resolvi isso de uma maneira MUITO mais rápida e sem SQL.

Em outra etapa antes desta, pego as URLs e IDs da tabela (em vez de apenas a URL, que eu usaria como hash para a pesquisa - esta pergunta), salvo-as na memória e, em seguida, no FS (no caso de falha na memória -- assíncrono, é claro), quando chegou a hora de fazer a pesquisa, li dos meus dados armazenados na memória/FS.

O processo agora leva menos de 5 segundos em média para fazer uma pesquisa e atualizar (a etapa após esta pergunta) os dados de 3.000 linhas. Muito melhor do que 240 segundos em média.