List of indexes used in an execution plan?

Question

Post reply

List of indexes used in an execution plan?

TheGreenShepherd

SSCertifiable

Points: 6979
More actions
February 23, 2016 at 10:45 am

#316249

I'm sure there's a way to query this, but I'd like to generate a query that outputs a list of all indexes projected to be used in a given execution plan. Anyone know how to do this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This does the opposite, but could be a good starting point:

https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

This will get you started. You just have to look at the right properties to only see the indexes

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),

QueryPlans AS

(

SELECT RelOp.pln.value(N'@PhysicalOp', N'varchar(50)') AS OperatorName,

RelOp.pln.value(N'@NodeId',N'integer') AS NodeId,

RelOp.pln.value(N'@EstimateCPU', N'decimal(10,9)') AS CPUCost,

RelOp.pln.value(N'@EstimateIO', N'decimal(10,9)') AS IOCost,

dest.text

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

CROSS APPLY deqp.query_plan.nodes(N'//RelOp') RelOp (pln)

)

SELECT qp.OperatorName,

qp.NodeId,

qp.CPUCost,

qp.IOCost,

qp.CPUCost + qp.IOCost AS EstimatedCost

FROM QueryPlans AS qp

WHERE qp.text = 'SELECT * FROM HumanResources.vEmployee AS ve;'

ORDER BY EstimatedCost DESC;

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Great script Grant.

Added to my briefcase.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 4

Thanks.

I should add though, that queries like this can be somewhat expensive. I wouldn't recommend running them under load on a production system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Actually, I never perform cache analysis in production.

I usually dump the whole cache to a table in my local sql server and start from there.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

spaghettidba (2/24/2016)
Actually, I never perform cache analysis in production.
I usually dump the whole cache to a table in my local sql server and start from there.

Good plan. And, you can always read a .SQLPLAN file if you want.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lowell SSC Guru Points: 323518 More actions · Answer 7

spaghettidba (2/24/2016)
Actually, I never perform cache analysis in production.
I usually dump the whole cache to a table in my local sql server and start from there.

excuse the neophyte question, but how do you grab the cache?

do you mean a typical sql that is grabbing from the dm views and putting the results into a temp/permanent table, or are you using some other magic?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

No magic at all. All I do is dump this...

SELECT *

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p

... to a a file with BCP.

Than I BCP in the file to my local sql server, to a regular table.

-- Gianluca Sartori