How do I query SQL statement history data?

[former_member211576]

Hi experts,

   How do I query SQL statement history data? For example, I would like to find ut how many times a specific program consumes more than 20% physical reads last night? And what parameters they used? And is there a parameter sniffing issue occurred?

---

the SQL may like:

select Date, Time, % phys Rd, SQL statement from <table unkwown>

where Date = '2012/10/19' and Time < '08:00:00' and % phys Rd > '20%'

[clas_hortien · Accepted solution]

Hello,

there is no such detailed history table in SQL Server. But you can find the badest statements when you query the quest stats DMV. This script will you give the top 5 of logical, physical reads and CPU time.

Regards

Clas

-----------------------------------------------------------------------------

-- Logical Reads

-----------------------------------------------------------------------------

SELECT TOP 5

    total_logical_reads / execution_count AS [log reads/exec],

    total_physical_reads / execution_count AS [phy reads/exec],

    total_worker_time / execution_count AS [cpu time/exec],

    qp.query_plan,

    st.text

    FROM

        sys.dm_exec_query_stats qs

        CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp

            CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st

    ORDER BY

        total_logical_reads / execution_count DESC

-----------------------------------------------------------------------------

-- Physical Reads

-----------------------------------------------------------------------------

SELECT TOP 5

    total_logical_reads / execution_count AS [log reads/exec],

    total_physical_reads / execution_count AS [phy reads/exec],

    total_worker_time / execution_count AS [cpu time/exec],

    qp.query_plan,

    st.text

    FROM

        sys.dm_exec_query_stats qs

        CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp

            CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st

    ORDER BY

        total_physical_reads / execution_count DESC

-----------------------------------------------------------------------------

-- CPU Time

-----------------------------------------------------------------------------

SELECT TOP 5

    total_logical_reads / execution_count AS [log reads/exec],

    total_physical_reads / execution_count AS [phy reads/exec],

    total_worker_time / execution_count AS [cpu time/exec],

    qp.query_plan,

    st.text

    FROM

        sys.dm_exec_query_stats qs

        CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp

            CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st

    ORDER BY

        total_worker_time / execution_count DESC

-----------------------------------------------------------------------------

[former_member211576 · Accepted solution]

Hi Clas,

  Sorry, I did not mean the data in SQL server DMV but in DBACockpit.

This is created by a store procedure which is written by SAP. However, I do not know where it stored.

[clas_hortien · Accepted solution]

Hi,

the table sap_stmt_workload (all lowercase name) is your friend. There will you find all the values of your screenshot.

Regards

Clas