Tuesday, September 25, 2012

Memory architecture changed in SQL Server 2012

So far, as a DBA'S we were always  trained to configure memory on 64 bit OS and follow the things like lock pagesin memory, setting max and min memory etc

With the release of SQL Server 2012, some of the things need consideration as memory structure has changed a bit ( for good ofcourse :) )

In SQL Server 2012, now CLR and MPA are now part of the buffer pool itself.

What this means?????

This means requests for CLR allocation and multi-page allocation(MPA) will be handled by buffer pool and is not outside like it used to be in sql server 2008 R2 and previous versions.

Now when we are configuring max server and min server memory, we have to keep in mind that allocations of pages greater than 8KB, like that for CLR and MPA are also controlled by max and min settings.

So these settings need to be revised, whenever you are upgrading to sql server 2012.

sql server 2012  now will account for more memory allocations(even pages > 8kb) from buffer.

Also for the 32 bit SQL, AWE feature is deprecated.
 

Thursday, September 20, 2012

Set based Windows function should remove the need of cursors completly

Today I was refreshing myself with the new Windows functions availaible in SQL Server 2012. It all started with back in windows server 2005, when we saw lot of usage of CTE,OVER(), RANK,DENSE_RANK etc to support lot of complex queries into simple code.

With SQL Server 2012, this list has extended with extended support over clause, FRAME, LEAD,LAG,FIRST_VALUE,LAST_VALUE,PERCENT_RANK,PERCENTILE_DISC etc

As a developer, you should leverage more of these set based functions and as a DBA, when reviewing code, make sure you see usage of these functions more :)