Category: Query Tuning

Brent Ozar recommends a strategy:

When I’m tuning queries, the normal answer is to make the query perform better – either via changing the T-SQL, adding hints, or adding indexes so that the data’s better prepared for the query.

However, sometimes when I’m looking at the output of sp_BlitzCache, I scroll across to the Average Rows column and double-check that the query’s actually returning a reasonable number of rows out in the wild.

Click through for some horror stories and the benefits of pagination. Brent’s last case was a scenario in which people received 1000 rows and nobody ever complained. I’ve worked in systems where the customers did want and need every row. And in those cases, pagination still works. Because 85-90% of customers only need the first page or two.

Now, I wish that OFFSET/FETCH actually performed well. Sadly, it generally is a dog when you work with larger datasets and get past the first few pages. That’s because if you want to show rows 10,000-10,050, you first need to scan from rows 1-9999 and throw that data away, then grab the 50 rows you need. There are some clever sorting tricks you can use to reduce the pain level on repeated fetch operations, and I show one of them in this demo script (scroll down to the OFFSET/FETCH demo, number 6 in the list). The prior key approach is, in my experience, the best performer but it assumes you only move in one direction and don’t allow users to select arbitrary page numbers or go straight to the end.

1 Comment

Brent Ozar is down with OPP(O):

SQL Server 2025 improved PSPO to handle multiple predicates that might have parameter sensitivity, and that’s great! I love it when Microsoft ships a v1 feature, and then gradually iterates over to make it better. Adaptive Memory Grants were a similar investment that got improved over time, and today they’re fantastic.

SQL Server 2025 introduces another feature to mitigate parameter sniffing problems: Optional Parameter Plan Optimization (OPPO). It ain’t perfect today – in fact, it’s pretty doggone limited, like PSPO was when it first shipped, but I have hopes that SQL Server vNext will make it actually usable. Let’s discuss what we’ve got today first.

Okay, I really had to stretch the truth to make my lead-in work, but I’m too proud of it to change anything. Click through to see where OPPO is today. Even with just one optional parameter working well, there is still a class of stored procedures that this can help: the “get by one ID, or get me all of them” type.

Comments closed