Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Storedprocedure trace other than profiler

Posted on 2013-01-09
22
Medium Priority
?
441 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
22 Comments
 
LVL 42

Assisted Solution

by:Meir Rivkin
Meir Rivkin 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

926 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