sp_blitzCache against one stored procedure

Question

Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.

I have a copy of the output of sp_BlitzCache taken during the week before we tuned the query, it is available here

https://drive.google.com/open?id=1oiAEl62ZT51qcCEhVzkg8oDA3HB-NCsv

The command run to generate this output was

exec sp_BlitzCache @StoredProcName = 'hs_UpdateShipmentPackagesAndWeightSp'

Few questions

1) I noticed that we have multiple rows for the same plan handle, why is that?

2) You'll notice that the highest cost query (1451) has a query text of the entire create procedure statement, what does that mean? Are the metrics for this row just for the creation of the stored procedure?

3) For the highest cost row, the one referenced above, it has a Min/Max grant of almost 2GB. Does that mean that when we run the create procedure statement it asked for 2gb of memory? Or is this plan being used when the proc is called ? I suppose this ties into the answer from question 2

4) Why in some cases we have Min/Max grant KB > 0 and Min/Max used grant kb > 0, but percent memory grant used is null ?

We are running SQL Server 2012.

Thanks, Kevin

Answer 1

Rephrased your questions a little:

Q: Why multiple rows for the same plan handle?

A: Because a plan can include multiple statements.

Q: Why does a proc have multiple lines in sp_BlitzCache?

Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.

Q: When I see "CREATE PROC" does that mean the proc was created?

No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.

Q: Why is the memory granted different from memory used?

Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.