Here’s a newsflash: You may not thing so, but you are expensive. There’s no point in endless performance tuning. I love performance tuning a lot, and even more, I love challenges. But sometimes taking a query from the “good enough zone” down to “super-performance zone” will take a lot of time that is not justified business wise. Once you tune a query to the state it’s good enough, you better go back to surfing the web the other important tasks you have on your table.
Good enough can be measured in different ways, most obvious one is by time. When a user/developer/manager tells you something is running slow, ask how much time she wants it to run. You may find out that even though you think it can run under a second, she’s perfectly fine with 5 seconds. Other ways to measure can be the collision level with other processes (blocks), Tempdb usage, disk reads/writes, memory usage, etc..
The measure type will be derived from the resource that is most crucial to that specific scenario. If it is time, you need to work on duration. If the issue is concurrency, you may need to look at blocking (although it can also be other things), and so on.
The Good Enough Tuning Cycle