Solved

SQL Storedprocedure trace other than profiler

Posted on 2013-01-09
22
421 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now