I got tired of trying to find my script to check this, or search through the monitoring tool that we use to find it. I wanted a lightweight tool that I could use to get that one stored procedure that I was being asked to look into.
Either it's causing issues because the compiled value in the cache is rough on the optimizer, or the entire thing needs to be looked at for optimization. (I try to peruse the cache frequently as you can learn a lot just by seeing what is causing your bottlenecks this way. )
The idea is to be able to search the proc cache for a single stored procedure and return all query plans associated to it. Say you're trying to find the difference between the slowness in the app vs the speedy response from when you exec the code in SSMS. (Seriously, read that link.)
It's not the prettiest, but it's probably the script I use the most when working with a developer about on a nasty procedure. Specifically, they can use it and send me the Query Plan as well.
Here is sp_FindQueryPlan
I have an RPG database that I developed for learning a bit. It's a bit old but comes in handy for situations like this. Here is the example procedure I will be using:
USE [AdventureTime] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID('dbo.GetMonsterActions') IS NULL EXEC ('CREATE PROCEDURE dbo.GetMonsterActions AS RETURN 0'); GO ALTER PROCEDURE [dbo].[GetMonsterActions] @MonsterId NVARCHAR(128) AS SELECT m.[Name] ,ma.ActionName ,ma.Attack ,ma.ActionText FROM dbo.Monsters m INNER JOIN dbo.MonsterActions ma ON ma.MonsterID = m.MonsterID WHERE m.MonsterID = @MonsterId GO
This will create the sample procedure. It's quite simple, so really anything that's going to store in the cache is going to work.
So once you have made, or found, the test procedure you would like to use. Go ahead and fire it to make sure it's in the cache.
Here are my results, the Demogorgon.
The query plan:
Now let's try a test run of sp_FindQueryPlan:
I will say this, it's a small database. About 17mb. So my performance when searching the cache is basically a non-issue. But it can take some time to search a large cache and depending on the server it can make it take a hit. So try not to use the wide open search if you can.
From this point you can crack open the plan XML and then open Plan Explorer or just take a quick peek at what's going on in the procedure. I find the real benefit is being able to work with the developers quickly, often times they will report a procedure being slow and I can pull it up and see what the query plan looks like in a few seconds and give more detailed answers.