Schlagwort: Tuning

Veröffentlicht am

T-SQL: Fehlende Indizes…

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
Veröffentlicht am

Die langsamsten SQL Statements

/*
-----------------------------------------------------
-- Infos zu den langsamsten SQL-Statement 

-- use master
-- Es muss gewisse Rechte bestehen (z.B. "SA") "sysdba" reicht nicht aus"
Hinweis: Durations können z.B. aus dem Einlesen von vielen Millionen von Sätzen stammen
Ebenso bitte beachten, dass AvgDuration[s] meist aus "Warten" von gesperrten Objekten stammen.
Daher sollte die AvgCpuTime und die Executions besonders bewertet werden!

-----------------------------------------------------
Änderungen:
2016-02-19 ORDER BY "AvgCpuTime[s]"
*/



use master
go


SELECT TOP(200)
                qs.execution_count AS "Executions",
                --CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) 
                --CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4))
                --AS "AvgCpuTime[ms]", /*Umrechung_in_Millisekunden*/
                cast(CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
                AS "AvgCpuTime[s]", /*Umrechung_in_Sekunden*/
                SUBSTRING(st.text,(qs.statement_start_offset+2)/2, /*Offset wird in Bytes angegeben*/
                                        (CASE WHEN qs.statement_end_offset = -1
                                                        THEN LEN(CAST(st.text AS NVARCHAR(MAX)))*2
                                                        ELSE qs.statement_end_offset
                                                END - qs.statement_start_offset)/2)
                AS "SqlStatement",

                --AS "AvgDuration[ms]", /*Umrechung_in_Millisekunden*/
                CAST(CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000/qs.execution_count AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
                AS "AvgDuration[s]", /*Umrechung_in_Sekunden*/

                CAST (CAST(CAST(qs.last_elapsed_time AS NUMERIC(20,4))/1000  AS NUMERIC(20,4))  / 1000 as Numeric (20,1))
                AS "LastDuration[s]",  /*Umrechung in Sekunden*/
                CAST( CAST(CAST(qs.last_worker_time AS NUMERIC(20,4))/1000  AS NUMERIC(20,4))   / 1000 as Numeric (20,1))
                AS "LastCpuTime[s]",   /*Umrechung in Sekunden*/
                CAST( CAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))/1000  AS NUMERIC(20,4)) / 1000 as Numeric (20,1))
                AS "TotalDuration[s]", /*Umrechung in Sekunden*/
                CAST ( CAST(CAST(qs.total_worker_time AS NUMERIC(20,4))/1000  AS NUMERIC(20,4))  / 1000 as Numeric (20,1))
                AS "TotalCpuTime[s]",  /*Umrechung in Sekunden*/
                qs.total_logical_reads            AS "TotalLogicalReads",
                qs.total_physical_reads           AS "TotalPhysicalReads",
                qs.total_logical_writes           AS "TotalLogicalWrites",
                qs.creation_time                  AS "FirstExecution",
                qs.last_execution_time            AS "LastExecution",
                db_name(st.dbid)                  AS "Database"
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
WHERE qs.total_elapsed_time  > 0
--	and qs.last_execution_time > '2016-02-18 19:00:00'
--	and (qs.last_execution_time < '2016-02-18 19:00:00'	or qs.creation_time < '2016-02-18 19:00:00')
--ORDER BY "AvgDuration[ms]" DESC, qs.execution_count DESC
--ORDER BY "AvgDuration[s]" DESC, qs.execution_count DESC
ORDER BY "AvgCpuTime[s]" DESC, qs.execution_count DESC

/*
Select * FROM    sys.dm_exec_query_stats AS qs
*/