adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Как просмотреть историю запросов mssql

/************************************************************
 *  * k.moskvichev ©
 * Time: 27.06.2019 13:13:52
 ************************************************************/

SELECT 		
      creation_time
           ,last_execution_time
           ,execution_count
           ,total_worker_time / 1000      AS CPU
           ,CONVERT(MONEY ,(total_worker_time)) / (execution_count * 1000) AS [AvgCPUTime]
           ,qs.total_elapsed_time / 1000  AS TotDuration
           ,CONVERT(MONEY ,(qs.total_elapsed_time)) / (execution_count * 1000) AS [AvgDur]
           ,total_logical_reads           AS [Reads]
           ,total_logical_writes          AS [Writes]
           ,total_logical_reads + total_logical_writes AS [AggIO]
           ,CONVERT(
                MONEY
               ,(total_logical_reads + total_logical_writes) / (execution_count + 0.0)
            )                             AS [AvgIO]
           ,CASE 
                 WHEN sql_handle IS NULL THEN ' '
                 ELSE (
                          SUBSTRING(
                              st.text
                             ,(qs.statement_start_offset + 2) / 2
                             ,(
                                  CASE 
                                       WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX) ,st.text)) * 2
                                       ELSE qs.statement_end_offset
                                  END - qs.statement_start_offset
                              ) / 2
                          )
                      )
            END                           AS query_text
           ,DB_NAME(st.dbid)              AS database_name
           ,object_schema_name(st.objectid ,st.dbid) + '.' + OBJECT_NAME(st.objectid ,st.dbid) AS OBJECT_NAME
           ,qp.query_plan
FROM   
      sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp --WHERE --creation_time > '2018-12-14 08:00:00.000' --object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) = 'dbo.ChequeExciseInsert' ORDER BY creation_time DESC

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>