Um dos principais fatores na otimização do desempenho de consultas no SQL Server é a colocação de índices não clusterizados. Por padrão, os índices não clusterizados são criados no mesmo grupo de arquivos da tabela base. No entanto, é recomendado armazenar os índices não clusterizados em grupos de arquivos separados, criados em discos diferentes. Isso pode melhorar significativamente o desempenho das consultas distribuindo a carga de E/S em vários discos.

Neste post do blog, discutiremos como usar o Gerenciamento Baseado em Políticas (PBM) no SQL Server para identificar índices não clusterizados que foram criados no grupo de arquivos errado.

Usando sp_msforeachdb

Um método para identificar índices não clusterizados criados no grupo de arquivos errado é usando o procedimento armazenado não documentado sp_msforeachdb. Esse método pode ser usado no SQL Server 2005, que não possui o Gerenciamento Baseado em Políticas.

Aqui está um exemplo de consulta que pode ser usada:

CREATE TABLE #indexesStats (
    [dbid] int,
    dbname nvarchar(50),
    objName nvarchar(100),
    indname nvarchar(100)
)

EXEC dbo.sp_msforeachdb 'INSERT INTO #indexesStats
SELECT db_id(''?''), ''?'' as dbname, o.name as objName, i.name as indName
FROM [?].sys.indexes i JOIN
    [?].sys.objects o ON i.object_id = o.object_id
WHERE i.index_id > 1 -- apenas índices não clusterizados
    AND i.data_space_id = 1 -- grupo de arquivos PRIMÁRIO
    AND o.is_ms_shipped = 0 -- tabelas do MS
    AND db_id(''?'') > 4' -- não bancos de dados do sistema

SELECT [dbid], dbname, objName, indName FROM #indexesStats

DROP TABLE #indexesStats

No entanto, é importante observar que este método usa um procedimento armazenado não documentado que pode ser removido em versões futuras do SQL Server.

Usando o Gerenciamento Baseado em Políticas

Uma abordagem mais recomendada é usar o Gerenciamento Baseado em Políticas (PBM) no SQL Server para identificar índices não clusterizados criados no grupo de arquivos errado. O PBM permite que você defina condições e políticas para aplicar as melhores práticas e padrões em suas instâncias do SQL Server.

Aqui estão as etapas para criar uma política para verificar índices não clusterizados no grupo de arquivos errado:

1. Crie condições para verificar índices não clusterizados, tabelas não do sistema e o nome do grupo de arquivos.
2. Crie uma política e defina as condições para verificar índices não clusterizados, tabelas não do sistema e o nome do grupo de arquivos.
3. Avalie a política para identificar índices não clusterizados criados no grupo de arquivos errado.

Ao usar o PBM, você pode facilmente aplicar as melhores práticas e garantir que os índices não clusterizados sejam colocados no grupo de arquivos correto, melhorando assim o desempenho das consultas.

Conclusão

A colocação correta de índices não clusterizados no SQL Server pode melhorar significativamente o desempenho das consultas. Ao usar o método sp_msforeachdb ou o Gerenciamento Baseado em Políticas, você pode identificar índices não clusterizados que foram criados no grupo de arquivos errado. Isso permite que você tome ações corretivas e otimize seu banco de dados para obter um melhor desempenho.