Query Performance within Stored Procedure

• Rahuul

  Mr or Mrs. 500

  Points: 581

  More actions

  September 11, 2003 at 10:27 pm

  #106495

  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

• mccork

  SSC-Insane

  Points: 22288

  More actions

  September 11, 2003 at 11:08 pm

  #473857

  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

• v_schoen

  SSC Journeyman

  Points: 80

  More actions

  September 26, 2003 at 8:26 am

  #476023

  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