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 MoreExecution Plans
How STRING_SPLIT row estimates can affect performance
Recently someone brought a tweet to my attention regarding STRING_SPLIT and how performance of it was faster than table valued parameters (TVP). As you may have read in one of my prior posts TVP’s have issues related to lack of good row estimates. This can be fixed with a little extra work. STRING_SPLIT caught my […]
Read MoreIndex seeks and NULL values
A long time ago I heard or read that you couldn’t index a NULL value – I didn’t think that was correct. The other day I saw a missing index (as identified by the optimizer) where the first column was looking for a NULL value. I was working on optimizing a process that was pretty […]
Read MoreWhy do I have multiple plans for one query?
Have you ever been digging in your plan cache with either sys.dm_exec_query_stats or sys.dm_exec_procedure_stats and seen two identical queries with different execution plans? When I say identical I’m referring to having the same sql_handle. A sql_handle is generated based on the query text submitted to sql. If one character is different (even white space) it […]
Read MoreJoin Hints – Careful, They Force Order!
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 MoreHow to view percent done for a long executing query
If you’ve ever wondered when a long running query was going to complete there is now a way to get some visibility into it. Starting in SQL Server 2016 SP1 there is a lightweight query profiler that can be enabled with extended events or by enabling the 7412 trace flag. When you enable this trace […]
Read More