Out of Memory exception, SQL Server 2012 hotfix

I'm back from the MVP Summit 2013 R2, a cool week of SQL nerdness in Bellevue, WA. I've Heard all the juicy, cool stuff about SQL Server now and the future and it really hurts not being able to talk much about it. Being a MVP is surrounded with much NDA and supersecrets that are not available for disclosure yet. I'll promise you all I'll get back on this when time is right.

Anyways, for this post I've chosen to give you readers a link, http://support.microsoft.com/kb/2769594.

It's for a hotfix, fixing something unusual. It fixes a Out of Memory Exception that occurs on SQL Server 2012. What happens is a memory leak by the MEMORYCLERK_SQLLOGPOOL which is used for transaction log activity. If you're running AlwaysOn and have a huge buch of availability databases this could occur in your setup.

Failed allocate pages: FAIL_PAGE_ALLOCATION 8
Error: 701, Severity: 17, State: 107.
There is insufficient system memory in resource pool 'internal' to run this query.
Error: 701, Severity: 17, State: 123.
There is insufficient system memory in resource pool 'PoolName' to run this query.
Error: 701, Severity: 17, State: 107. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1).
AppDomain 3181 (<appdomain>) is marked for unload due to memory pressure.
AppDomain 3181 (<appdomain>) unloaded.
Error: 9602, Severity: 16, State: 1.
Failed to start a system task with error code 28709, state 19.
Error: 17300, Severity: 16, State: 1.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task HADR Task due to errors in starting up the task (setup state 1).
Error: 18054, Severity: 16, State: 1.

Physical forms of a table

I just had this question in my email.

"
Dear dbguru,

How do I find which tables that are heaps and/or clustered?

Best regards,
XXX
"

Well, first of all the XXX person has probably been to TechEd 2013 in Madrid, and maybe seen the keynote day one when they did a great demo of GeoFlow. If you haven't seen it you have the link here, http://channel9.msdn.com/Events/TechEd/Europe/2013/Key01. The most juicy part is appr 1 hour into the keynote when Dandy Wein, @ilikesql, enters the scene and do a demo of GeoFlow. It's about ten to fifteen minutes... Anyway, I wore that jersey with pride all week!

Secondly, XXX, has a real name too. But I have decided to leave the person out of this.

Ok, let's get back on track.

In SQL Server the tables are either one of two basic physical forms; HEAP or CLUSTERED INDEX.
* A heap is a table without a clustered index, i.e. this table stores the rows in any order on data pages. This means that when you insert a row it ends up on any of the available data pages assigned to this table.
* A clustered index on the other hand has a clustered index key, which is one up to sixteen columns, with a width up to 900 bytes, that determines on which data page the inserted rows should end up on. This index clusters the rows on pages. This means that rows that is relating to each other can end up close to each other in the database.

Ok, how do I find out how my database is configured related to this?
Well, you could run the following query!

SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS TABLE_OBJECT,
CASE
WHEN i.type = 0 THEN 'HEAP'
WHEN i.type = 1 AND i.is_primary_key = 1 THEN 'CLUSTERED INDEX (' + i.name + ') AS PRIMARY KEY'
WHEN i.type = 1 AND i.is_primary_key = 0 THEN 'CLUSTERED INDEX (' + i.name + ')'
END AS TABLE_PHYSICAL_FORM
FROM sys.Schemas s
INNER JOIN sys.Tables t ON s.schema_id = t.schema_id
INNER JOIN sys.Indexes i ON t.object_id = i.object_id
WHERE i.index_id < 2

If i.type equals 0 the table is a heap, 1 means a clustered index, 2 means a non clustered index and i.index_id is the sequential id of indexes on a table, 0 is heap, 1 is clustered index, 2 or larger *) is a non clustered index.

*) There are more types of indexes than I cover here. Just larger than 2 is not the whole truth, but it's Close enough in this blog post.

Updates

As from today you all can update your SQL Servers, at least 2008 SP3 and 2012 SP1.

Check these links out:
* SQL Server 2008 SP3 Cumulative Update 12
* SQL Server 2012 SP1 Cumulative Update 5

Enjoy!

A blog, to be continued...

For the last year I've been blogging on and off at my current employers blog and almost forgot about this blog. I have a lot of old blogposts from previous and old blogs I'm going to inject into this blog in a close future. Or at least having the ambition to...

Changes are about to happen, close to a month ago I decided to move on  as I got an offer from Claremont AB and to be their database guru from somewhere in the time frame of "late september/early october" this year. My future role will be database guru/trainer/mentor/speaker, and I'll continue on doing consultancy on Microsoft SQL Server.

So guys, see this post as a new start of this blog, this is where I'll continue to post stuff about SQL Server, databases, BI, and whatever that comes in mind.