Skip to content

SQLGrease SQL Server Performance tips

Performance tuning tips from the SQLGrease team

  • Blog Home
  • About Me
  • Contact Me
  • SQL Grease Home Page

Query Tuning

Improving performance of your RIGHT query

April 29, 2022May 12, 2022 ~ franklinyamamoto ~ Leave a comment

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 More

Perform RTRIM and LTRIM in your code not in your queries

April 26, 2022April 26, 2022 ~ franklinyamamoto ~ Leave a comment

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 More

Performance of parameterized LIKE queries with new parameter sniffing option

April 25, 2022April 25, 2022 ~ franklinyamamoto ~ Leave a comment

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 More

Don’t use UPPER in WHERE unless you really need it

April 9, 2022April 10, 2022 ~ franklinyamamoto ~ Leave a comment

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 More

Performance implications of using DATEDIFF function in WHERE

April 4, 2022April 4, 2022 ~ franklinyamamoto ~ 1 Comment

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 More

Performance implications of using ISNULL vs IS NULL

April 3, 2022April 4, 2022 ~ franklinyamamoto ~ Leave a comment

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 More

Fixing an implicit type conversion without a code change

January 30, 2022January 30, 2022 ~ franklinyamamoto ~ Leave a comment

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 More

How to remove a hint from a query without code changes

December 29, 2019January 30, 2022 ~ franklinyamamoto ~ Leave a comment

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 More

Join Hints – Careful, They Force Order!

March 9, 2019January 30, 2022 ~ franklinyamamoto ~ 1 Comment

Recently I was looking at a query generating deadlocks as a result of a clustered index scan.  I saw that someone forced a LOOP JOIN on one of the offending queries.  At first glance it appeared as if the LOOP JOIN should have made an index seek more likely; however, after remembering a side effect […]

Read More

What are wait events and why are they important for performance analysis?

November 22, 2017August 2, 2018 ~ franklinyamamoto ~ Leave a comment

While trying to get to the bottom of a SQL Server performance issue, on many occasions I’ve seen engineering teams and in some cases DBA’s start focussing on perfmon counters to determine where a SQL performance issue is coming from.  Performance counters quite often will report symptoms of a performance issue and can many times […]

Read More

Posts navigation

Previous

See how SQLGrease can help fix your deadlocks

 

Subscribe to our mailing list

* indicates required

Follow @sqlgrease

Recent Posts

  • Filtered indexes and the new parameter sniffing option (spoiler alert it doesn’t help)
  • Negative blocking session ids
  • Query store doesn’t work on read replicas
  • Improving performance of your RIGHT query
  • Improving performance of your LEFT query
  • Perform RTRIM and LTRIM in your code not in your queries
  • Performance of parameterized LIKE queries with new parameter sniffing option
  • Don’t use UPPER in WHERE unless you really need it
  • Estimate rows Vs Estimated rows to be read what’s the difference
  • Performance implications of using DATEDIFF function in WHERE
  • Performance implications of using ISNULL vs IS NULL
  • Connecting to a read only replica with SSMS
  • Index seeks on date and CONVERT
  • Fixing an implicit type conversion without a code change
  • Dealing With “Bad” Plans
  • Making Queries With Leading Wildcards Faster
  • Creating a database per customer VS single database for all customers (SQL Server)
  • What’s filling up tempdb?
  • Plan guides made easy
  • Tuning Substring Query Without Changing the Query
~