sp_BlitzCache slow results on @SortOrder='cpu'

Question

I've been using sp_BlitzCache to investigate a few problems with a DB of mine for a few days. It's been a huge help, but just today it started acting a little odd and I'm curious if anyone has any ideas what's up.

Yesterday I ran many checks with @SortOrder as either 'reads' or 'CPU' and it worked fine, and always returned the results in about the same amount of time.

Today, 'reads' comes back in about the same amount of time as yesterday, but 'cpu' takes 10X or more as long. It gets to 'Checking for functions' and then sits there for a very long time. 'xpm' is also as fast as usual.

I'm using v5.7(20170901)

I realized my SQL session had been open for +24hours, and thought that the ##temp files were perhaps getting too big. But after I restarted the session and those tables went away, cpu sort order is still really slow. So that wasn't it.

Any thoughts on what would be causing this?

Answer 1

The most likely reason for this is a very large execution plan in the cache that doesn't show up in other sort orders. That can certainly hold things up when we hit the RelOp section of the XML.

You can use the @SkipAnalysis parameter, and set it to 1. This will skip all the XML analysis, but it will get you results. You'll want to use @ExpertMode to get the SqlHandle and QueryHash for the big plan that's holding things up.

EXEC sp_BlitzCache @ExpertMode = 1, @SkipAnalysis = 1

When you figure out which plan it is, you can use the @IgnoreSqlHandles or @IgnoreQueryHashes to skip over that plan in the cache in the future.

EXEC sp_BlitzCache @IgnoreQueryHashes ='', @IgnoreSqlHandles = ''

Hope this helps!