forked from vincent/notebook
135 lines
6.9 KiB
MySQL
135 lines
6.9 KiB
MySQL
|
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<EFBFBD>d<EFBFBD>ric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *
|
|||
|
|
* Architecte de donn<EFBFBD>es : expertise, audit, conseil, formation, mod<EFBFBD>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<EFBFBD>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<EFBFBD>d<EFBFBD>ric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com *
|
|||
|
|
* Architecte de donn<EFBFBD>es : expertise, audit, conseil, formation, mod<EFBFBD>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<EFBFBD>fragmentation des idex d'une base *
|
|||
|
|
*******************************************************************************
|
|||
|
|
* INPUTS : *
|
|||
|
|
* n<EFBFBD>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
|
|||
|
|
|
|||
|
|
--> <EFBFBD> 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);
|
|||
|
|
|
|||
|
|
|