locked
index Execution Not Capturing from Sql query RRS feed

  • Question

  • Dear all

    we have designed SSIS (Sql server 2008 r2) Packages.Pkg has Designed

    1.truncate all tables using  Execute Sql Task( inside task using Stored Procedure )

    2. truncate all ReportTables using  Execute Sql Task( inside task using Stored Procedure )

    3.Drop Index Container(Sequence Container used here)

    inside of this container  10 Execute Sql Tasks are Existing .Each Execute Sql task is calling one Stored procedure.Each Stored procedure is having Droping Existed indexes on tables

    After that i am loading the Data next

    Create  index container(Sequence Container used here)

    inside of this container  10 Execute Sql Tasks are Existing .Each Execute Sql task is calling one Stored procedure.Each Stored procedure is having Creating indexes on tables.

    here i have Configured Text log file i capturing the data  i  have Exported the data into my sql server 2008 r2(flat file to oledbdestination)

    here we have created Sql query for to find how much time is taking by containers on Everyday At execution   

    query is fetching the all taskExecution time but

    My Query is NOT FETCHING THE CREATE DROP INDEX CONTAINER(tasks) EXECUTION TIME

    my query is existed like this

    SELECT * FROM(
    SELECT MIN(starttime)starttime,MAX(endtime)Endtime,
    DATEDIFF(MI,MIN(starttime),MAX(endtime))AS [Time Taken In Minutes],
    [SOURCE],CONVERT(varchar,starttime,103)etl_date FROM sysssislog
    WHERE
    starttime >= '19-JULY-2012' and starttime < '19-JULY-2012'
    and [event] in ('OnPreExecute','OnPostExecute')

    GROUP BY source, CONVERT(varchar,starttime,103)
    )a  --where [source] like '%import4%'

    --ORDER BY etl_date,[Time Taken In Minutes] desc
    union all

    SELECT * FROM(
    SELECT MIN(starttime)starttime,MAX(endtime)Endtime,DATEDIFF(MI,MIN(starttime),MAX(endtime))AS [Time Taken In Minutes],[SOURCE],CONVERT(varchar,starttime,103)etl_date FROM sysssislog
    WHERE
    starttime >= '19-JULY-2012' and starttime < '2012-07-19 16:45:33.000'
    and [event] in ('OnPreExecute','OnPostExecute')

    GROUP BY source, CONVERT(varchar,starttime,103)
    )a  --where [source] like '%import4%'

    ORDER BY --[source],
    [Time Taken In Minutes] desc

    how to fetch my create and drop index container Execution time from the query

    pls guide me

    Friday, July 27, 2012 5:31 AM

Answers

  • Hey there, you should use in your statement

    SET STATISTICS IO ON;

    SET STATISTICS TIME ON;

    for more information take a look here


    thx benedikt

    • Proposed as answer by Eileen Zhao Wednesday, August 1, 2012 8:43 AM
    • Marked as answer by Eileen Zhao Tuesday, August 7, 2012 9:25 AM
    Monday, July 30, 2012 8:19 PM