All subsequent executions of that same query will go to the query cache to reuse that same initial query plan — this saves SQL Server time from having to regenerate a new query plan.
Note: A query with different values passed as parameters still counts as the “same query” in the eyes of SQL Server.
In the case of the examples above, the first time the query was executed was with the parameter for “Costa Rica”. Remember when I said this dataset was heavily skewed? Let’s look at some counts:
Check it out for a clear depiction of the topic. One solution that Bert doesn’t have but I will use sometimes is to create local variables in a procedure and set their values equal to input parameters. That way, the optimizer doesn’t have an assumption as to the value of the local variable. But there are several ways to get around this when it’s an issue.
Ideally, you should remove only what’s absolutely necessary. Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated. Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.
For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.
There are reasons to run these commands, but ideally, you should be as precise as possible in clearing plans out of the cache.
Daniel Hutmacher shows several ways to combine data from multiple sources, prioritized by source:
For the sake of simplicity, I’ll assume that our example data has a clustered index on the “primary key” that we want to use to determine which rows have already been included in the set – in our example, the primary key is (FirstName, LastName). The data I’m using is a fictional example, but here’s the jist:
- #Employees has about 33 000 rows.
- #Customers has about 44 000 rows.
- #Passengers has about 500 000 rows.
The data is constructed in a way that these queries should return 530 000 rows, so we’re looking at some overlap but far from totally overlapping rows.
Example #492,806 that T-SQL is not a true 4th Generation Language, that how you write the query can greatly matter for performance.
Jovan Popovic shows off some automatic tuning functionality in SQL Server 2017:
Plan change regression happens when SQL Database changes a plan for some T-SQL query, and the new plan has the worse performance than the previous one. SQL Server 2017 has Automatic Tuning feature that enables you to easily find plan change regressions and fix them. In this post you will see the demo script that you can use to cause plan change regression and manually fix it using new sys.dm_db_tuning_recommendations view.
If you are not familiar with plan regressions and new tuning recommendations in SQL Server 2017, I would recommend to read these two posts:
- What is plan regression in SQL Server?
- How to find query plan choice regressions with SQL Server 2017 CTP2
This would be enough to understand steps in this demo.
Our experience with plan regression recommendations has been uniformly positive so far. Those tests have been in dev and QA environments, but so far, there hasn’t been a terrible recommendation.
Kendra Little explains that SQL Server will cache parameter values for invalid statements:
The first time that dbo.ReviewFlags is executed after the database comes online, it’s with an invalid parameter, like this:
- EXEC dbo.ReviewFlags @Flag = null;
- GO
This is caught by the IF block, hits the RAISERROR, and goes down to the THROW block, and the output is:
- Msg 50000, Level 11, State 1, Procedure ReviewFlags, Line 8 [Batch Start Line 70]
- @Flag must be a value between 1 and 5
But even though SQL Server didn’t execute the SELECT statement, it still compiled it. And it also cached the plan.
Read on to understand the trouble this can cause, as well as a few ways of solving the problem. This is a special case of parameter sniffing problems, but the solutions are the same as in the general case.
The first release was published at the end of June 2017 and provides a background collection of query execution statistics in a database of your choice. The v1.0 release supports SQL Server from 2008 to 2014 and all editions (Express, Standard, Enterprise). There is also a PowerShell installer for those that are so inclined, which will grab the code from GitHub and install OQS into a specified database.
There is also a custom report which can be installed in SSMS (2016 and 2017), which will display information that OQS has collected.
This is a really cool community solution if you’re stuck on an older version of SQL Server for a while.
Let’s talk about the sequence of table joins in detail. It is very important to understand that the possible number of table joins grows exponentially, not linearly. Fox example, there are only 2 possible methods to join 2 tables, and the number can reach 12 methods for 3 tables. Different join sequences can have different query cost, and SQL Server optimizer must select the most optimal method. But when the number of tables is high, it becomes a resource-intensive task. If SQL Server begins going over all possible variants, such query may never be executed. That is why, SQL Server never does it and always looks for a quite good plan, not the best plan. SQL Server always tries to reach compromise between execution time and plan quality.
There are ways you can help the optimizer, and one of my favorite query tuning books was all about table selection.
Kellyn Pot’Vin-Gorman shows an example of query hints in Oracle and in SQL Server:
Oracle hints were quite common during the infancy of the Oracle Cost Based Optimizer, (CBO). It could be frustrating for a database administrator who was accustomed to the Rules Based Optimizer, (rules, people! If there’s an index, use it!) to give up control of performance to a feature that simply wasn’t taking the shortest route to the results. As time passed from Oracle 9i to 10g, we harnessed hints less, trusting the CBO and by Oracle 11g, it started to be frowned upon unless you had a very strong use case for hinting. I was in the latter scenario, as my first Oracle 11g database environment required not just new data, but a new database weekly and a requirement for me to guarantee performance. I knew pretty much every optimal plan for every SQL statement in the systems and it was my responsibility to make sure each new database chose the most optimal plan. I had incorporated complex hints, (and then profiles as we upgraded…)
With the introduction of database version Oracle 12c, it became a sought after skill to use hints effectively again, as many new optimizer features, (often with the words “dynamic” or “automated” in them) started to impact performance beyond what was outside the allowable.
Read on for a nearly-equivalent query in the two database systems.
Let’s look at one query with a few variations.
SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total] FROM dbo.t1 AS t WHERE t.Id > 0 AND t.Id < 3;The plan for it is alright. It’s fairly straightforward and the query finishes in about 170ms.
We can see from the graphical execution plan that it’s been Simple Parameterized. SQL Server does this to make plan caching more efficient.
Check out the entire post.
Jovan Popovic points out the performance difference in using the WITH clause in an OPENJSON query:
Here are results of the queries:
SQL Server Execution Times: CPU time = 656 ms, elapsed time = 651 ms. SQL Server Execution Times: CPU time = 204 ms, elapsed time = 197 ms.As you can see, WITH clause specify that OPENJSON should immediately return properties from the JSON array without second parsing. Performance of the queries might be increased 3 times if you avoid double parsing.
That’s a pretty big difference when you specify the relevant data model elements.
Kevin Feasel
2017-08-10
Query Tuning
No Comment