A Wandering Mind

So today I spent basically the entire day working on troubleshooting an issue that is related to a little SQL Server technology called MemToLeave. Now I’m not going to go into extreme technical detail on what exactly this is; instead I’ll direct you towards some other excellent resources:

http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

http://blogs.msdn.com/khen1234/archive/2005/05/08/415501.aspx

Basically MemToLeave is an area of reserved memory outside of the buffer pool, which is commonly used for such operations as extended stored procedures, CLR, and linked servers. It only affects 32 bit systems, is limited to a maximum amount of 512MB, and can be set using the “-g” startup parameter.

In my short experience as a DBA, this is far and away the most complex and mind-bending piece of investigative work I’ve done by a landslide. After spending nearly three hours Googling  and digging into various DMVs, I had made very little headway. This is real down-the-rabbit-hole stuff; we’re talking about understanding and trying to determine what is happening deep within the internals of SQL Server. Even my senior DBA, who has a tremendous wealth of knowledge and more than a decade of experience, was largely at a loss to explain what was going on. I even went so far as to use the great Twitter #sqlhelp hashtag (and got some good information, thanks David Levy (twitter | blog)) to try my best to get some insight. As it turned out, the Twitter community helped to push us in what we believe to be the right direction.

While I’ll leave the technical details for another post, our basic conclusion after nearly a day’s worth of work is this: due to a tremendous amount of statements in the procedure cache (the application using this DB makes a maddening number of adhoc calls, in such a way that each is stored as its own plan, even with forced parameterization on), and the fact that a large number of these plans were stored in multi-page arrangements (see Jonathan’s blog post above), MTL became suddenly and rapidly depleted. Normally we’d be forced to stop and start SQL in order to clear this up, but in this case, all we ended up having to do is to a) wait for the application to cease its (adhoc) insert heavy behavior, then run a DBCC FREEPROCCACHE. Before running the command, the return result of xp_memory_size was around 3MB; after it immediately jumped to ~21MB. Also, we saw that the CACHESTORE_SQLCP memory clerk type went from consuming around 180MB to around 1/100th of that (we used the queries on Brent McCraken’s blog here, plus some home-brewed ones of various DMVs). The server stayed quiet the rest of the day.

While I’m far from convinced, the evidence is pretty strong that we’ve found at least a contributing factor. The next bulk series of imports into the system happens overnight, so in the morning we’ll be checking in to see how we are doing.

That’s all for now, but once we have more data I’ll probably be posting a followup with the exact queries and results. What a day!

Be Sociable, Share!

•