Otimizando o SQL Server para um aplicativo que acessa repetidamente o banco de dados com exatamente a mesma consulta?

O fato de as consultas serem idênticas não é o problema e, de certa forma, ajuda, pois o Plano de Execução é armazenado em cache e as páginas de dados necessárias para a consulta ainda devem ser armazenadas em cache. O problema tenderia a ser a sobrecarga por conexão de autenticação e inicialização da sessão.

A primeira coisa a verificar é: o "pooling de conexões" está sendo usado? Você pode testar isso usando o SQL Server Profiler, selecione o evento "RPC:Completed" na categoria "Stored Procedures", certifique-se de que "TextData", "ClientProcessID" e "SPID" estejam marcados para esse evento (no pelo menos, você pode selecionar outras colunas, se quiser). Em seguida, vá em "Filtros de coluna", selecione "TextData" e na condição "Curtir" adicione a seguinte condição: exec sp[_]reset[_]connection. Agora execute esse rastreamento. Se você vir instâncias de exec sp_reset_connection chegando, isso se deve ao uso do pool de conexões. Mas isso não significa necessariamente que é este aplicativo que o está usando. Então olhe para um dos "SPID"

SELECT sssn.login_time,
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       conn.*,
       sssn.[program_name],
       sssn.host_process_id,
       sssn.client_interface_name,
       sssn.login_name,
       qry.[text]
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) qry
WHERE conn.session_id <> conn.most_recent_session_id
OR    DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time) > 50
ORDER BY conn.connect_time;

O campo na extrema direita/extremidade tem a consulta mais recente executada. Isso deve confirmar que a sessão é o aplicativo em questão (pelo que está fazendo).

SE você não conseguir encontrar nenhuma evidência de uso do pool de conexões em geral, ou pelo menos usado para este aplicativo, a string de conexão usada pelo aplicativo precisa ser atualizada para o pool de conexões habilitado.

...na maioria dos casos, o aplicativo cliente e o SQL Server estão na mesma máquina e várias instâncias do cliente são acessadas por meio do RDP. Em alguns casos, o cliente está em uma máquina diferente na LAN...

Com base nas informações acima da pergunta, parece que há vários clientes se conectando, correto? Nesse caso, o pool de conexões, embora provavelmente ainda seja uma boa ideia e útil, será menos eficaz, pois cada cliente mantém seu próprio pool de conexões. Ou seja, 5 instâncias (ou qualquer número delas) ainda criarão 5 pools separados para conexões, e cada um reduzirá a sobrecarga de inicialização da conexão para seu respectivo aplicativo, mas não pode reduzir a sobrecarga além disso/para uma única conexão compartilhada). Lembre-se também de que, mesmo com o pool de conexões, se um aplicativo não fechar corretamente suas conexões antes de tentar abrir novas, você ainda terá várias conexões/sessões provenientes de uma determinada instância do aplicativo.

Nesse caso, e no caso de um único aplicativo que não está usando o pool de conexão e não pode ter sua String de conexão atualizada para usar o pool de conexão, então, se possível atualizar o aplicativo, seria bastante útil implementar uma camada de cache como Redis ou memcache (e acredito que AWS e Azure oferecem soluções de cache baseadas em nuvem). Esses acessos repetitivos geralmente podem ser armazenados em cache e ignorar completamente o RDBMS (por um período de tempo especificado, é claro), o que é uma grande parte do motivo pelo qual essas coisas existem.

Agora que acabei de acompanhar os comentários recentes sobre a questão, parece que provavelmente nem a string de conexão nem qualquer parte do aplicativo podem ser modificadas. Nesse caso, não há muito o que fazer, exceto possivelmente verificar se as conexões feitas a partir do aplicativo que está sendo executado no mesmo servidor do SQL Server estão usando Memória Compartilhada ou TCP/IP para conectar e se é TCP /IP para as conexões do mesmo servidor, verifique se o protocolo de Memória Compartilhada está habilitado para SQL Server e, se não estiver, habilite-o. Esta não é uma melhoria garantida, pois é possível que a string de conexão force o protocolo a ser TCP/IP (por exemplo, usando a sintaxe de: server=tcp:{something}), mas ainda vale a pena tentar, pois essa sintaxe provavelmente não está sendo usada.

ATUALIZAR

_{do comentário sobre esta resposta:}

[a consulta acima] não me dá nada, mas olhar sys.dm_exec_connections.connect_timepara o aplicativo em questão é de várias horas a dias atrás. ... em todos os casos ... a diferença entre a conexão e o início da sessão é sempre inferior a 50ms (entre 3 e 16)

