Eu tenho um problema bastante estranho (quem não tem). Nosso software ETL verifica bancos de dados de origem para reivindicar dados de objetos. Extrai informações de INFORMATION_SCHEMA sobre objetos (tabelas e visualizações)
SELECT
t.[TABLE_SCHEMA],
t.[TABLE_NAME],
c.[COLUMN_NAME],
c.[DATA_TYPE],
c.[CHARACTER_MAXIMUM_LENGTH],
c.[NUMERIC_PRECISION],
c.[NUMERIC_SCALE],
c.[DATETIME_PRECISION],
c.[ORDINAL_POSITION],
c.[IS_NULLABLE],
prim.[CONSTRAINT_NAME] AS [PRIMARY_KEY_NAME],
t.[TABLE_TYPE]
FROM [INFORMATION_SCHEMA].[TABLES] t
INNER JOIN [INFORMATION_SCHEMA].[COLUMNS] c
ON t.[TABLE_CATALOG] = c.[TABLE_CATALOG]
AND t.[TABLE_SCHEMA] = c.[TABLE_SCHEMA]
AND t.[TABLE_NAME] = c.[TABLE_NAME]
LEFT OUTER JOIN (
SELECT tc.[CONSTRAINT_NAME], tc.[CONSTRAINT_SCHEMA], tc.[CONSTRAINT_CATALOG], cc.[TABLE_NAME], cc.[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc
INNER JOIN [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] cc
ON tc.[CONSTRAINT_NAME] = cc.[CONSTRAINT_NAME]
AND tc.[CONSTRAINT_SCHEMA] = cc.[CONSTRAINT_SCHEMA]
AND tc.[CONSTRAINT_CATALOG] = cc.[CONSTRAINT_CATALOG]
AND tc.[TABLE_NAME] = cc.[TABLE_NAME]
AND tc.[TABLE_CATALOG] = cc.[TABLE_CATALOG]
AND tc.[TABLE_SCHEMA] = cc.[TABLE_SCHEMA]
WHERE tc.[CONSTRAINT_TYPE] = 'PRIMARY KEY') prim
ON t.[TABLE_CATALOG] = prim.[CONSTRAINT_CATALOG]
AND t.[TABLE_SCHEMA] = prim.[CONSTRAINT_SCHEMA]
AND t.[TABLE_NAME] = prim.[TABLE_NAME]
AND c.[COLUMN_NAME] = prim.[COLUMN_NAME]
ORDER BY t.[TABLE_SCHEMA], t.[TABLE_NAME], c.[ORDINAL_POSITION]
Para a maioria dos bancos de dados (dependendo da quantidade de objetos e colunas), isso leva um minuto para ser concluído. Para um banco de dados específico, isso leva de 10 a 35 minutos! Não consigo entender o porquê. Não posso alterar a consulta acima (já que está incorporada no software), então estou procurando uma maneira de melhorar o desempenho neste banco de dados
A consulta gera aproximadamente a mesma quantidade de registros. Verifiquei a configuração do banco de dados e são exatamente iguais. Quando testei com 'SET STATISTICS IO ON', ficou claro que havia muita coisa acontecendo no banco de dados de menor desempenho do que nos outros bancos de dados. Coloquei em www.statisticsparser.com e é assim que parece para os de menor desempenho:
Para comparação, é assim que parece um banco de dados normal:
Eu mesmo estou sem opção. Tentei limpar o proc e o cache do sistema (com o DBCC correspondente) e atualizar as estatísticas de todo o banco de dados, mas (como esperado) não adiantou.
Qualquer ajuda é apreciada!
Atenciosamente
EDIT: adicionei os planos de consulta para comparar com um banco de dados com aproximadamente o mesmo tamanho e estrutura no mesmo servidor (que não tem problema de desempenho: https://file.io/ODajcfrzvGTP
Contém 2 arquivos: _fast é do banco de dados sem problemas _slow é do banco de dados com problemas