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 MoreHow to fix RESOURCE_SEMAPHORE waits
RESOURCE_SEMAPHORE waits occur when a query is unable to get enough memory to execute. In this post I will describe how to see they are occurring. I will also provide tips on ways to help reduce or eliminate them. How do I see them occurring? You can detect they are occurring by checking wait stats. […]
Read MoreHave you ever wondered what SQL generated that FETCH or FETCH API_CURSOR statement?
Have you ever seen a FETCH statement show up when monitoring via activity monitor, sysprocesses, dm_exec_requests, or name your monitoring tool of choice? These surface when a process is programatically fetching through a cursor. Most of the time the fetch statement isn’t of interest when performance tuning. Instead it is of more use to see […]
Read MoreSitecore on SQL Server? Something you should be aware of…
Sitecore is a .Net based content management system. It uses SQL Server for its backend database. I recently got asked to help investigate a performance issue on a new implementation that was being performance tested. In this post I’ll describe the behavior causing the performance issue, and how through proper configuration this could be […]
Read MoreTable valued parameters and performance
Table valued parameters (TVP) are a great feature that solves the problem of passing a table of values into a stored procedure. In the past problems like this would get solved by complicated approaches such as passing and parsing XML, or passing long streams of dynamically generated SQL. There is a caveat you should be aware […]
Read MoreWhat is a query_hash or query_plan_hash and why is it useful?
If you have ever queried sys.dm_exec_requests or sys.dm_exec_query_stats you may have noticed the query_hash and query_plan_hash columns. If you’re familiar with a sql_handle and plan_handle the query_hash and query_plan_hash should be easy to understand. A query_hash is a computed value that points to a query irrespective of literal values. Similarly a query_plan_hash is a computed […]
Read More