September 11, 2003 at 10:27 pm
Hi All,
Can any tell me how to find out How a Query is performing which is written within a Stored Procedure?
Thanks in Advance,
Rahul
September 11, 2003 at 11:08 pm
As a start, you can trace it with SQL Profiler.
Trace Event: SP:StmtCompleted
Trace Columns: default + databaseid + objectid
Filters: databaseid = <your database id>, objectid = <object id of the SP>, optionally... textdata like '%<bits of the query.%'
Then run SP and look at CPU, reads, writes, duration.
Cheers,
- Mark
Cheers,
- Mark
September 26, 2003 at 8:26 am
Hi Rahul,
1. You can use Query Analyser to get the excecution plan of your whole proc.
2. Take SQL-Statements out of the proc and get execution plan in Query Analyser for every single statement.
3. You could integrate a output which shows you the number of rows processed
DECLARE @X_COUNTNUM INT
SET @X_COUNTNUM = 0
SET @X_COUNTNUM = @X_COUNTNUM + 1
IF (@X_COUNTNUM % 1000) = 0
BEGIN
PRINT @X_COUNTNUM
END
If you start your proc in query anlayser you could see in you can see time and rowcount in output window.
HTH
Volker
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply