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.
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:
db_name(st.dbid) as database_name,
object_name(st.objectid) as name,
p.size_in_bytes / 1024 as size_in_kb,
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.