February 16, 2007 at 4:24 am
Hi All,
How to find Execution time for SELECT query in SQL 2005?
Your comments will be very useful for me
Suresh
February 16, 2007 at 4:53 am
- if you're using ssms to execute the query, you can see it's elaps time on the bottom of the ssms-window
- if you want more info, check "Displaying Execution Plans by Using the Showplan SET Options " in books online
- you can run a profiler-trace to capture the queries and examine the results
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 16, 2007 at 2:33 pm
You can use the dynamic management view sys.dm_exec_query_stats to pull out execution statistics. I took some code I use to examine the query plans of problem spids (via sys.dm_exec_query_plan), and added a few more queries on the end to pull times:
SET
NOCOUNT ON;
SET STATISTICS IO OFF;
DECLARE
@sql_handle-2 varbinary(64), @plan_handle varbinary(64), @spid int, @sql nvarchar(max)
SELECT
@spid = --<insert your monitored spid here>
SELECT
@sql_handle-2 = ec.most_recent_sql_handle, @plan_handle = qs.plan_handle
FROM sys.dm_exec_connections ec INNER JOIN
sys.dm_exec_query_stats qs ON ec.most_recent_sql_handle = qs.sql_handle
WHERE ec.session_id = @spid
-- display the plan and sql associated with it
SELECT @sql = text FROM sys.dm_exec_sql_text(@sql_handle-2)
SELECT query_plan FROM sys.dm_exec_query_plan(@plan_handle)
@sql
-- as for the timing of the query...
-- Here's where the data is coming from:
SELECT *
FROM sys.dm_exec_query_stats
WHERE sql_handle = @sql_handle-2
-- Provided your execution was the last one, your time is as follows
-- last_elapsed_time is in microseconds, divide by 1000 to get milliseconds or 1000000 to get seconds
SELECT SUM(last_elapsed_time) / 1000.00000 AS [Execution Time (ms)]
FROM sys.dm_exec_query_stats
WHERE sql_handle = @sql_handle-2
-- ...if every statement in the plan was executed by your spid.
Eddie Wuerch
MCM: SQL
February 16, 2007 at 2:48 pm
Or:
DECLARE @starttime DATETIME
DECLARE @endtime DATETIME
SET @starttime = GETDATE()
SET @endtime = GETDATE()
SELECT DATEDIFF(SS, @startdate, @enddate)
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply