Solved

SQL Storedprocedure trace other than profiler

Posted on 2013-01-09
22
430 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:sedgwick
sedgwick earned 75 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 75 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 75 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 75 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

860 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