A common question I hear when discussing forcing of plans in query store is, “Why did a plan I previously forced quit using the forced plan?”. In most cases this is related to having a database in an Always On Availability Group. If the database IDs in the availability group differs across the servers, plan […]
Read MoreExecution Plans
Perform RTRIM and LTRIM in your code not in your queries
On occasion I’ll see queries trim off whitespace from both the input parameters as well as against the columns in the predicate (WHERE clause). If you’ve been reading my most recent posts I’ll start to sound like a broken record – applying functions to columns in a predicate will make a query unsargable (unable to […]
Read MorePerformance of parameterized LIKE queries with new parameter sniffing option
A while back I wrote a blog post on how parameterized queries with a LIKE in the predicate (WHERE) performed slower than the non-parameterized version. In a nutshell, the parameterized version of the query can’t tell for certain what parameters will be passed and does not generate an optimal plan. While attempting to set up […]
Read MoreEstimate rows Vs Estimated rows to be read what’s the difference
Starting with SQL Server 2016 sp1 execution plans from the cache now contain “Estimated Number Of Rows to be Read”. If you are interested in how this is different from “Estimated Rows Per Execution” and how this value can help identify a performance problem in your plan read on… How does it differ from Estimated […]
Read MoreFixing an implicit type conversion without a code change
If you have ever run into the problem of implicit type conversions you will be aware that the fix for it involves a code change or a table change. In a previous post I showed a common cause of such implicit conversions. In this post I will show a fix for this problem that does […]
Read MoreDealing With “Bad” Plans
SQL Server does the best it can to generate an efficient execution plan when a query is executed. This process can be expensive. To avoid the expense, SQL Server attempts to cache a plan once it is generated. This eliminates the need for subsequent calls to the same query to generate the plan. In an […]
Read MoreMaking Queries With Leading Wildcards Faster
Queries with leading wildcards generally are slow because they are not sargable. If by chance they pick up an index, they will usually full scan the index. Below is one example of a typical query that you might see when using a leading wildcard. SELECT * FROM CustomerAccount WHERE AccountNumber LIKE ‘%518370’ From looking at […]
Read MoreTuning Substring Query Without Changing the Query
Introduction The blog shows how to tune a query using substring in the WHERE clause. The tuning solution is to use indexing on a computed column. Performance Issue A General Ledger financial report execution in Financial system takes a long time finishing more than 4 hours. Performance Analysis The first step in the performance tuning […]
Read MoreHow to remove a hint from a query without code changes
On occasion I’ve been in situations where I’ve identified a query with a hint applied that was causing degraded performance. In the case of vendor code this can be a problem since removal or changes to hints would require a code change. This can also be a problem in shops where there are strict processes […]
Read MoreWhat happens with parameterized wildcards?
I’ve been curious of how the optimizer behaves when using parameterized SQL with a LIKE clause. Wildcard values that have a wildcard character in front generally do not seek on indexes. I was curious how the optimizer deals with this from a cached plan perspective – would placement of the wildcard character cause recompilations? I […]
Read More