SQL Storedprocedure trace other than profiler

To capture what are the Stored procedures are being hit in Production environment the profiler would be used. Whether it would be slowdown the environment? Whether it will affect the performance? If so, what are the alternative to trace the SP hits without affecting the performance?
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
Vadim RappConnect With a Mentor Commented:
The only way to track them is to put into each s.p. an sql command that would insert a record into a special table. Which in fact would be pretty much the same profiling.

As I said, the performance won't take any noticeable hit even with full profiling. But you can filter the events so that only the calls to stored procedures will be recorded, as TempDBA suggested above.

Yet another way is - since you said that you are interested in the calls to s/p made by your own application - in your application source find all places where it calls them, and put statements that would write the call into the log, with parameters.
0
 
Meir RivkinConnect With a Mentor Full stack Software EngineerCommented:
that should answer your question (check first comment):
Using SQL Profiler on a database that's in production
0
 
TempDBAConnect With a Mentor Commented:
Good link expert sedgwick.

I would like to add little more. The impact of using a profiler can be reduced by selecting the right events and the only limited desired columns. Remove all extra things that you think may be required in some cases.
         Putting a backend trace with stored procedures is another good way. Lastly, dmvs should come handy here.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Alpesh PatelAssistant ConsultantCommented:
Yes hit the performance but you can audit the limited events and take the benefit of it.
0
 
Easwaran ParamasivamAuthor Commented:
@sedgwick: As per answer that was given in the link that you pointed out is not working.

I run the query after running my application. But the SPs are not traced by below query? Please do suggest solution.

SELECT session_id, num_writes, st.text AS statement_text
FROM sys.dm_exec_connections AS ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
ORDER BY num_writes DESC

Open in new window

0
 
TempDBACommented:
Did you restart your server? or flush the cache?
0
 
Easwaran ParamasivamAuthor Commented:
Each and every time I need to restart the server? In production I don't think so it would possible.
0
 
TempDBACommented:
No, I asked the question because you mentioned that you are not seeing anything with the dmvs. My question just would have helped with answering the issue.
0
 
Easwaran ParamasivamAuthor Commented:
I ran the application and it hits some SPs. From the above mentioned query the SPs are not shown in the result. How to trace those SPs with passed parameter values?
0
 
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
There are many things you can find out without turning on profiler. For example:
Expensive queries:
http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/
0
 
Easwaran ParamasivamAuthor Commented:
But I do want all the SP hits with parameters. I don't analyze the performance of the queries as of now. Please do suggest.
0
 
Vadim RappCommented:
> Whether it would be slowdown the environment?

not to a noticeable degree

> what are the alternative to trace the SP hits without affecting the performance?

debugging it.
0
 
Easwaran ParamasivamAuthor Commented:
I don't want to debug SP. While running my application I would like to trace what are the SPs are being hit without using profiler (In Production)
0
 
David ToddSenior DBACommented:
Hi,

One way of reducing the performance hit is to run profiler from your workstation against the server.

It also depends on how exactly your code is calling the procedures for what you see.

Can you confirm the version of sql you have is SQL 2005?

Thanks
  David
0
 
Easwaran ParamasivamAuthor Commented:
Is there any script such as given below to trace the Sp hits without running profiler? Because below query did not return result as expected. I would like to get correct query. Please do suggest.

SELECT session_id, num_writes, st.text AS statement_text
FROM sys.dm_exec_connections AS ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
ORDER BY num_writes DESC
0
 
Easwaran ParamasivamAuthor Commented:
Even the answers are not resolving my question they are informative. Hence giving points.
0
 
Vadim RappCommented:
I don't see why they are not resolving your question. Literally:

Q:  Whether it would be slowdown the environment?
A:  Not to any level that anyone would notice

Q: Whether it will affect the performance?
A: Not to any level that anyone would notice

Q: If so, what are the alternative to trace the SP hits without affecting the performance?
A: (since this is not so, this does not apply)

You seem to be sure that it will affect the performance. Is it because you actually tried to use Profiler and noticed impact on performance, or you are just sure that it must be the case?
0
 
Easwaran ParamasivamAuthor Commented:
In production it is not allowed to run  the profiler.
0
 
Easwaran ParamasivamAuthor Commented:
I mean that I do not have permission to run the profiler.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.