December 11, 2013 at 10:49 am
One of my production databases has in the exec plan cache a statement that needs to be modified so that it will use an index; the principal developer using that database does not know where the code originates.
Is there a query (or some other way) to determine further information for this plan other than what's available in sys.dm_exec_cached_plans, sys.dm_exec_sql_text, and sys.dm_exec_query_plan?
Thanks,
~ Jeff
December 12, 2013 at 12:27 am
What further information do you need?
I think that everything you need is in those DMV's
December 12, 2013 at 4:34 am
You can look to the dynamic management function sys.dm_exec_plan_attributes. It will show the user_id that created the plan among other things. You'll just have to pass it the plan_handle.
"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
December 12, 2013 at 5:09 am
Grant,
Thanks for the info, I wasn't aware of that function (yes, I realize I should spend the time to look into all the DMV/DMF's).
BTW, whenever I'm confronted by an issue regarding performance / optimization, I look to your blog and/or book as the definitive source, and quote you quite often to my colleagues (they're excellent SQL programmers, but understand little about how to code for best performance).
Thanks again,
~ Jeff
December 12, 2013 at 5:40 am
Wow! Thank you. Very high praise. Happy I can help.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply