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 MoreASYNC_NETWORK_IO Wait, What is it? What causes it?
You may occasionally see ASYNC_NETWORK_IO waits surface in your database . This wait can be caused by an unhealthy network connection; however, more often than not I see it caused for different reasons. What are causes of ASYNC_NETWORK_IO wait? There’s a few common scenarios I see as a cause for this. Queries retrieving large result […]
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 MoreIndexes for dummies
This post is geared towards explaining why indexes are necessary in layman’s terms. My goal is for this to be understandable to somebody that doesn’t necessarily have a database background. The Phonebook Example Finding a phonebook on my front doorstep is a nuisance these days. I’ve quit picking them up over the years to try […]
Read MoreDiagnosing: The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time
Have you ever had the joy of receiving this error message? The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running […]
Read MoreWhat parameters were used when compiling your execution plan?
In this post I will show you a simple way to see what parameters were used when an execution plan was compiled. This can be valuable when trying to diagnose parameter sniffing problems. Our Example Plan The following is a screenshot of our example plan. You can see the query is parameterized. How do I […]
Read MoreDoes rebuilding an index cause blocking?
Yes. I’m always surprised when people perform index operations such as this as well as dropping and creating of indexes during the middle of the day. These operations have an impact on performance. In this post I’ll show you why index operations are blocking operations. I will also discuss an option (only for Enterprise Edition […]
Read MoreDoes updating statistics cause blocking?
Earlier today I had a conversation with a colleague who thought statistics updates could cause blocking. I have never seen this before, but since I consider this colleague to be pretty competent I second guessed myself. I decided to put together a post as soon as I got home to verify this. To start with […]
Read MoreHave you ever used sp_create_plan_guide_from_handle?
On occasion you may have a query that starts picking up a bad plan as a result of an execution plan regression. This can be for a variety of reasons (stats, parameter sniffing, etc). If this occurs on a regular basis and you don’t have the luxury of query store, you can use sp_create_plan_guide_from_handle in […]
Read More