SQL Server Memory Management

Recently, I had the opportunity to brush up on my SQL Server memory tuning skills.  It’s been a while since I needed them, but I thought I’d throw out what I used for someone else’s future benefit.

First, some background knowledge:
There are different physical memory limits for each Windows release.
The virtual address space (set of virtual memory addresses) is limited to 4 GB for 32-bit Windows.
The virtual address space is divided into two 2 GB partitions: one for use by the processes and other reserved for use by the system.
The address space (amount of physical memory) is limited to 2 GB for a 32-bit process.

In tuning SQL Server, there are 3 key things to look at: Physical Address Extension (PAE), Address Windows Extensions (AWE), &  4 Gigabyte Tuning (4GT).

Physical Address Extension (PAE) is a switch (/PAE) that is added to the boot.ini that allows an x86 processor to access and use more than 4GB of physical memory.  It’s only used for 32-bit versions of Windows, as x64 processors can access more than 4 GB natively.  Most versions of Windows since 2000 Advanced Server support it.

4 Gigabyte Tuning (4GT) is a switch (/3GB) that is added to the boot.ini that increases the processes portion of the virtual address space from 2 GB to 3 GB.  4GT will limit the available RAM to 16GB, so if the server has more than 16GB RAM, 4GT should not be used.

Address Windows Extensions (AWE) is a set of APIs that allows applications to access more than 4GB of RAM.

Which ones to use is determined by how much RAM your server has.  Here is a general rule of thumb for what to use and when:

RAM USE
4GB /3GB
8GB /3GB, /PAE, AWE enabled
16GB /3GB, /PAE, AWE enabled
>16GB /PAE, AWE enabled

Of course, doing research is part of the fun, so here are some references to keep handy:
Memory Limits for Windows Releases:  http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx
4GT:  http://msdn.microsoft.com/en-us/library/bb613473(VS.85).aspx
4GT Technical Reference:  http://technet.microsoft.com/en-us/library/cc778496(WS.10).aspx
AWE:  http://msdn.microsoft.com/en-us/library/aa366527(VS.85).aspx
PAE:  http://msdn.microsoft.com/en-us/library/aa366796(VS.85).aspx

Happy tuning!

Implicit Conversion of Fast_Forward Cursor to Static Cursor

During the course of one of my last blog posts on Cursors, I ran across a niche piece of information that I didn’t realize.  I had always read that FAST_FORWARD CURSORs were the fastest and least locking of the different CURSOR types.  What I didn’t realize was that if the SELECT statement joins on one or more tables with a trigger table (INSERTED/DELETED), the CURSOR is converted to a STATIC CURSOR!  Unfortunately, most of the tables that I work with have some sort of trigger.  So this was really important information.  Read more about it in this MSDN article.

Differences between STATIC and FAST_FORWARD CURSORs:
They are both read-only concurrency model.  This means that no locks are held on the base table(s).  However, while fetching a cursor row, a Shared Lock will be acquired.  The Shared Lock can be avoided with a NOLOCK hint on the SELECT, or via isolation level.  However, once the row is fetched, the locks are released.
STATIC:  Think of this as a snapshot of the result set.  When it is opened, a temporary table is created in tempdb, which adds additional pressure on the database.  It does not reflect any types of changes to the underlying database, such as INSERTs, UPDATEs, or DELETEs.  This is a read-only cursor that can go forward and backwards.  There can be significant impact on server resources for creating, populating, and cleaning up the snapshot in the tempdb database.
FAST_FORWARD:  This will create a fast forward-only, read-only cursor with performance optimizations enabled.  It can only go from the first row forward to the last row.  It operates directly on the base table(s).  Unlike a static cursor, rows are not retrieved from the database until they are fetched.  Any changes to a row (from INSERT, UPDATE, DELETE statements) before the row is fetched, will be reflected in the fetched row.

Disclaimer:  If possible, avoid using cursors.

Avoid CURSORs, rewrite into WHILE LOOPs

A few months back, we had a stored proc at work that was using CURSORs pretty heavily.  It had CURSORs inside of CURSORs.  This proc was one of our oldest, biggest, and most involved procs at over 2050 lines.  On larger clients, it could be called thousands of times a day.  All of the cursors were FAST_FORWARD types, which should have at least been somewhat performance friendly.  However they were SELECTing from tables that had triggers which implicitly converted the cursor type to be STATIC.  That’s not good, because it was locking up the SELECTed table and causing all kinds of headaches.

So the decision was made to rewrite it to use table variables and WHILE LOOPs.  This sped the proc up immensely, as measured by elapsed time, at the cost of using more in-process memory.  You see, when a CURSOR is used, it basically creates a temp table on TempDB and iterates through it.  With a STATIC cursor, the selected table has locks placed on it in the transaction tables.
The CURSOR alternative is to create a table variable containing the rows that would have gone in the CURSOR and use a counter variable to loop through it using the IDENTITY column on the table variable.  It’s still row-by-row processing, but it’s faster, more efficient, and doesn’t lock.  The only downside is that it will use more RAM.  So beware if you’re using SQL 2000, SQL Express, or an MSDE version as you may see performance degrade if the sqlservr process hits it’s memory ceiling.

