USE master;
GO

CREATE PROCEDURE dbo.sp__ADMIN_RECALC_STATS
AS
/******************************************************************************
* NATURE  : PROCEDURE                                                         *
* OBJECT  : master.dbo.sp__ADMIN_RECALC_STAT                                  *
* CREATE  : 2020-06-26                                                        * 
* VERSION : 1                                                                 *
* SYSTEM  : OUI                                                               *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
*******************************************************************************
* PURPOSE : recalcul des statistiques d'une base                              *
*******************************************************************************
* INPUTS :                                                                    *
*    néant                                                                    *
*******************************************************************************
* EXEMPLE :                                                                   *
*    USE maBase;                                                              *
*    EXEC  sp__ADMIN_RECALC_STAT                                              *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max) = N''
SELECT @SQL = @SQL + N'UPDATE STATISTICS [' + s.name + '].[' 
                                            + o.name + '] ([' 
                                            + st.name + ']) WITH FULLSCAN;' 
FROM   sys.stats AS st
       INNER JOIN sys.objects AS o ON st.object_id = o.object_id
       INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
       CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id)
WHERE modification_counter > 0 
       AND 100.0 * modification_counter / rows >
              CASE  
                 WHEN 10 + (13.8 - LOG(rows)) / 2 < 0.5 
                    THEN 0.5 
                 ELSE 10 + (13.8 - LOG(rows)) / 2 
              END
/* VARIANTE 1 du WHERE
WHERE   modification_counter > 0
  AND   100.0 * modification_counter / rows < 
           CASE WHEN rows + modification_counter < 100000 
              THEN 10 
              ELSE LOG10(modification_counter + rows) 
                   / (1 + (LOG10(modification_counter + rows) - LOG10(100000))) END
*/
/* VARIANTE 2 du WHERE
WHERE 1 = CASE WHEN COALESCE(CAST(modification_counter AS REAL) / rows, 1) 
                     > 0.3
                   THEN 1
                WHEN rows < 100000 AND 
                     COALESCE(CAST(modification_counter AS REAL) / rows, 1) 
                     > 0.1
                  THEN 1
                WHEN rows > 300000000 AND 
                     COALESCE(CAST(modification_counter AS REAL) / rows, 1) 
                     > 0.0048
                  THEN 1
                WHEN rows BETWEEN 100000 AND 300000000 AND   
                     COALESCE(CAST(modification_counter AS REAL) / rows, 1) 
                     > (20 - LOG(rows)) 
                  THEN 1
               ELSE 0
           END;
*/
EXEC (@SQL);
GO

EXEC sp_MS_marksystemobject 'dbo.sp__ADMIN_RECALC_STATS';
GO

CREATE PROCEDURE dbo.sp__ADMIN_DEFRAG_INDEX
AS
/******************************************************************************
* NATURE  : PROCEDURE                                                         *
* OBJECT  : master.dbo.sp__ADMIN_DEFRAG_INDEX                                  *
* CREATE  : 2020-06-26                                                        * 
* VERSION : 1                                                                 *
* SYSTEM  : OUI                                                               *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
*******************************************************************************
* PURPOSE : défragmentation des idex d'une base                               *
*******************************************************************************
* INPUTS :                                                                    *
*    néant                                                                    *
*******************************************************************************
* EXEMPLE :                                                                   *
*    USE maBase;                                                              *
*    EXEC  sp__ADMIN_DEFRAG_INDEX                                             *
*******************************************************************************
* IMPROVE :                                                                   *
*  version Enterprise                                                         *
*  ALTER INDEX ... REBUILD WITH (ONLINE = ON)                                 *
******************************************************************************/
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max) = N''
SELECT @SQL = @SQL + 
       CASE WHEN i.name IS NULL
               THEN N'ALTER TABLE [' + s.name + '].[' + o.name + '] REBUILD;'
            WHEN avg_fragmentation_in_percent > 30
               THEN N'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + o.name + '] REBUILD;'
            ELSE    N'ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + o.name + '] REORGANIZE;'
       END
FROM   sys.dm_db_index_physical_stats(DB_NAME(), NULL, NULL, NULL, NULL) AS ips
       INNER JOIN sys.objects AS o ON ips.object_id = o.object_id
       INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
       INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE  page_count > 64
  AND  avg_fragmentation_in_percent > 10
  AND  ips.index_id < 1000;
EXEC (@SQL);
GO

EXEC sp_MS_marksystemobject 'sp__ADMIN_DEFRAG_INDEX';
GO

--> à mettre dans un travail de l'AGENT SQL 1 fois par jour aux heures creuses
DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL + 'USE [' + name + '];EXEC sp__ADMIN_DEFRAG_INDEX;EXEC sp__ADMIN_RECALC_STATS;'
FROM   sys.databases
WHERE  name NOT IN ('model', 'tempdb')
  AND  state = 0
  AND  source_database_id IS NULL;
EXEC (@SQL);