SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 performance issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nextaxtion
Starting Member

India
32 Posts

Posted - 09/20/2013 :  01:44:13  Show Profile  Reply with Quote
hi team,

i have a procedure that do multiple calculation , insert, update , select data from tables, execute other procedures.
My problen is that it is taking too much time to complete but i am not able to find which part of procedure is taking time.
Is ther any way to get know the exact part of whole proc. I tried various method like profler and long rnning queries finding an activity monitor
but that help nothing.

Kindly suggest me correct approach.

prithvi nath pandey

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 09/20/2013 :  03:02:39  Show Profile  Reply with Quote
The debug button?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 09/20/2013 :  08:32:20  Show Profile  Reply with Quote
quote:
Originally posted by nextaxtion

hi team,

i have a procedure that do multiple calculation , insert, update , select data from tables, execute other procedures.
My problen is that it is taking too much time to complete but i am not able to find which part of procedure is taking time.
Is ther any way to get know the exact part of whole proc. I tried various method like profler and long rnning queries finding an activity monitor
but that help nothing.

Kindly suggest me correct approach.

prithvi nath pandey

You can turn on "Query->Include Actual Execution Plan" option (Control-M), run the query and look at the execution plan. It will show you the relative costs of various parts in the stored procedure to help you narrow down where the bottlenecks are. Once you find those, you will get some clues.

Another possibility is to insert debug statements within the stored procedure that prints out the elapsed time, so you can zero in on the parts that are consuming most time.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/22/2013 :  02:30:36  Show Profile  Reply with Quote
You should also be able to find out time taken by individual parts through profiler. which template/counters were you using in profiler?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2076 Posts

Posted - 09/23/2013 :  10:44:15  Show Profile  Visit jackv's Homepage  Reply with Quote
As suggested - grabb the queries , with a tool such as Profiler - analyse the Exceution Plans.


There are also some other approaches - such as using DMVs - looking for high impact queries - http://www.sqlserver-dba.com/2012/11/sql-server-find-high-impact-queries-with-sysdm_exec_query_stats.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Edited by - jackv on 09/23/2013 10:46:01
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000