SQL Storedprocedure trace other than profiler
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alpesh Patel
Yes hit the performance but you can audit the limited events and take the benefit of it.
ASKER
@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.
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
Did you restart your server? or flush the cache?
ASKER
Each and every time I need to restart the server? In production I don't think so it would possible.
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.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
> 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.
not to a noticeable degree
> what are the alternative to trace the SP hits without affecting the performance?
debugging it.
ASKER
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.mo
ORDER BY num_writes DESC
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.mo
ORDER BY num_writes DESC
ASKER
Even the answers are not resolving my question they are informative. Hence giving points.
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?
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?
ASKER
In production it is not allowed to run the profiler.
ASKER
I mean that I do not have permission to run the profiler.