[SQL Server] Calculer la densité des index
DECLARE @requete AS NVARCHAR(MAX)=''
,@nom_table AS NVARCHAR(150)='MaTable' --Pour traiter toutes les tables il faut renseigner la valeur NULL dans la variable @nom_table
DROP TABLE IF EXISTS dbo.IndexAnalyse_densite_colonnes
CREATE TABLE dbo.IndexAnalyse_densite_colonnes
(
num_lig INT IDENTITY(1,1)
,nom_table VARCHAR(75) NULL
,nom_index VARCHAR(75) NULL
,champ_index VARCHAR(75) NULL
,densite FLOAT NULL
)
SELECT
@requete = @requete +
requete FROM (
SELECT DISTINCT 'BEGIN TRY IF NOT EXISTS (SELECT nom_table FROM dbo.IndexAnalyse_densite_colonnes WHERE nom_table=''' + nom_table + ''' AND champ_index=''' + champ_index + ''' ) BEGIN INSERT INTO dbo.IndexAnalyse_densite_colonnes (nom_table,nom_index,champ_index,densite)
SELECT ''' + nom_table + ''' AS nom_table,''' + nom_index + ''' AS nom_index,''' + champ_index + ''' AS champ_index,(1.000000/COUNT(' + QUOTENAME( champ_index ) + ')) AS densite FROM (
SELECT DISTINCT ' + QUOTENAME( champ_index) + ' FROM [dbo].' + nom_table + ') T1 END
ELSE
BEGIN
INSERT INTO dbo.IndexAnalyse_densite_colonnes (nom_table,nom_index,champ_index,densite)
SELECT TOP 1 ''' + nom_table + ''' AS nom_table, ''' + nom_index + ''' AS nom_index,''' + champ_index + ''' AS champ_index, CAST(densite AS NVARCHAR(15)) AS densite
FROM dbo.IndexAnalyse_densite_colonnes WHERE nom_table=''' + nom_table + ''' AND champ_index=''' + champ_index + '''
END END TRY BEGIN CATCH SELECT err=ERROR_NUMBER(), liberr=ERROR_MESSAGE() END CATCH;' AS requete
FROM (
SELECT OBJECT_NAME(T1.object_id) as nom_table ,T1.name as nom_index,T3.name as champ_index FROM sys.indexes T1
JOIN sysindexkeys T2 ON T2.id=T1.object_id AND T2.indid=T1.index_id
JOIN syscolumns T3 ON T3.id = T2.id AND T3.colid = T2.colid
JOIN sys.index_columns T4 on T4.object_id = T2.ID and T4.index_id = T2.indid and T4.column_id = T3.colid
JOIN sys.objects T5 ON T5.object_id=T1.object_id
WHERE T5.[type]='U' AND T4.is_included_column=0
AND OBJECT_NAME(T1.object_id)=ISNULL(@nom_table,OBJECT_NAME(T1.object_id))
) R1 ) RF
EXEC (@requete)
SELECT * FROM dbo.IndexAnalyse_densite_colonnes
ORDER BY nom_table ASC,densite ASC
DROP TABLE IF EXISTS dbo.IndexAnalyse_densite_champs_index_theorique
SELECT nom_table ,nom_index,STRING_AGG(champ_index,' | ') WITHIN GROUP (ORDER BY densite ASC) champs_index
INTO dbo.IndexAnalyse_densite_champs_index_theorique
FROM dbo.IndexAnalyse_densite_colonnes
GROUP BY nom_table ,nom_index
ORDER BY nom_table ,nom_index
CREATE NONCLUSTERED INDEX [IX_IndexAnalyse_ordre_champs_index_theorique_nom_index_nom_table] ON dbo.IndexAnalyse_densite_champs_index_theorique
(
nom_index ASC,
nom_table ASC
)
DROP TABLE IF EXISTS dbo.IndexAnalyse_ordre_champs_index_existant
SELECT OBJECT_NAME(T1.object_id) as nom_table ,T1.name as nom_index
,STRING_AGG(T3.name,' | ') WITHIN GROUP (ORDER BY T2.keyno ASC) champs_index
INTO dbo.IndexAnalyse_ordre_champs_index_existant
FROM sys.indexes T1
JOIN sysindexkeys T2 ON T2.id=T1.object_id AND T2.indid=T1.index_id
JOIN syscolumns T3 ON T3.id = T2.id AND T3.colid = T2.colid
JOIN sys.index_columns T4 on T4.object_id = T2.ID and T4.index_id = T2.indid and T4.column_id = T3.colid
JOIN sys.objects T5 ON T5.object_id=T1.object_id
WHERE T5.[type]='U'
AND OBJECT_NAME(T1.object_id)=ISNULL(@nom_table,OBJECT_NAME(T1.object_id))
and is_included_column=0
and T1.type>0 --exclut les heaps
GROUP BY OBJECT_NAME(T1.object_id),T1.name
ORDER BY OBJECT_NAME(T1.object_id),T1.name
CREATE NONCLUSTERED INDEX [IX_IndexAnalyse_ordre_champs_index_existant_nom_index_nom_table] ON dbo.IndexAnalyse_ordre_champs_index_existant
(
nom_index ASC,
nom_table ASC
)
SELECT T1.nom_table,T1.nom_index,T1.champs_index AS ordre_champs_index_existant,T2.champs_index AS ordre_champs_index_theorique
,CASE WHEN T1.champs_index=T2.champs_index THEN 'OK' ELSE 'NO' END AS EtatOrdreChampsIndex
FROM dbo.IndexAnalyse_ordre_champs_index_existant T1
LEFT JOIN dbo.IndexAnalyse_densite_champs_index_theorique T2 ON T2.nom_table=T1.nom_table AND T2.nom_index=T1.nom_index
WHERE CHARINDEX('|',T1.champs_index,0)<>0
ORDER BY T1.nom_table,T1.nom_index