March 31, 2016 at 9:46 am
I have a WMI alert setup in SQL that executes a job each time CPU utilization goes beyond 75%. The job executes a stored procedure that captures any running query and stores the information in a table, including the query_plan in a XML column.
How do I query the query_plan column for words like 'Missing Index" and "Table Scan"?
Thanks,
MC
March 31, 2016 at 9:52 am
If you just want to find rows where query_plan contains those terms, convert to varchar(max) and use LIKE, CHARINDEX or PATINDEX. If you want to know where in the XML the terms occur, you'll have to query the XML itself. I bet if you search for "shred query_plan", someone will have done it before.
John
April 1, 2016 at 6:37 am
I have an example on the blog here [/url]that pulls out Missing Index information.
"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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply