Thread by @Nick_Craver

We only cache a few things on the Question page for Stack Overflow (and all Q&A sites).

Loaded live (from SQL) are: your user (top bar), the question, answers, comments, users, your votes, flags (for mods).

We cache some sidebar: linked and related questions, for 5 minutes.

When you consider how many questions are on the network (over 27 million active), the chances of a user hitting the same question _on the same server_ (it's a local cache) within those 5 minutes is basically just the very hot or very new questions.

So is it worth caching at all?

Today, we're going to test.

I have a PR in that adds a setting to remove all caching and just query it live, as a single query with 2-3 result sets and Dapper reads them off. This means 1 SQL roundtrip for latency. I think it'll perform better, and eat far less cache:

I was going to cleanup the SQL after testing but...for @_Tomalak.

Note: the runtime is 1.1ms - even on Stack Overflow's DB this should be really, really fast. We have some filtered indexes there for this often-run use case. Even with caching, current queries run quite often.

@_Tomalak It's on for the network, here's when I turned it on for Super User - note that....you can't really tell anything happened. But I've disabled that sidebar caching. Same perf, no cache.

Time to enable on Stack Overflow next.

@_Tomalak Hey there Redis keyspace misses, where did you go?

No speed difference on Stack Overflow either, but note: we're not caching anymore. Same perf, no cache.

I'll get some memory over time graphs tomorrow. Redis is also under lower load (not that it cared or flinched) from all those wasted cache items and misses:

Early early graph: Gen 2 memory growth has arrested. Awesome.

Side observation: whenever we dig into metrics (you need before AND after to access your changes!), we usually find something unrelated.

Today, in looking at Stack Overflow timings I see just how much the hourly jobs we run at smacking around their neighboring requests. hmmmmm

It is now 1:1 with the most-run queries on SQL now (once per question render), but even on Stack Overflow the SQL Server is overall still at 5%.

This sidebar query is currently running about 27-30,000 times a minute, though we're well past peak traffic for today:

Turns out I missed some things - we're still pulling back more data than we render. More SQL optimization incoming.

So deployments went out yesterday and it was all good for 20 hours, until a SQL plan regression hard and fast this morning resulting in a Stack Overflow-specific outage.

The new plan was 7,146x more expensive, and the stability backoff was not working due to async. Double fail.

Specifically, it parameter sniffed for this question: stackoverflow.com/questions/8343… and resulted in an atrocious plan for the vast majority of questions. That slammed SQL at 100% CPU for several minutes causing a cascade of connection and thread pool failures upstream.