Sql-server – How to find the time taken to execute a script

execution-planpartitioningsql serversql-server-2008

As a part of partitioning table,1M records were inserted into a table using insert statement and I forgot to capture the time taken to insert.

Is there any way to find out the time taken to execute that insert statement.
Since its a huge data it's not possible to populate data again.

Best Answer

Try to use sys.dm_exec_query_stats. Find your query, if it's cached:

set transaction isolation level read uncommitted

select 
    top 100
    creation_time,
    last_execution_time,
    execution_count,
    qs.total_elapsed_time/1000 as TotDuration,
    convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
    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
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
order by AvgDur desc
Related Question