I don’t think I need to say this, but will anyway.   Changing CURSORs into WHILE LOOPs are better, but CURSORs into a set-based query is best.  It’s always best to do a query in a single batch as opposed to row-by-row.

CURSOR code:

DECLARE @SomeID INT
DECLARE myCursor CURSOR LOCAL FAST_FORWARD  FOR
  SELECT DISTINCT  
    SomeID
  FROM dbo.TableA
  WHERE ID =  @ID

OPEN myCursor
FETCH NEXT FROM myCursor INTO  @SomeID

WHILE(@@FETCH_STATUS  <> -1)
BEGIN
  --do  activity
END

TABLE VARIABLE, WHILE/LOOP code:

DECLARE 
  @rowCount INT, 
  @currentRow  INT, 
  @SomeID  INT

DECLARE @tableVariable TABLE (RowID INT IDENTITY(1,1), SomeID INT NULL)

INSERT INTO  @tableVariable(SomeID)
  SELECT 
    SomeID
  FROM dbo.TableA
  WHERE ID = @ID

SELECT @rowCount = @@RowCount, @currentRow =  1

WHILE @currentRow<=@rowCount
BEGIN
  SELECT @SomeID =  SomeID FROM @tableVariable WHERE RowID =  @currentRow
  --do  activity
  SET  @currentRow = @currentRow + 1
END

Goal: Explore more solutions than just WinForms

It seems to me lately that I am continually writing, essentially, the same program for every problem.  The language has changed from VB6 to VB.NET to C#, but the pattern behind the design is the same.  It’s always a Winform front end, common logic/objects in a middle dll, and data access from another dll that usually hits stored procedures on a SQL Server.  Not that it’s a bad design, because it’s not.  But I’m just afraid that I’m not growing as a developer.  I even use the same old design of a common starting point that calls a logical UI object that controls the flow to the user and back down to the database.  I guess it’s considered a data centric design for n-tier architecture.

The point is that I need more depth and breadth in my solutions.  Sure I can be an expert in this one area, but how often is another area a better solution to the problem.  What do I do when a client-server WinForm isn’t the answer?  I should have a better understanding of the WebForm/Web application world and the design patterns they use.  The funny part is, I’ve had jobs where I’ve written to mobile devices and done middle/backend work on web apps; I’ve even done Javascript on an ASP.NET app.  I was doing my part in those development shops; Providing the needed code to get the job done.  I just never felt completely comfortable with anything but WinForms.  At least to the point where I would tackle one by myself.

So here’s a goal for myself: Explore more solutions than just WinForms.  My previous post on free iPhone development training should be a good starting point for completely thinking outside of the WinForm world.  I would ideally like to learn more about ASP.NET web apps and Silverlight.  Certainly the MVC design pattern deserves quite a bit of attention.  But I should also explore the new kid on the block in WinForms, WPF.  Anyway, it should be a fun journey and I’ll surely have grown in the process.  I’ll try to blog about what I’m doing to achieve this new goal.

~Adam

Free iPhone development training

A few months ago, I was talking with a friend about programming on an iPhone.  We were discussing the potential audience that an iPhone commanded.  I didn’t know much how the iPhone operated (I don’t own one), however, it did pique my interest enough to research it a bit.  So, shortly after that conversation, I went to the Apple Dev Center and perused the Getting Started Documentation.  Well, it was more than a little confusing.  I’m a .NET developer and certainly didn’t have a grasp on the iPhone or it’s programming model.  Needless to say, I didn’t make it very far in reading that initial documentation.

Last night, I was surfing blogs and came across David Hayden’s blog.  He had an entry on iPhone development and again I was reminded that I didn’t really give it my all in getting under the hood of the iPhone.  David’s post pointed to free iPhone training by Stanford University on iTunes.  WHAT?!  FREE?!  So now I have downloaded the almost 24 hours of video and 17 PDFs.  This time I will give the iPhone the attention it deserves.  Hopefully, I’ll be able to put together a Hello World app on an iPhone.  (Anyone want to let me borrow a Mac OS X and iPhone?!)

Use the following link to find the free training.  It should either open iTunes and allow you to download the videos/PDFs, or it’ll prompt you to install iTunes.

http://deimos3.apple.com/WebObjects/Core.woa/Browse/itunes.stanford.edu.2024353965.02024353968

What makes up a good blog?

In starting this blog, I have to ask, What amount and kind of content makes up good blog?  My goal is for it to define me as a familyman, and professional.  It should be about what interests me.  That being technology, family, books, & thoughts.  I believe that it should reflect the balance in life in which I do all of those things.  For me, about 50% of my time is consumed by technology, 30% is enjoying family, and remaining 20% is split evenly between books & thoughts.  Hopefully, my content will be worthy of following.  Let me know if I need to include something else, or add/remove anything as I’m sure this will be a rough draft for content composition.