SET Twisted mind on

I've had another inspiration about the execution plan. I think this is delibirately left out simply because some of the greatest DBAs all had a wack at it. Let's assume at least Brad and Steve if not 5-6 more of the same caliber. My best guess at this point is that this info is <considered> completely useless or they simply want us to consider other options because that path's been beaten to death already. Missing indexes are fine but there's no way in h*ll they have not considered that before. So while this seems like a viable option, I'm pretty sure they expect something completely different than that.

With that in mind >> Do you guys really need to track those clicks real time? Are you opened to creating either a logging table with identity bigint, article_key and just inserting to that table with a single update every 15 minutes to the base table? Or god forbid, track that in aspx in the session variables and flush to the db once a minute (or whatever makes sense for your needs)

AnalysisLog.Data is varchar(50). This is a hell of a long key for something that gets that much activity. Any opened-ess to change that key to identity as int (4 billions articles should get you a long way). I have no idea of the real cost of doing this as I don't know how much code change this would entail nor the actual cost of adding FKs to point to that and possibly new indexes to support that operation.

Same thing for PostName nvarchar(256).

Are you guys using compression, if so page or row? I checked out on SM but I couldn't find the sql version info anywhere (sorry if I missed it and also if it's not available). The "standard" is that you get more CPU and less disk activity. You seem to have a lot of room on the cpus, so maybe that could be a nice hit to take there and save on disks / ram (even if cache hit ratio is 100%). All in all maybe a 0 to 5% gain, but still something worth trying.

Do you have a job that runs every hour around H:20-23? You get a 5X spike in page splits around that time. I would guess something like the top poster and other leaderboads being updated but I can't tell for sure. Log shipping, replication?

Do you do anything special arount 11:00 to 17:00 on sqlservercentral db? It gets a hell of a spike on log flushes and transactions... or is it just your "normal" business hours.

Any way we can get a trace for replay so we can prove our ideas make a real impact or are we supposed to guess all the way to the end of the contest?