soonerdave


Hello, all

We are trying to work out a memory problem associated with a SQLCLR procedure we have developed.

This procedure will run properly on a SQLExpress box, but fails with an Insufficient Memory error (701) when run on our 32-bit enterprise edition server on W2K3 Enterprise Server. The server has 3.25 GB of RAM, and does *not* have the /3GB switch enabled; AWE is *not* enabled on SQL Server.

I initially suspected the problem was due to the idiosyncracies of how the BPool is allocated on SqlExpress and its 1GB buffer pool limitation, giving a 1GB MemToLeave region for SQLCLR allocations -- substantially larger than the default available on the server with it's default 256MB allocation (+128MB for thread stacks). That seemed to explain why a memory problem might not manifest itself on the client, but would on the server. I then altered the server to include an explicit "-g" parameter at startup, reserving 512MB for MemToLeave, but the query still fails with the same memory errors. I've tried values as high as 1GB (-g1024), but none have worked Our DBA has installed a recent hotfix associated with certain memory errors, to no avail.

It appears that the server will max out the CLR allocations at 102MB, regardless of the presence of the -g parameter. Could it be that the MemToLeave region really is that severely fragmented There are still log messages indicating failures to reserve virtual memory...

One thing I notice is that the buffer's VM Reserved value never seems to change regardless of the value of the "-g" option. I would expect to see it decrease by the amount specified as reserved, eg -g 768 should leave, for example, something like 1.2GB VM Reserved for buffer pool allocation, but it doesn't. That tells me I'm not really establishing a larger MemToLeave area. Is this expectation incorrect

Bottom line, we don't know why this procedure is running on the server, but not in SqlExpress. It is a procedure that connects to a remote Oracle database and collects about 14,000 records, but dies after sending about 7,000 or so records when run on the server. Again, the same query running against the same code on a SqlExpress box works perfectly.

Any thoughts or suggestions welcomed and greatly appreciated.

David




Re: Should SQL 2005's BPool reservation size vary with "-g" startup parameter?

Ajmer Dhariwal


First of all, make sure there's no space between the -g parm and the memtoleave value; I noticed you written it "-g 768" and if that's how you passed it as a parameter, then it'll be ignored and defaults will be used.






Re: Should SQL 2005's BPool reservation size vary with "-g" startup parameter?

soonerdave

Hi, Ajmer

Thanks for taking the time to help!

I have double-checked and can verify that there is no space between the "-g" and the memory size parameter.

-David






Re: Should SQL 2005's BPool reservation size vary with "-g" startup parameter?

soonerdave

All:

We have discovered the cause of this problem. This may, in fact, be a bug in SQL Server.

After specifying various values for the "-g" parameter, we would restart SQLServer. Even though we would see the "-g" option appear in the log during startup, the DBCC MEMORYSTATUS results showed that the -g parameter was not being honored. We tried several different values, restarting SQLServer each time, and the results were the same.

We then rebooted the server, forcing a ground-up restart of SQLServer, and after doing so, the DBCC MEMORYSTATUS results DID show that the -g parameter was now being honored, and the SQLCLR procedure that drove the issue now runs properly.

It is my understanding that the intent of the configuration manager was to permit changes to SQLServer configuration that do not require a reboot of the server, but merely a restart of the SQLServer. This would indicate that, for at least this one option, it is recognized only after the first SQL Server startup following system boot -- even if it appears in the database startup logs.

This has the potential to be a very, very tricky issue to discover, so I thought it might be useful to post our findings here.

Many thanks,

David