Isso pode muito bem indicar o problema, ou pelo menos uma grande parte dele. Se uma conexão foi feita várias horas a dias atrás e a sessão começou imediatamente depois disso, o aplicativo é um aplicativo de desktop que faz uma única conexão e sessão na qual executa todas as consultas (semelhante ao funcionamento das guias de consulta do SSMS) ou o código do aplicativo incorretamente não está fechando o objeto de conexão todas as vezes, caso em que você pode estar vendo muitas conexões simultâneas, muitas das quais foram efetivamente abandonadas, mas ainda ocupando memória (e no SQL Server Express, pode haver uma conexão limite de qualquer maneira).

Tente a seguinte consulta, adaptada da consulta original acima:

SELECT conn.connect_time,
       sssn.login_time,
       CASE WHEN conn.last_read > conn.last_write THEN conn.last_read
            ELSE conn.last_write END AS [LastActivity],
       GETDATE() AS [Now],
       DATEDIFF(MILLISECOND, conn.connect_time, sssn.login_time)
                  AS [MillisecondsBetweenConnectionAndSessionStart],
       DATEDIFF(MILLISECOND, sssn.login_time, CASE WHEN conn.last_read > conn.last_write
                                               THEN conn.last_read ELSE conn.last_write END)
                  AS [MillisecondsBetweenSessionStartAndLastActivity],
       DATEDIFF(MILLISECOND, CASE WHEN conn.last_read > conn.last_write
                         THEN conn.last_read ELSE conn.last_write END, GETDATE())
                  AS [MillisecondsBetweenLastActivityAndNow],
       sssn.[program_name],
       sssn.host_process_id,
       sssn.client_interface_name,
       sssn.login_name,
       qry.[text],
       conn.*
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sssn
        ON sssn.session_id = conn.session_id
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) qry
WHERE  sssn.is_user_process = 1
ORDER BY [MillisecondsBetweenLastActivityAndNow] DESC;

Se o pool de conexões estiver sendo usado, você verá linhas com valores altos para o MillisecondsBetweenConnectionAndSessionStartcampo, mas valores muito mais baixos para o MillisecondsBetweenSessionStartAndLastActivitycampo. A razão é que uma conexão é estabelecida e reutilizada. Cada vez que a conexão é reutilizada, o login_timeredefine para o SqlConnection.Openevento mais recente e imediatamente executa a consulta.

Se você tiver um aplicativo de desktop com uma conexão estável (como SSMS), verá o comportamento oposto ao pool de conexão: em vez disso, você terá linhas com valores baixos para o MillisecondsBetweenConnectionAndSessionStartcampo, mas valores muito mais altos para o MillisecondsBetweenSessionStartAndLastActivitycampo. A razão é que a conexão, uma vez estabelecida, nunca é fechada e simplesmente tem consultas continuadas a serem executadas nela.

Se você tiver um aplicativo que não está fechando suas conexões, mas ainda está abrindo novas conexões (devido a um erro ou mal-entendido de como funciona o pool de conexões - estando o pool ativado ou não), você não apenas terá muito de linhas, mas terão um valor baixo para o MillisecondsBetweenConnectionAndSessionStartcampo, mas valores muito mais altos para o MillisecondsBetweenLastActivityAndNowcampo. Isso acontece se uma conexão for feita, usada uma vez e depois SqlConnection.Open()chamada antes de chamar SqlConnection.Close()ou SqlConnection.Dispose()( Dispose()chamará Close()e será chamada automaticamente se o SqlConnectionobjeto tiver sido criado em uma using()construção).

Se você não pode alterar as consultas, não pode otimizar isso. A partir dos números que você postou, quase todo o tempo não é gasto na execução da consulta.

Agora, cada consulta acima é executada muito, muito rápido, estamos falando de algumas dezenas de microssegundos em cada caso. Na verdade, se você somar todas as 1.031 consultas usadas para fazer este relatório, o tempo total usado para todas elas é de apenas 59.193 microssegundos, ou apenas 59 milissegundos.

Portanto, mesmo que você otimize a execução da consulta para zero, isso não terá muito impacto.

(Na verdade, eu não compro o número 10us. Mesmo um SELECT NULLleva ~ 150us de ponta a ponta quando executado usando ADO.NET no transporte de memória compartilhada em uma conexão aberta. Mas qualitativamente pode ser verdade.)