If you have a query executing having unexpected slowness or timeouts but at the same time you see queries referencing statman, you might be running into an issue of synchronous statistics updates. By default SQL Server performs automatic statistics updates synchronously. This means when the threshold for requiring a statistics update is reached statistics are […]
Read MoreSQL Server tuning
Negative blocking session ids
On occasion while examining lock scenarios, I’ve seen a lead blocker with a negative session ID. After looking in the documentation for the blocking_session_id, it explained why I am seeing this (taken directly from Microsoft’s sys.dm_exec_requests documentation): -2 = The blocking resource is owned by an orphaned distributed transaction. -3 = The blocking resource is […]
Read MoreQuery store doesn’t work on read replicas
While attempting to find a query that ran on a read only replica, it got me wondering whether I needed to be querying the secondary, the primary, or the always on listener. Turns out the answer is none of the above. Query store only collects information from the primary. If you query any of these […]
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 MorePlan guides made easy
Plan guides are a useful tool for changing an execution plan when despite your best efforts the optimizer just isn’t coming up with the optimal plan. This becomes useful for situations where you can’t make code changes to a query or stored procedure. People are sometimes hesitant to use them due to the complexity in […]
Read MoreTuning Substring Query Without Changing the Query
Introduction The blog shows how to tune a query using substring in the WHERE clause. The tuning solution is to use indexing on a computed column. Performance Issue A General Ledger financial report execution in Financial system takes a long time finishing more than 4 hours. Performance Analysis The first step in the performance tuning […]
Read MoreHow To Fix Your Deadlocks
A common problem I see in systems is deadlocks. In a lot of cases the DBA isn’t totally aware of them because applications use retry logic for deadlocked transactions. I consider this to be a hack for a real problem that can be addressed in most cases. In this post I will explain what a […]
Read MoreAre VARCHAR or CHAR Hurting Your Performance Due To Implicit Conversions?
A common issue I’ve seen with code developed in ADO.Net is parameterized SQL performing full scans as a result of implicit conversions (despite appropriate indexing). This seems to occur most commonly if a database uses VARCHAR/CHAR datatypes as opposed to NVARCHAR/NCHAR datatypes. I’ll explain later in this posting why VARCHAR and CHAR datatypes seem to […]
Read More