24 lines
1.4 KiB
Transact-SQL
24 lines
1.4 KiB
Transact-SQL
-- diagnotsic des index a créer
|
|
|
|
--!!!!!!!!!!!!!!!!!!!!!!!!! ATTENTION : ne pas utiliser si moins de 30 jours de fonctionnement CONTINU du SGBDR
|
|
|
|
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
|
|
|
|
-- 2 moyens
|
|
-- 1) grossier : je prend le top n des index qui me font ganger le MAX
|
|
SELECT TOP (SELECT COUNT(*) / 3 FROM [dbo].[sys_dm_db_missing_index_details])
|
|
statement, equality_columns, inequality_columns, included_columns
|
|
FROM [dbo].[sys_dm_db_missing_index_details] AS mid
|
|
JOIN [dbo].[sys_dm_db_missing_index_groups] AS mig ON mid.index_handle = mig.index_handle
|
|
JOIN [dbo].[sys_dm_db_missing_index_group_stats] AS mis ON mig.index_group_handle = mis.group_handle
|
|
ORDER BY statement, equality_columns, inequality_columns, included_columns
|
|
|
|
-- 2) analyse des pertinence des index à créer...
|
|
SELECT statement, equality_columns, inequality_columns, included_columns
|
|
FROM [dbo].[sys_dm_db_missing_index_details] AS mid
|
|
ORDER BY statement, COALESCE(equality_columns+ ', ' + inequality_columns, equality_columns, inequality_columns)
|
|
|
|
CREATE INDEX XYZ ON [Anthadine_prod].[dbo].[C_C_CSTPAT] ([CSTTYP_CODE], [INDI_CODE] [CSTPAT_DATE], [CSTPAT_ESTREF])
|
|
INCLUDE ([CSTPAT_CODE], [UTIL_INDI_CODE], [CSTPAT_HEURE], [IDENT], [CSTPAT_TEXTE], [CSTPAT_VALE], [CSTPAT_ESTREF], [TRACE_INS_INDI_CODE], [TRACE_UPD_INDI_CODE], [TRACE_INS_DATE], [TRACE_UPD_DATE], [TRACE_FLAG_DELETE], [EFEV_CODE])
|
|
|