Dave Ballantyne Blog

Ok, as myths go, its a pretty weak one.  In fact, it is true, this whitepaper explicitly states that.  But hand in hand with that statement goes another one, “Table variables will always estimate to one row”.  This is most definitely false,  if there are no statistics then sql server can, at times, default to its ‘guessing’…

OK, so that is quite a contradictory title, but unfortunately it is true.  There is a common misconception that the query with the highest percentage relative to batch is the worst performing.  Simply put, it is a lie, or more accurately we dont understand what these figures mean. Consider the two below simple queries:SELECT * FROM Person.BusinessEntity…

Whilst working at a client site, I hit upon one of those issues that you are not sure if that this is something entirely new or a bug or a gap in your knowledge. The client had a large query that needed optimizing.  The query itself looked pretty good, no udfs, UNION ALL were used…

As we are all aware, there are a number of traceflags.  Some documented, some semi-documented and some completely undocumented.  Here is one that is undocumented that Paul White(b|t) mentioned almost as an aside in one of his excellent blog posts. Much has been written about residual predicates and how a predicate can be pushed into a…

A long time ago I looked at some reasons as to why persisted computed columns ( and therefore indexes on those columns) are not used (Part1, Part2). Recently I was drawn into an issue where the same thing was happening, a persisted computed column had been created with an index, but neither were being used when expected.…

.. Is there something I should know,  sang Duran Duran many moons ago when I was young and before I knew that query optimization was even a thing.  But,  it is quite a handy little line to keep in mind when you are tuning queries,  giving SQL server a little bit more explicit information about your data…

I have been working with SQL server for more years than I really care to mention and like to think that I am now quite comfortable with the internals, specifically the Query Optimizer. Every so often though a new problem gets thrown into the mix, just to keep me on my toes ( or so…

Sometimes it does feel that a problem is a database problem until proven otherwise.  When this does occur I have had to put a lot of time and effort into demonstrating the hows and whys.  One of the easiest ( and most common ) scenarios of this is related to ASYNC_NETWORK_IO waits. Simply put ASYNC_NETWORK_IO…

The world of software engineering is full of acronyms and terms as long (and longer) than your arm. There are a few super important ones when developing code in an enterprise to ensure tech-debt (and the accompanying Human Debt ) does not build and hamstring the product in the mid to long term. Keep it…

What is Human Debt ? “HumanDebt™ is the equivalent to Technical Debt but for people. All of the initiatives, the projects, the intentions we (the organisation) had to do better by our employees, but we abandoned halfway. All of the missed opportunities to make their lives and their work easier and more joyful. All of…