July 31, 2013 at 4:50 am
Hi Team,
am having 10 select statements and 10 Insert statements, and executed all the 20 statements individually.
Now i want to get the report or result to display all the 20 executed statements.
latest executed queries, am using below code, but that is not corrent.
SELECT top 10
DMExQryStats.last_execution_time AS [Executed At],
DMExSQLTxt.text AS [Query]
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
DMExQryStats.last_execution_time DESC
Please sugest...
July 31, 2013 at 6:58 am
That will show you what is currently in the cache. It is an aggregation of executions for each of your statements in cache, the number of times called, average CPU, execution time, that sort of thing. It should give you a rough overview for what you're looking for.
But if you need to see each execution as it happens, then I'd suggest looking into extended events. This will enable you to capture procedure calls or batches. You can also capture individual statements from either procedures or batches. The particular events you want are rpc_completed for procedures, sql_batch_completed for sql batches, sp_statement_completed for procedure statements, and sql_statement_completed for batch statements. Just be very careful capturing statement level commands. These will generate a lot of information unless you put appropriate filtering in place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply