Fixing P1 Queries

Steve asks if we should focus on the slowest queries or tackle other issues in our systems. Or if we should even have to make these decisions.

Implicit Conversions Cripple SQL Server

Learn about implicit conversions, which can dramatically affect the performance of your workload.

Why Your Index Isn't Being Used? - Reading Execution Plans to Find the Real Culprit

Built the index, but SQL Server still scans the whole table? Learn how to read execution plans and find exactly why your index is being ignored.

There Are Days When I Feel Like Giving Up on the Plan Cache and Query Store.

In theory, SQL Server performance monitoring is pretty simple: 1. Review the server’s top wait types, 2. Find the queries causing those wait types, 3. Fix those queries, or improve the way the server reacts to them (indexes, settings, etc.). But in practice, step 2 is awful because:

How Multi-Column Statistics Work

The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don’t already exist), and uses those two statistics together – but they’re not really correlated.

Every Database Has Problems

The magic way to get better performance from your system is to write better code from the start.

SQL Server Long-Running Query Detection: How to Find and Monitor Slow Queries

Monitoring and alerting can be overwhelming tasks, especially for those new to the world of managing production data. One common challenge for any data professional is the identification and management of queries that run for longer than they should.

How Filters and Sorting can Derail SQL Pagination Performance

In my previous tip, Pagination Performance in SQL Server, I showed how to make SQL pagination more predictable – turning O(n) into O(1). I materialized and cached row numbers to page through instead of calculating them on every request. It wasn’t the whole story, though; real pagination queries rarely get to sort without filtering. Users always want more control, and filtering can threaten that predictability.

The Query Optimizer and Page Density

Optimize SQL Server Query without Changing the Query

Sometimes we cannot improve query performance because we don’t have control over the code. Consider a query that is generated by Entity Framework (EF) from the application and you do not have access to the source code. The main question is how you can improve SQL Server query optimization for a poorly performing query?