Schlagwort: Indizees

/*
-----------------------------------------------------
-- 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
*/