Solved

SQL Storedprocedure trace other than profiler

Posted on 2013-01-09
22
425 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
 
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 38

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now