Analyzing SQL Server Buffer Pool with TreeMaps in PowerShell

On Monday Chrissy LeMaire & I Did a session called “SQL Server Cmdlets and Community Involvement” for the PowerShell 10 Year Anniversary all-day event on Channel9 on MSDN. If you jump to the 18 minutes 30 second mark of that video you’ll see me showing how to look at the Buffer Pool of your SQL Server instance, first with the Out-GridView cmdlet, then I used a function from PowerShell MVP Boe Prox ( b | t ) called Out-SquarifiedTreeMap like so:

You’ll want to get the latest version of from Boe’s GitHub, and here’s the PowerShell code that calls SQL Server to get the Buffer Pool information (Direct Link to Gist):

Please give the code a whirl and let me know what you think.  I will post a longer write-up of how this works soon.

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

10 Responses

  1. I also saw it on GroupBy session. Thank you so much for the script.

    I also got the same error as dbamemories mentioned, I changed the -ShowLabel LabelProperty to -ShowLabel {“$($This.LabelProperty) “}. Similar to the tooltip, just as Jason’s note.

  2. Yep, I second having the issue. Also, script is for instances, not default. I had to remove Databases\$($_.db_name)\ to loop through default. BTW good presentation on GroupBy.

      1. Upvoted! Any word the ShowLabel issue? I am digging in today to see if I can resolve it. Just have to get a few other items taken care of.

        1. So the funny thing is, I can’t get it to give me that error message any more.
          :-/
          Can you try sending a different object/column down the pipeline to the -ShowLabel parameter?

          1. 🙁 nope, can you email me your versions that you show as working so I can do a comparison?

  3. I ran into something very similar, if not identical to your error message, very early on in my testing of this command. I will have a look at my old transcript files and see if I can spot where it stemmed from.

  4. Hi Aaron,
    I saw this on a GroupBy sesison and thought: “this is amazing”, and found this post of you, however for some reason I’m getting the following error when running the code:

    Out-SquarifiedTreeMap : Cannot process argument transformation on parameter ‘ShowLabel’. Cannot convert the “LabelProperty” value of type “System.String” to type “System.Management.Automation.ScriptBlock”.
    At line:32 char:143

    Can you point me to the right direction to fix the problem?

    1. You have to script out ShowLabel just like ToolTip. The examples do not show ShowLabel like it does for ToolTip. So just review ToolTip examples. Else Aaron has a slightly older version which he was so generously sent me so I can compare and see what was causing me the same problem.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner