cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Level 7

SQL Plan Warnings

So I've been playing around with the SolarWinds SQL Plan Warnings free tool. It is straight-forward in use and quick. I was surprised by how quick it is and its ability to pinpoint the exact SQL in quesiton, which leads me to my own question:

Where is the prodecure name for problemed query?  Is there something I am missing, or is this a tool designed to get me to have to pay for an even bigger tool to make it of any use?

I hope I'm missing something, because as it stands the SPW Tool isn't much of a "tool" at all, and feels more like a ploy to get me to spend money (think Idera) which then dampens my opinion of SolarWinds. 

Tags (1)
0 Kudos
1 Reply
Level 17

Re: SQL Plan Warnings

Hey @dsb, thanks for the question. Sorry for such a delayed response.

I believe you are asking for the name of the stored procedure, is that correct?

SPW is focusing on statements in the plan cache, as you have seen. The trouble with showing stored procedure names is that the same T-SQL statement could be used by more than one procedure. It would be difficult to unwind this data by mining the query DMVs after execution. You would really need to be logging those details prior.

Here's a piece of code you could use to return the procedures listed in the plan cache:

SELECT
db_name(st.dbid) as database_name,
object_name(st.objectid) as name,
p.size_in_bytes / 1024 as size_in_kb,
p.usecounts, st.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) st
WHERE p.objtype = 'proc'
AND st.dbid = 'dbname'

You could use that query and then examine the text return, looking for specific keywords that match the statements found in SPW.

HTH

0 Kudos