{"id":3438,"date":"2009-08-31T18:07:26","date_gmt":"2009-08-31T16:07:26","guid":{"rendered":"http:\/\/www.glorf.it\/blog\/?p=3438"},"modified":"2010-01-27T08:56:41","modified_gmt":"2010-01-27T07:56:41","slug":"sql-server-langlaeufer","status":"publish","type":"post","link":"http:\/\/www.glorf.it\/blog\/2009\/08\/31\/sql-talk\/sql-server-langlaeufer","title":{"rendered":"SQL Server: Langläufer"},"content":{"rendered":"

In dem Artikel "Wer sind heute meine 5 Langläufer? <\/a>" beschrieb ich ein gefundenes SQL-Statement mit dem man sich Infos zu den langsamsten SQL-Statement anzeigen lassen kann. Im Laufe der zeit modifizierte ich die Abfrage immer weiter.<\/p>\n

Hier ist die verbesserte Fassung, die viele weitere interessante Angaben über den Befehl macht:<\/p>\n

SELECT TOP(200)
\n\t\tqs.execution_count AS "Executions",
\n\t\tCAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))\/1000\/qs.execution_count AS NUMERIC(20,4)) AS "AvgDuration[ms]", – Umrechung in Millisekunden
\n\t\tCAST(CAST(qs.total_worker_time AS NUMERIC(20,4))\/1000\/qs.execution_count AS NUMERIC(20,4)) AS "AvgCpuTime[ms]", – Umrechung in Millisekunden
\n\t\tSUBSTRING(st.text,(qs.statement_start_offset+2)\/2, – Offset wird in Bytes angegeben
\n\t\t\t\t\t(CASE WHEN qs.statement_end_offset = -1
\n\t\t\t\t\t\t\tTHEN LEN(CAST(st.text AS NVARCHAR(MAX)))*2
\n\t\t\t\t\t\t\tELSE qs.statement_end_offset
\n\t\t\t\t\t\tEND – qs.statement_start_offset)\/2)\t\t\t\t\t\t AS "SqlStatement",
\n\t\tCAST(CAST(qs.last_elapsed_time AS NUMERIC(20,4))\/1000 AS NUMERIC(20,4)) AS "LastDuration[ms]", – Umrechung in Millisekunden
\n\t\tCAST(CAST(qs.last_worker_time AS NUMERIC(20,4))\/1000 AS NUMERIC(20,4)) AS "LastCpuTime[ms]", – Umrechung in Millisekunden
\n\t\tCAST(CAST(qs.total_elapsed_time AS NUMERIC(20,4))\/1000 AS NUMERIC(20,4)) AS "TotalDuration[ms]", – Umrechung in Millisekunden
\n\t\tCAST(CAST(qs.total_worker_time AS NUMERIC(20,4))\/1000 AS NUMERIC(20,4)) AS "TotalCpuTime[ms]", – Umrechung in Millisekunden
\n\t\tqs.total_logical_reads\t\t AS "TotalLogicalReads",
\n\t\tqs.total_physical_reads\t\t AS "TotalPhysicalReads",
\n\t\tqs.total_logical_writes\t\t AS "TotalLogicalWrites",
\n\t\tqs.creation_time\t\t AS "FirstExecution",
\n\t\tqs.last_execution_time\t\t AS "LastExecution",
\n\t\tdb_name(st.dbid)\t\t AS "Database"
\nFROM\tsys.dm_exec_query_stats AS qs
\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
\nWHERE qs.total_elapsed_time > 0
\nORDER BY "AvgDuration[ms]" DESC, qs.execution_count DESC<\/code><\/p>\n

Viel Erfolg damit… \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

In dem Artikel "Wer sind heute meine 5 Langläufer? " beschrieb ich ein gefundenes SQL-Statement mit dem man sich Infos zu den langsamsten SQL-Statement anzeigen lassen kann. Im Laufe der zeit modifizierte ich die Abfrage immer weiter. Hier ist die verbesserte Fassung, die viele weitere interessante Angaben über den Befehl macht: SELECT TOP(200) qs.execution_count AS […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[576,579],"_links":{"self":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/3438"}],"collection":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/comments?post=3438"}],"version-history":[{"count":14,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/3438\/revisions"}],"predecessor-version":[{"id":4088,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/3438\/revisions\/4088"}],"wp:attachment":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/media?parent=3438"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/categories?post=3438"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/tags?post=3438"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}