/******************************************************************************
* INDEX À SUPPRIMER - SQLpro - Frédéric BROUARD - 2022-03-23 - SQL SPOT SARL  *
******************************************************************************/
IF NOT EXISTS (SELECT * 
               FROM   sys.dm_os_sys_info 
               WHERE  sqlserver_start_time < DATEADD(day, -31, GETDATE()))
BEGIN
   DECLARE @DT VARCHAR(16) = (SELECT CONVERT(VARCHAR(16), sqlserver_start_time, 112)
                              FROM sys.dm_os_sys_info)
   RAISERROR('La profondeur d''analyse pour diagnostiquer les index à supprimée est insuffisante, le serveur ayant redémarré le %s, soit moins d''un mois. Renouvellez votre demande ultérieurement.', 16, 1, @DT);
END
ELSE
SELECT DB_NAME() AS DATABASE_NAME, s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,
       i.name AS INDEX_NAME, 
       (SELECT STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal)
        FROM   sys.index_columns AS ic 
               JOIN sys.columns AS c 
                  ON ic.object_id = c.object_id 
                     AND ic.column_id = c.column_id
        WHERE  ic.object_id = ius.object_id 
               AND ic.index_id = ius.index_id
               AND ic.is_included_column = 0) AS INDEX_KEY,
       (SELECT STRING_AGG(c.name, ', ')
        FROM   sys.index_columns AS ic 
               JOIN sys.columns AS c 
                  ON ic.object_id = c.object_id 
                     AND ic.column_id = c.column_id
        WHERE  ic.object_id = ius.object_id 
               AND ic.index_id = ius.index_id
               AND ic.is_included_column = 1) AS INDEX_INCLUDE,
       filter_definition,
       ius.user_updates,
       N'DROP INDEX [' + i.name + N'] ON [' + s.name + N'].[' + o.name + N'];' AS DROP_INDEX
FROM   sys.dm_db_index_usage_stats AS ius
       JOIN sys.indexes AS i 
          ON ius.object_id = i.object_id
	          AND ius.index_id = i.index_id
       JOIN sys.objects AS o 
          ON ius.object_id = o.object_id
	    JOIN sys.schemas AS s 
          ON o.schema_id = s.schema_id
WHERE  ius.database_id = DB_ID()
-- pas de seek, ni de lookup ni de scan
  AND  ius.user_seeks = 0
  AND  ius.user_lookups = 0
  AND  ius.user_scans = 0
-- pas hypothétique
  AND  i.is_hypothetical = 0
-- pas sémantique
  AND  i.is_primary_key = 0
  AND  i.is_unique = 0
  AND  i.is_unique_constraint = 0
-- et qu'il y ait des lignes dans la table
  AND  EXISTS(SELECT * 
              FROM   sys.dm_db_partition_stats AS ps 
			     WHERE  ius.object_id = ps.object_id 
                     AND row_count > 0);
GO

/******************************************************************************
* INDEX À CRÉER  - SQLpro -  Frédéric BROUARD  - 2022-03-23 -  SQL SPOT SARL  *
******************************************************************************/
IF NOT EXISTS (SELECT * 
               FROM   sys.dm_os_sys_info 
               WHERE  sqlserver_start_time < DATEADD(day, -31, GETDATE()))
BEGIN
   DECLARE @DT VARCHAR(16) = (SELECT CONVERT(VARCHAR(16), sqlserver_start_time, 112)
                              FROM sys.dm_os_sys_info)
   RAISERROR('La profondeur d''analyse pour diagnostiquer les index à supprimée est insuffisante, le serveur ayant redémarré le %s, soit moins d''un mois. Renouvellez votre demande ultérieurement.', 16, 1, @DT);
END
ELSE
WITH
IDX_DIAG AS
(
SELECT mid.index_handle, statement,
       COALESCE(equality_columns + N', ' + inequality_columns, 
                equality_columns, inequality_columns) AS INDEX_KEY, 
       COALESCE(included_columns, N'') AS INCLUDE_LIST,
       avg_total_user_cost * avg_user_impact AS GAIN,
       ROW_NUMBER() OVER(ORDER BY avg_total_user_cost * avg_user_impact DESC) AS RN,
       COUNT(*) OVER() AS NB
FROM   sys.dm_db_missing_index_details AS mid
       JOIN sys.dm_db_missing_index_groups AS mig 
          ON mid.index_handle = mig.index_handle
       JOIN sys.dm_db_missing_index_group_stats AS migs 
          ON mig.index_group_handle = migs.group_handle
),
IDX_GAIN AS
(
SELECT DISTINCT statement, INDEX_KEY, INCLUDE_LIST
FROM   IDX_DIAG
WHERE  (100.0 * RN) / NB < 30 --> 30% des index les plus performants
),
IDX_METER AS
(
SELECT id.statement, id.INDEX_KEY, id.INCLUDE_LIST, MAX(GAIN) AS GAIN_MAX
FROM   IDX_DIAG AS id
       JOIN IDX_GAIN as ig
          ON id.statement = ig.statement 
             AND id.INDEX_KEY = ig.INDEX_KEY
             AND id.INCLUDE_LIST = ig.INCLUDE_LIST
GROUP  BY id.statement, id.INDEX_KEY, id.INCLUDE_LIST
)
SELECT *,
       N'CREATE INDEX X_SQLpro__' + REPLACE(CAST(NEWID() AS VARCHAR(38)), N'-', N'_')
       + N'__' + REPLACE(CAST(CAST(GETDATE() AS DATE) AS CHAR(10)), N'-', N'') 
       + N' ON ' + statement 
       + N' (' + INDEX_KEY + N')'
       + CASE INCLUDE_LIST WHEN N'' THEN N''
              ELSE N' INCLUDE(' + INCLUDE_LIST + N')' END + N';' AS SQL_CMD
FROM IDX_METER
ORDER BY GAIN_MAX DESC;