Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Storedprocedure trace other than profiler

Posted on 2013-01-09
22
Medium Priority
?
437 Views
Last Modified: 2013-01-24
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?
0
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
22 Comments
 
LVL 42

Assisted Solution

by:sedgwick
sedgwick earned 300 total points
ID: 38762215
that should answer your question (check first comment):
Using SQL Profiler on a database that's in production
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 300 total points
ID: 38762438
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38765855
Yes hit the performance but you can audit the limited events and take the benefit of it.
0
Stressed Out?

Watch some penguins on the livecam!

 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38766045
@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
 
LVL 25

Expert Comment

by:TempDBA
ID: 38766972
Did you restart your server? or flush the cache?
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38768409
Each and every time I need to restart the server? In production I don't think so it would possible.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38771106
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38777439
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
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 300 total points
ID: 38785734
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38785752
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38785777
> 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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38785789
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
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 300 total points
ID: 38785851
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38786029
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38786180
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
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 38814072
Even the answers are not resolving my question they are informative. Hence giving points.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38814177
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38814232
In production it is not allowed to run  the profiler.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 38814233
I mean that I do not have permission to run the profiler.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 38814354
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question