Index

-- Index fragmentation 
SELECT COALESCE(Quotename(D.NAME), N'-') + N'.' 
       + COALESCE(Quotename(Object_schema_name(IPS.object_id, D.database_id)), 
       N'-') 
       + N'.' 
       + COALESCE(Quotename(Object_name(IPS.object_id, D.database_id)), N'-') AS [Object], 
       I.NAME AS [Index], 
       IPS.partition_number AS [Partition], 
       IPS.index_level AS [Level], 
       CONVERT(DECIMAL(38, 2), IPS.page_count / 128.0)   AS [Size (MB)], 
       IPS.avg_fragmentation_in_percent   AS [Mean fragementation (%)], 
       -- Only relevant where in-order pages are faster to read or write. 
       IPS.avg_page_space_used_in_percent AS [Mean  space Used (%)] 
FROM   sys.databases AS D 
       CROSS apply sys.Dm_db_index_physical_stats(D.database_id, NULL, NULL, 
     NULL, 
     N'SAMPLED' 
     /* For non-leaf pages use N'DETAILED'*/) AS IPS 
       INNER JOIN sys.indexes AS I 
        ON I.object_id = IPS.object_id 
    AND I.index_id = IPS.index_id 
WHERE  D.database_id = Db_id() 
ORDER  BY [object], 
   [index], 
   [partition], 
   [level]; 
GO 
-- Index usage. 
SELECT COALESCE(Quotename(Db_name(US.database_id)), N'-') 

N'.' 
COALESCE(Quotename(Object_schema_name(US.object_id, US.database_id)), 
N'-') 
N'.' 
COALESCE(Quotename(Object_name(US.object_id, US.database_id)), N'-') AS [Object], 
I.NAME AS [Index], 
CONVERT(DECIMAL(38, 2), PS.[page count] / 128.0) AS [Size (MB)], 
( US.user_seeks + US.user_scans 
US.user_lookups )  AS [User operations], 
US.user_seeks AS [User seeks], 
US.user_scans AS [User scans], 
US.user_lookups AS [User bookmark lookups], 
US.user_updates AS [User updates] 
FROM   sys.dm_db_index_usage_stats AS US 
CROSS apply (SELECT IPS.object_id, 
IPS.index_id, 
Sum(IPS.page_count) 
FROM   sys.Dm_db_index_physical_stats(US.database_id, 
US.object_id, 
US.index_id, NULL, 
N'SAMPLED' 
/ For non-leaf pages use N'DETAILED'/) 
AS IPS 
GROUP  BY IPS.object_id, 
IPS.index_id) AS PS ([Object], [Index], 
[Page count]) 
INNER JOIN sys.indexes AS I 
ON I.object_id = US.object_id 
AND I.index_id = US.index_id 
WHERE  US.database_id = Db_id() 
ORDER  BY [user operations], 
[object], 
[index]; 

GO 
-- Missing indexes 
SELECT COALESCE(Quotename(Db_name(MID.database_id)), N'-') 

N'.' 
COALESCE(Quotename(Object_schema_name(MID.object_id, MID.database_id)), 
N'-') 
N'.' 
COALESCE(Quotename(Object_name(MID.object_id, MID.database_id)), N'-') 
AS [Object], 
Round(MIGS.avg_total_user_cost  ( MIGS.avg_user_impact / 100.0 )  ( 
MIGS.user_seeks + MIGS.user_scans ), 0) 
AS [Relative cost], 
Round(MIGS.avg_total_user_cost, 2) 
[Average cost], 
Round(MIGS.avg_user_impact, 2) 
[Percentage improvement], 
MIGS.user_scans 
AS [User scans], 
MIGS.user_seeks 
AS [User seeks], 
COALESCE(MID.equality_columns, N'') 
AS [Equi-join], 
COALESCE(MID.inequality_columns, N'') 
AS [Inequi-join], 
COALESCE(MID.included_columns, N'') 
AS [Included] 
FROM   sys.dm_db_missing_index_group_stats AS MIGS 
INNER JOIN sys.dm_db_missing_index_groups AS MIG 
ON MIG.index_group_handle = MIGS.group_handle 
INNER JOIN sys.dm_db_missing_index_details AS MID 
ON MID.index_handle = MIG.index_handle 
WHERE  MID.database_id = Db_id() 
ORDER  BY [percentage improvement] DESC; 

