I always get a kick out of when a query needs to perform a look up on the last 4 digits of a column because it usually ends up being something radioactive like a credit card number or a social security number. Either way, trying to do a lookup by the RIGHT most characters of […]
Read MoreImproving performance of your LEFT query
If you have queries that search for the first X number of characters in a column you might be using a LEFT. Since LEFT is a function it makes the query predicate (WHERE) unsargable. In other words an index cannot be efficiently used. The demo query In this query we are searching for the LEFT […]
Read MorePerform 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 MoreDon’t use UPPER in WHERE unless you really need it
In SQL Server when I run into a problematic query doing an UPPER on a column in the predicate (WHERE clause), I check the collation scheme of the database. If you have a Case Insensitive (CI) collation scheme you don’t need to use UPPER. As with most functions applied to a column, it is unsargable […]
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 MorePerformance implications of using DATEDIFF function in WHERE
These might be narrow cases I’m describing, but as I’ve seen it happen often enough I figured it is worth writing a post about. In this post I’ll describe two different scenarios where the DATEDIFF function caused a query to perform poorly due to DATEDIFF not being sargable. The first scenario I’ll be describing is […]
Read MorePerformance implications of using ISNULL vs IS NULL
Quite often I see queries that are not sargable due to the use of the ISNULL function in the query’s predicate (WHERE clause). If you’re not familiar with sargable it means whether or not a condition in the predicate can take advantage of an index. I won’t dive too deep into sargability here, but I’ll […]
Read MoreConnecting to a read only replica with SSMS
If you are using always on availability groups and need to connect to a read only replica, you might get the following error message in SSMS: Msg 978, Level 14, State 1, Line 1 The target database (‘Database’) is in an availability group and is currently accessible for connections when the application intent is set […]
Read MoreIndex seeks on date and CONVERT
Using functions on columns typically prevents SQL Server from being able to efficiently use an index with a seek (sargability). When it comes to DATE and DATETIME data types, there are some exceptions. A common use case I see is where a column contains a DATETIME data type; however, a query needs to look at […]
Read More