(First blog post from Poland! We're here to present at the Microsoft Poland Technology Summit.) 

The October edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column.

This month's topics are:

• Unexpected consistency checks?
• How much space is each database using in the buffer pool 
• Difference between SUSPECT and RECOVERY_PENDING 
• Why database mirroring failure detection isn't instant

Check it out at http://technet.microsoft.com/en-us/magazine/2009.10.sqlqa.aspx.

I'm constantly surprised by the number of people that don't follow the CSS SQL blog - some of the best SQL people in the industry post there. I just revisited a couple of really interesting posts by my good friend Bob Ward on memory management internals that I'd like to publicize.

The first (from June this year) discusses the Large Page allocations that can occur on 64-bit systems and how they affect the memory used by SQL Server. Apart from explaining how this works and the conditions under which it is possible, this post also explains some of the potential problems that may occur from using them, especially with fragmented Windows Server memory. You can get to the blog post at: SQL Server and Large Pages Explained...

The second post from a week or so ago discusses the MemToLeave behavior of SQL Server (leaving memory from SQL Server's virtual address space for stuff outside the buffer pool) and some of the issues around that (and that it doesn't happen on 64-bit systems). You can get to that post at: Come on 64bit so we can leave the mem...

Enjoy!

I was reading a thread on SQL Server Central today where someone replied to a question and confused 823 with 832. Now, 823 is a nasty error to get - it says that an I/O operation failed at the OS level and the I/O subsystem is causing corruption - SQL Server didn't even get a chance to check the page checksum (and potentially raise an 824 error).

Error 832 is way worse. When you get one, you'll see an error like the following:

A page that should have been constant has changed (expected checksum: 1dcb28a7, actual checksum: 68c626bb, database 13, file 'E:\Program Files\microsoft sql server\MSSQL\data\BlahBlah.mdf', page (1:112644)). This usually indicates a memory failure or other hardware or OS corruption. 

This error occurs *AFTER* a page has been read into memory. Once the page passes all checks as it's read in from disk, it's a known clean page sitting in the buffer pool. When the time comes to update the page and mark it as a dirty page, the checksum is checked again, juuuust to make sure. If the checksum is no longer valid, error 832 is raised. This is saying that something apart from SQL Server stomped on the page while it was in SQL Server's memory - either hardware memory corruption (bad RAM), an OS memory-management bug, or a rogue process writing into SQL Server's memory space.

It's really bad. If memory diagnostics don't show up any issues, there's a way to start to track down what's going on, but only by enabling an expensive trace-flag at server startup that prevents untoward accesses to SQL Server's memory unless an exclusive page latch is held. And this should only be enabled under Product Support's supervision.

So - hopefully you'll never see this - they're very rare - I've only seen a handful in my time. If you do, run memory diagnostics, and if nothing shows up, call PSS to help you out.

Hope this helps.

(Thanks to David Baffaleuf for pointing this out to me)

Ever wanted to know a bit deeper how memory works in your laptop or servers? This comprehensive series of blog posts will give you a great overview. It's very clearly written and goes very deep. At times it talks about how things show up in the Linux OS, but all the hardware details and concepts (caches, NUMA, etc) translate exactly into our world.

It covers:

• Part 1 (Introduction to memory)
• Part 2 (CPU caches)
• Part 3 (Virtual memory)
• Part 4 (NUMA systems)
• Part 5 (What programmers can do - cache optimization)
• Part 6 (What programmers can do - multi-threaded optimizations)
• Part 7 (Memory performance tools)
• Part 8 (Future technologies)
• Part 9 (Appendices and bibliography)

[Edit: And as Kalen points out in her comment below, you can get the whole thing as a PDF here.]

Enjoy!

PS This is something I'll start posting on in the rest of the year too, as far as SQL Server is concerned.

Finally! All those customers out there who are frustrated by not being able to turn on the 'lock pages in memory' option to protect the SQL working set on Standard Edition - the wait is nearly over. Bob Ward (Principal Escalation Engineer in CSS, and a good friend of mine) announced at PASS Europe that the option is soon to be supported - in the May CU for SQL 2008 and the June CU for SQL 2005. You can read a little more in his blog entry here.

If you don't know what this is, checkout this entry in Books Online - it basically prevents Windows paging out SQL memory to disk.

[Edit: the bits containing this fix have been released for 2008. See here for details.]