GO 
-- Bygga om alla index i databas
DECLARE @Database VARCHAR(255)

DECLARE @Table VARCHAR(255)

DECLARE @cmd NVARCHAR(500)

DECLARE @fillfactor INT 
SET @fillfactor = 90 
DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases

WHERE name IN ('') 
-- Kan även ändras till not in där man väljer bort de db:s man inte vill bygga om i.

ORDER BY 1  
OPEN DatabaseCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN  
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName

                    FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   
-- create table cursor

PRINT (@cmd)

EXEC (@cmd)

OPEN TableCursor   
FETCH NEXT FROM TableCursor INTO @Table

WHILE @@FETCH_STATUS = 0

BEGIN   
   -- SQL 2000 command  
   --DBCC DBREINDEX(@Table,' ',@fillfactor)   

   -- SQL 2005 command  
   SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'  
   EXEC (@cmd)  

   FETCH NEXT FROM TableCursor INTO @Table   
END   
CLOSE TableCursor

DEALLOCATE TableCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor 
--Lista Index för index I varje tabell I en databas med fillfactor ocg fragmentation
-- SQL Server 2005/2008 Index Fragmentation Report
-- SQL Server DMV - Dynamic Management View
SELECT schema_name(t.schema_id) AS [Schema],
object_name(ps.object_id) AS [Table],
i.name AS [Index],
ps.Index_type_desc AS IndexType,
convert(TINYINT,ps.avg_fragmentation_in_percent) AS [AvgFrag%],
convert(TINYINT,ps.avg_page_space_used_in_percent) AS [AvgSpaceUsed%],
ps.record_count AS RecordCnt,
ps.fragment_count AS FragmentCnt,
i.fill_factor AS [FillFactor]
FROM     sys.dm_db_index_physical_stats(NULL,
NULL,NULL,NULL,'SAMPLED') ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
INNER JOIN sys.tables t 
ON ps.object_id = t.object_id
WHERE ps.avg_fragmentation_in_percent > 20
ORDER BY IndexType
--Lista Index för index I varje tabell på ytterligare ett sätt
SELECT ps.database_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name 
FROM sys.objects so INNER JOIN sys.indexes 
ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'SAMPLED') AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
--WHERE ps.database_id = DB_ID(5) AND si.name is not null AND
where ps.avg_fragmentation_in_percent > 0 
ORDER BY ps.avg_fragmentation_in_percent desc
-- IndexFragemntation alla tabeller I en databas
SELECT object_name(IPS.object_id) AS [TableName], 
SI.name AS [IndexName], 
IPS.Index_type_desc, 
IPS.avg_fragmentation_in_percent, 
IPS.avg_fragment_size_in_pages, 
IPS.avg_page_space_used_in_percent, 
IPS.record_count, 
IPS.ghost_record_count,
IPS.fragment_count, 
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(N'[Db_Name]'), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,5
GO
 
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2013
SELECT TOP 25
dm_mid.database_id AS DatabaseID, 
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
object_name(dm_mid.object_id,dm_mid.databaseid) AS [TableName],
'CREATE INDEX [IX' + object_name(dm_mid.object_id,dm_mid.databaseid) + ''

REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equalitycolumns,''),', ',''),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequalitycolumns IS NOT NULL THEN ''
ELSE ''
END
REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequalitycolumns,''),', ',''),'[',''),']','')
']'
' ON ' + dm_mid.statement
' (' + ISNULL (dm_mid.equality_columns,'')
CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
ISNULL (dm_mid.inequality_columns, '')
')'
ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC 
GO

-- Index Usage
SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
FROM sys.dm_db_index_usage_stats sis
INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
-- Index Size
SELECT
i.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.indexes                AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.tablename')
GROUP BY i.name
ORDER BY i.name