Missing Indizees – rdfo.org

Häufig scheinen Abfragen überraschend langsam sein, oder immer langsam werden…. Als Tunings-Maßnahme lohnen sich meist als erster Versuch Indizes in der Datenbank. Lediglich sind bestimmte Rechte für der u. g. TSQL-Code notwendig


-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
-- verändert von Frank Odenbreit 2011/2013/2016

/*
Es werden zwar die 50 wichtigsten Indizes geholt und diese nachher sortiert nach ihrem Namen
(Ähnlichkeiten sollte man erkennen können, ggf. anpassen bzw. aus kommentieren)
*/


Select *
from (
SELECT TOP 50
	dm_mid.database_id AS DatabaseID,
	dm_migs.user_seeks as user_seeks,
	dm_migs.avg_user_impact as avg_user_impact, 
	dm_migs.avg_user_impact * (dm_migs.user_seeks+dm_migs.user_scans) AS Avg_Estimated_Impact,
	dm_migs.last_user_seek AS Last_User_Seek,	object_name(dm_mid.object_id) AS [TableName],
	--*,

	'CREATE NONCLUSTERED INDEX [ndx' + object_name(dm_mid.object_id) + '__'
	+ REPLACE(REPLACE(REPLACE(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
	+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
	+ ']'
	
	+ ' 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 + ')', '') 
	+ '  WITH (pad_index=OFF,Fillfactor=90,ignore_dup_key=OFF,allow_row_locks=ON,allow_page_locks=ON,DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]'
	
	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 
) as t
order by Create_Statement asc

/*Anzahl der Sätze Total*/ Select Count(1) from [dbo].[AsyncOperationBase] with (nolock) /*Benutzung*/ Select Sum(Coalesce(Len([Data]),0)) as [Data] ,Sum(Coalesce(Len([WorkflowState]),0)) as [WorkflowState] ,Sum(Coalesce(Len([Message]),0)) as [Message] ,Sum(Coalesce(Len([RegardingObjectIdName]),0)) as [RegardingObjectIdName] ,Sum(Coalesce(Len([RegardingObjectIdYomiName]),0)) as [RegardingObjectIdName] ,Sum(Coalesce(Len([FriendlyMessage]),0)) as [FriendlyMessage] ,Sum(Coalesce(Len([OwningExtensionIdName]),0)) as [OwningExtensionIdName] from [dbo].[AsyncOperationBase] with (nolock) /*Interner Zustand der Tabelle... z.B. falls diese riesig ist*/ SELECT 'AsyncOperationBase' Tablename, type_desc, total_pages * 8 as total_pages_in_KB, used_pages * 8 as used_pages_in_KB, total_pages, used_pages, data_pages,container_id, allocation_unit_id FROM sys.allocation_units WHERE container_id in (SELECT partition_id FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('AsyncOperationBase') ) /*Verteilung bezüglich des StatusCode*/ Select StatusCode, COUNT(1) Anzahl, Case when StatusCode=0 Then 'Ready/WaitingForResources' when StatusCode=10 Then 'Suspended/Waiting' when StatusCode=20 Then 'Locked/InProgress' when StatusCode=21 Then 'Locked/Pausing' when StatusCode=22 Then 'Locked/Canceling' when StatusCode=30 Then 'Completed/Succeeded' when StatusCode=31 Then 'Completed/Failed' when StatusCode=32 Then 'Completed/Canceled' Else 'unbekannter Status' END StatusCode_Readable FROM dbo.[AsyncOperationBase] with (nolock) Group by StatusCode

Schlagwort: Missing Indizees

T-SQL: Fehlende Indizes…

Tabelle AsyncOperationBase: Viele Sperren und viele Sätze