Best Thing I Learned at PASS Summit

, 2009-06-19

If you haven't heard, PASS is running a contest where you could win conference registration to this year's PASS Community Summit or, if you have that covered, 4 nights coverage of hotel costs. All you have to do is enter by July 1. Check the link for details. Here's my best thing I learned, which was from the 2004 PASS Community Summit:

It was a packed, standing-room only presentation. The late Ken Henderson was talking on how SQL Server allocated memory, and was talking about the MemToLeave region. He discussed how large execution plans can tap memory from this space and how any such allocations require contiguous memory. So if MemToLeave is fragmented and you need a large amount of memory, a contiguous block may not be available, generating an error. I slotted away this knowledge, never thinking I would use it.

Fast forward a couple of years when one of our teams kept reporting that one of their nightly processes was failing with an error indicating that it couldn’t allocate contiguous memory. My mind went back to the Henderson talk. The error indicated it couldn’t allocate 229 MB of contiguous memory. When the default setup had only 256 MB total, it wasn’t surprising. Some were blaming SQL Server and asking a case be opened with Microsoft. I pointed to the amount of memory and asked what needed that much. We looked and found a horrendous query in a looping piece of code. It was being built dynamically, adding an AND clause to the WHERE each time it processed a record. This was to exclude said record and the process had tens of thousands of rows to process nightly. I can’t imagine what that execution plan looked like. We had a likely culprit and forced them to rewrite the process. After they did, the process didn’t fail again.






Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads