[SQL Server] Index Manquants Statistiques
-- Liste des Index Manquants
select object_name(object_id) as objet, d.*,s.* from sys.dm_db_missing_index_details d
join sys.dm_db_missing_index_groups g on d.index_handle=g.index_handle
join sys.dm_db_missing_index_group_stats s on g.index_group_handle=s.group_handle
where database_id=db_id()
order by s.user_seeks desc,object_id
USE MEKTABA
go
--Afficher les index de la table
sp_helpindex 'TABLE_MEKTABA'
--Afficher les dernieres dates de mise à jour des statistiques
SELECT * FROM
(
SELECT O.name AS nom_table,S.name AS stats_name,
STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date
FROM sys.stats S
JOIN sysobjects O ON OBJECT_ID(O.name)=S.object_id
WHERE type='U'
--AND object_id = OBJECT_ID('TABLE_MEKTABA')
) R
ORDER BY statistics_update_date,nom_table
-- ou
SELECT * FROM
(
SELECT O.name AS nom_table,S.name AS stats_name,
STATS_DATE(S.object_id, S.stats_id) AS statistics_update_date
FROM sys.stats S
JOIN sysobjects O ON OBJECT_ID(O.id)=S.object_id
WHERE type='U'
--AND object_id = OBJECT_ID('TABLE_MEKTABA')
) R
ORDER BY statistics_update_date,nom_table
--Mise à jour des statistiques pour la table
UPDATE STATISTICS BASE_MEKTABA..TABLE_MEKTABA
-- Afficher les index fragmentées
----------------------- Requete 1
SELECT a.object_id AS ObjectID,
object_name(b.object_id) nom_table,
b.name AS IndexName,
a.index_id AS IndexID,
a.avg_fragmentation_in_percent AS PercentFragment,
a.fragment_count AS TotalFrags,
a.avg_fragment_size_in_pages AS PagesPerFrag,
a.page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('BASE_MEKTABA'),
NULL, NULL, NULL , 'DETAILED') AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 0
ORDER BY ObjectID, IndexID
----------------------- Requete 2
SELECT object_id AS ObjectID,
index_id AS IndexID,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('BASE_MEKTABA'),
NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent > 0
ORDER BY ObjectID, IndexID
----------------------- Requete 3
SELECT b.name AS IndexName,
a.avg_fragmentation_in_percent AS PercentFragment,
a.fragment_count AS TotalFrags,
a.avg_fragment_size_in_pages AS PagesPerFrag,
a.page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('BASE_MEKTABA'),
OBJECT_ID('Sales.StoreContact'), NULL, NULL , 'DETAILED') AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
WHERE a.avg_fragmentation_in_percent > 0
ORDER BY IndexName
----------------------- Requete 4
DECLARE @IndexName VARCHAR(100)
SET @IndexName = 'PK_StoreContact_CustomerID_ContactID'
DECLARE @IndexID SMALLINT
SET @IndexID =
(SELECT index_id FROM sys.indexes
WHERE name = @IndexName)
SELECT @IndexName AS IndexName,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('BASE_MEKTABA'),
OBJECT_ID('Sales.StoreContact'),
@IndexID, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent > 0
------------- Réorganiser les Index lorsque PercentFragment <= 30
ALTER INDEX PK_StoreContact_CustomerID_ContactID
ON AdventureWorks.Sales.StoreContact
REORGANIZE
------------- Créer les Index lorsque PercentFragment > 30
ALTER INDEX IX_StoreContact_ContactID
ON AdventureWorks.Sales.StoreContact
REBUILD