Perf and Stability of the site | The Coffeehouse

Sampy
This will be the sixth time we have destroyed it and we have become exceedingly efficient at it

So I made a bunch of database optimizations last week to fix some CPU issues we were having on our database server. We got a DB trace that covered an hour of time and I fixed up every query that was running longer that 25 seconds (turns out it was just two). I deployed those fixes (one was a new index and the other was a change to a sproc) Wednesday and Thursday of last week.

I was back east at a wedding all weekend so I didn't use the site much. Orcs didn't send us any "THE SKY IS FALLING!!!" emails so at least I didn't break it. You guys notice a decrease in random failures and crashes at all? Is the site speedier?

If you're not seeing improvements, is there anything that is consistently slow? We have some app pools that like to overzealously recycle so that's probably the cause for random errors but if there's anything that you can repro let me know.

Fixing up SQL queries and staring at Query Plans is a strange kind of fun. Taking a query from an estimated cost of 178 to 3 is quite the ego booster (as I'm sure everyone on the team can tell you as I've bragged about it 5 or 6 times). Thanks to Inside Microsoft SQL Server 2005: T-SQL Querying for my newfound SQL tuning powers.

W3bbo
The Master of Baiters

Well I haven't seen any error message after posting, so I guess that did the trick.

Thing is though, why did it take over a year and a half to get fixed?

ScanIAm
On a scale of 1 to 10, people are stupid.

W3bbo wrote:
Well I haven't seen any error message after posting, so I guess that did the trick.

Nor I, but it was pretty sporadic. We'll have to wait until the next flamewar and see how it handles Smiley

W3bbo wrote:

~~Thing is though, why did it take over a year and a half to get fixed?~~
Thank you.

There, fixed that for ya.

Simo
With me it's a full-time job.

Sampy,

I had a DBA once who would email round the top20 misbehaving SPs once a month. The table was based of a column of cost * frequency (ie number of times sproc was executed in sample). Data collected over decent sample.

It was damn good at catching those bits of code/Select sprocs that have ended up being called far too frequently for their own good.

Shaming in front of your peers also being an effective tool for encouraging fixes. Or allowing the young whipper snapper on the team to step up and show what he's made of. Wink

mig
Punctuality is the virtue of the bored. - Evelyn Waugh

Is it just me or does Sampy seem to have a soft side for kids stories?

"oh noes!! the sky is falling!!" <- haven't heard that since grade school!

On the other hand, the site does seem to be more responsive for me today, but it could just be me imagining things Tongue Out

littleguru
<3 Seattle

Thanks Sampy Smiley

Angus
.

Thanks, the search seems to be working a lot better. Before I was trying to do a search for all posts by user "Angus", and it would throw an error every time. I just tried it now and it works fine.

Angus Higgins

Sampy
This will be the sixth time we have destroyed it and we have become exceedingly efficient at it

Angus, that was the worst behaving query by far. Almost every call timed out.

I'm working on a blog post describing exactly what I added to fix things up.

eddwo
Wheres my head at?

Thanks for that.

So we can start using accessing more per-user RSS feeds without them failing to update most of the time now?

littleguru
<3 Seattle

eddwo wrote:
Thanks for that.

So we can start using accessing more per-user RSS feeds without them failing to update most of the time now?

I wonder I should run some of the queries with the LINQ thing again. I wonder if we are faster now... *trying*

littleguru
<3 Seattle

Sampy is there a way to speed up RSS feeds? I get sometimes a very long time to wait until I get the response for a feed... Especially on thread feeds.

Edit: Now I even got a timeout.