Can I get SSMS to show me the Actual query costs in the Execution plan pane?
I don't know of a way to do this in the plan from Management Studio, but this is one of the many things the free SentryOne Plan Explorer will do for you when you generate an actual plan from within the tool - it includes all the runtime metrics per statement.
One good way to do this is with Profiler. Set up a "repro" of your problem proc on a dev or test box, ie a sample call to the proc with parameters. Then using Profiler, create a trace using either the TSQL_SPs template, or from a blank template, add the SP:StmtCompleted event. Add the Duration, Reads, Writes and CPU columns if not already available. Add a filter to the trace on your SPID (which you should know from Management Studio). You might also add a filter to Duration ( eg greater than 1000 = greater than 1 second ).
You can either run the trace in Profiler although there is overhead (do NOT do this on a production box) or export the definition and create a server side trace. The Profiler overhead is not so much of a big deal on a dedicated dev or test box.
Run the proc and let it complete. You may also which to collect the Actual Execution plan at this point.
Stop your trace and open the file, and you should see a line by line breakdown of your proc, including timings for each step. I find this more useful than the plan for identifying bottlenecks although the plan will come in handy when looking at the relevant sections to tune.
HTH
You could also use the sys.dm_exec_procedure_stats and sys.dm_exec_query_stats dynamic management views. The first of those gives information about the procedure as a whole; the second can be used to break out each query in the procedure. An example is shown below:
USE AdventureWorks;
GO
CREATE PROCEDURE dbo.Test
@NameLike nvarchar(50)
AS
BEGIN
SELECT
ProductCount = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
th.ProductID = p.ProductID
WHERE
p.Name LIKE @NameLike;
SELECT
pc.Name,
ProductCount = COUNT_BIG(*)
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS ps ON
ps.ProductSubcategoryID = p.ProductSubcategoryID
JOIN Production.ProductCategory AS pc ON
pc.ProductCategoryID = ps.ProductCategoryID
WHERE
p.Name LIKE @NameLike
GROUP BY
pc.Name
ORDER BY
pc.Name;
END;
GO
EXECUTE dbo.Test @NameLike = N'A%';
EXECUTE dbo.Test @NameLike = N'F%';
Procedure statistics:
SELECT
deps.last_execution_time,
deps.last_worker_time,
deps.last_physical_reads,
deps.last_logical_writes,
deps.last_logical_reads,
deps.last_elapsed_time
FROM sys.dm_exec_procedure_stats AS deps
WHERE
deps.database_id = DB_ID()
AND deps.[object_id] = OBJECT_ID(N'dbo.Test', N'P');
Queries within the procedure:
SELECT
query.the_text,
deqs.last_execution_time,
deqs.last_worker_time,
deqs.last_physical_reads,
deqs.last_logical_writes,
deqs.last_logical_reads,
deqs.last_clr_time,
deqs.last_elapsed_time,
deqs.last_rows -- note: Only present from 2008 R2 onwards
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.[sql_handle]) AS dest
CROSS APPLY
(
VALUES
(
SUBSTRING
(
dest.[text],
deqs.statement_start_offset / 2 + 1,
(ISNULL(NULLIF(deqs.statement_end_offset, -1), DATALENGTH(dest.[text])) - deqs.statement_start_offset) / 2 + 1
)
)
) AS query (the_text)
WHERE
deqs.[sql_handle] IN
(
SELECT
deps.[sql_handle]
FROM sys.dm_exec_procedure_stats AS deps
WHERE
deps.database_id = DB_ID()
AND deps.[object_id] = OBJECT_ID(N'dbo.Test', N'P')
);