Now, no matter what value I pass it, the RECOMPILE hint is effectively ignored. I have the same plan every time (in my case, the London plan). Ah, but is the RECOMPILE hint ignored? In fact, no, it isn’t. If I capture the extended event sql_statement_recompile, I can see every single time I execute this procedure that it’s going through a full recompile… which is then tossed and the plan I chose is forced upon the optimizer. Every time.
This tells me that if you were using OPTION(RECOMPILE) to get around nasty parameter sniffing problems before, and if you go to Query Store, and if you force a particular plan to get around said nasty parameter sniffing problems, then you probably want to update the query to get rid of that OPTION(RECOMPILE) in order to prevent unnecessary plan compilation work.
A stored procedure with a single
@ProductIDparameter would allow us to add
WHERE ProductID = @ProductIDto both derived tables, which would make the query really fast. In my testing, it ran in under 100ms.
The problem is that this would require numerous code changes to the existing system. Many of the queries that rely on the view also use additional
WHEREconditions, meaning that a stored procedure is impractical.
Enter the table-valued user-defined function, or TVF.
I’m glad that the TVF worked out for him, but personally, I’m not a big fan of functions. In this case, though, it does the trick and leaves everyone happy.
Now, you have a meaningful list of wait statistics that will tell you exactly why, if not where, your server is running slow. Unfortunately, these waits still need to be interpreted. If you read further on Paul’s blog, you’ll see he has a number of waits and their causes documented. That’s your best bet to start understanding what’s happening on your system (although, I hear, Paul might be creating a more complete database of wait stats. I’ll update this blog post should that become available).
Wait stats are fantastic tools for figuring out your server resource limitations given the server’s historic query workload. They’re the first place to look when experiencing server pains.
Only want to use in specific scenarios. Basically, from various sources, I’ve always heard that these explicit query hints should typically be designed for edge cases or specific scenarios that are tested, documented, and known to scale appropriately. Anytime you introduce hints, you are taking control from the query analyzer and indicating you know best…. This might be the case, but test test test!
OPTION(RECOMPILE) is like dynamite: use it to blow up big problems, but understand beforehand what’s going to happen.
Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server 2008, keeps performance statistics for cached query plans, allowing us to find the queries and query plans that are most harming our system.
Glenn Berry’s fantastic set of diagnostic queries also includes a couple for finding CPU consumers.
Okay, this is getting out of hand. The query shouldn’t have to be this complicated.
Luckily I work with a guy named Chris. He’s amazing at what he does. He questions everything without being a nitpicker (there’s a difference). He read through the Mythbusters post and followed all the links in the comments. He asked whether gbn’s JFDI pattern wasn’t better here. So I implemented it just to see what that looked like:
I’ve ended up doing the same thing in a similar scenario. But as Aaron Bertrand notes in the comments, test your results because performance could end up being even worse than before.
The app’s plan was cached the day before. But wait a second! My assumption was that it had recompiled this morning due to the updated stats.
The conclusion I’d take here is that CROSS APPLY ought to be a tool you keep in the front of your toolbox and use when you must execute a function for each row of a set of tables. This is one of the T-SQL techniques that I never learned early in my career (it wasn’t available), and I haven’t used much outside of looking for execution plans, but it’s a join capability I will certainly look to use in the future.
I’m one of the biggest fans of the APPLY operator out there—my favorite talk is based on it, even. But in this case, I’m going to say that writing “CROSS APPLY” really didn’t do anything here—times are similar enough that I’d be suspicious that the database engine is doing the same thing both times.
In a similar vein to last week’s blog post… I heard an interesting comment recently. “Change that Column != 2 to a Column > 2 or Column < 2 combination, it can use indexes better.”
Sounds like something that clearly needs testing!
Not shockingly, this did nothing to make the query run faster or use fewer resources. There are ways to rewrite queries to improve performance while maintaining the same result structure (a common example being rewriting query using a cursor or WHILE loop to perform one set-based operation), but Gail’s point is vital: test your changes and make sure that if you’re saying it will perform better, that it actually perform better.
That is an enormous amount of data. What if you needed to sort that? What if you joined this to another table or view and a spool was required. What it it was a hash join and a memory grant was required? The demand that this seemingly innocuous statement placed on your server could be overwhelming.
The memory grant could create system variability that is very difficult to find. There is a thread on MSDN that I started which exposes what prompted this post. (The plan that was causing much of the problem is at this link.)
It’s important to keep in mind the good enough “big round figures” that SQL Server uses for row estimation when stats are unavailable (e.g., linked server to Hive or a CLR function like in the post). These estimates aren’t always correct, and there are edge cases like the one in the post in which the estimates will be radically wrong and begin to affect your server.