I am using SQL Server 2012 and was wondering how to profile stored procedures
For instance can profiler capture each individual SQL statement in a stored procedure, what it is, and how long it takes to run etc?
I am trying to diagnose merge replication stored procedures, and this must be captured a part of a full run of the merge agent. It doesn't seem possible to grab the stored procedure with performance issues and run it again because at that point it is not slow.
Kevin's answer describes what events to capture in SQL Trace/SQL Profiler. To expand on that answer a bit - SP:StmtCompleted will show you each statement within a stored procedure being completed, as it sounds.
Also if you are on a busy system and trying to diagnose a performance issue you should be careful with SQL Profiler. SQL Profiler is much slower than tracing to a file or using Extended Events. This blog post by Jonathan Kehayias shows about a 90% overhead on a system's performance from using SQL Profiler and about a 10% overhead from tracing to file. Less for Extended Events. That is why it is typically recommended to not run SQL Profiler itself whil
While this information is available through Extended Events, I would suggest still using SQL Trace (the technology behind SQL Profiler) but tracing to a file instead (if you wanted to invest in learning and using Extended Events this would be the way to go, in a future version of SQL Server SQL Trace will be gone and all we'll have is Extended Events) . I would also suggest you filter through the Column Filters button out as much background noise as possible to make sure you are only capturing what is necessary. You can setup your trace with the Profiler tool using the steps Kevin describes in his good answer and then add a filter from that same GUI. Then you can export the trace as a script and run that script on SQL Server tracing to a file on a folder that doesn't contain database or transaction log files. To export, you would simply setup your trace, run it for a few seconds to just make sure you are capturing what you want, stop it and then go to the menu bar and File -> Export -> Script Trace Definition and save the file. Then open that file in a new query window on the server you intend to trace. You can see more about the options and definitions of this script you created by looking at the help articles for the various stored procedures used in that script you just created by starting here.
If you have time and want to learn, you can also read some articles on Extended Events and see how to capture the information as well. Jonathan Kehayias is a great resource for blog posts when you are ready to start there.
You can capture the individual statements in a stored procedure through SQL Server Profiler. To do this, in your Events Selection tab, click the "Show all events" checkbox. Then, scroll down to the Stored Procedures category and check the box next to SP:StmtCompleted. If you also have the SQL:BatchStarted and SQL:BatchCompleted events selected, you can get a beginning-to-end picture of a stored procedure run, tying it all together by SPID.
It might also help to look at the plan cache to see if you can get a query plan for your slowly-performing procedure. You could start with something like this:
SELECT
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM
sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
cp.objtype = 'Proc'
AND OBJECT_NAME(st.objectid,st.dbid) = 'YourStoredProcedure';
