dmoldovan (12/11/2012)
My customer is running a small e-commerce site on a SQL Server 2008 R2 SP2 Workgroup Edition x64. The SQL Server runs on a Windows Server 2008 R2 Standard x64 SP1 having 12GB RAM. I've allocated a max memory of 5GB to SQL (the min memory is o) and the account used by the SQL Server service has been granted the "Lock pages in memory" right. The database and the site (.NET 2.0 ) are situated on the same machine. The machine has 2 NUMA nodes. I've set the processor affinity to one node for SQL and to the other node for IIS.Apart from CPU spikes due to a piece of sloppy programming, there seems to be no other area where things are going wrong.
However, this message appears every few days in the SQL error log : "Failed to map 8388608 bytes of contiguous memory". I've found no related error message in the Windows or application logs.
Where should I begin investigating in order to find the cause of this error?
Thank you...
Usually this is something seen on 32 bit machines and MemToLeave, but you state you are on x64 which usually doesn't have a problem there. But you do have a VERY low max memory set too. I note however that only about twice in 15 years of consulting have I seen valid reasons for messing with processor affinity and something could be screwy with that (and the fact that you have NUMA hardware too).
Are you getting any other errors? Please post the ENTIRE error message. Is this repeatable at/near the same time of day or random? Is this a virtual machine by any chance?
I would watch memory usage of all items on the box and see if you can't increase that max mem setting for SQL Server. You may also want to set up a profiler trace to local disk with rollover files to capture activity during the event. Be sure to delete the files as they stack up to avoid filling up a drive (and do NOT do STMT COMPLETED!!!).
Do you use a lot of XPs, CLR, Full Text, etc?
You may want to consider getting a professional on board for a few hours to give your system a quick review. Lots of potential issues here and it could take you days or weeks of hunt-and-peck forum stuff and still not isolate a cause.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service