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?
Best Answer
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.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.Hope this helps!