execution-planperformanceprofilershowplansql server
Is there any option to show "Include Actual Execution Plan" plus query cost and recommended indexes in SQL Profiler?
I see Showplan XML, but it shows only the execution plan without recommended indexes :/
It's working perfect when I run the query from SQL Server Management Studio and Include Actual Execution Plan.
I can't say for certain because I've never dealt with that large a stored procedure but you could try this:
SELECT query_plan
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE object_id = object_id('procedure_name')
Then save the output to a text file.
The Query Optimizer in SQL Server can make multiple missing index suggestions for individual queries. However the part of SQL Server Management Studio (SSMS) which displays execution plans visually only displays a single missing index suggestion; it looks like a bug. However these multiple index suggestions are visible in SSMS, eg in the properties for the first operator (eg SELECT), press F4.
As you've also noted, multiple suggestions are visible in the plan XML, or third-party tools like SQL Sentry Plan Explorer. Multiple suggestions will also be visible in the missing index DMVs (eg sys.dm_db_missing_index_details )
This simple query resulted in multiple suggestions for me:
USE tempdb
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL DROP TABLE dbo.t1
CREATE TABLE dbo.t1
(
rowId INT IDENTITY
)
GO
IF OBJECT_ID('dbo.t2') IS NOT NULL DROP TABLE dbo.t2
CREATE TABLE dbo.t2
(
rowId INT IDENTITY
)
GO
INSERT INTO dbo.t1 DEFAULT VALUES
GO 100000
INSERT INTO dbo.t2 DEFAULT VALUES
GO 100000
SELECT *
FROM dbo.t1 t1
INNER JOIN dbo.t2 t2 ON t1.rowId = t2.rowId
WHERE t2.rowId = 999;
HTH
Best Answer
FWIW, the missing index info is there is is just Profiler that doesn't bother to show it. If you dig into the underlying XML you will see it. Ans since Profiler has been deprecated and MS haven't done any work on it since 2010, we can't expect any change here. If you use Extended Events, the "new" (if we consider 2008 being new) trace engine, then you will find that the missing index is there as well. But the GUI for XE doesn't show the graphical representation for the plan in the first place, only the raw XML. I suggest you follow Erik's recommendation, most probably a more productive path.