Isolating Extended Procedures

for RuBoard

Because extended procedures run in the SQL Server process space, they can wreak havoc when ill-behaved. Of particular concern is the possibility that an extended procedure can leak memory, eventually exhausting the entire MemToLeave pool. SQL Server memory is organized into two basic memory pools: the buffer pool, from which the data and procedure caches are allocated, as well as most other memory allocations; and the MemToLeave pool, which defaults to 256MB in SQL Server 2000 (128MB in SQL Server 7.0) and is the area from which extended procedure and COM in-process (e.g., OLE-DB) allocations come. You can set the size of the MemToLeave pool via SQL Server's -g command-line option.

If an extended procedure that leaks memory is called frequently, it will eventually exhaust the entire MemToLeave pool. When this happens, the server will likely begin to crash or otherwise behave erratically, and the error log will begin to receive 17800-series errors along with, possibly, 8645 errors. At this point, the only real answer is to shut down and restart the SQL Server. If the extended procedure isn't fixed, the problem will continue to occur and the server will have to be regularly stopped and restarted to keep performance acceptable.

What if you could isolate extended procedures so that they ran outside the SQL Server process (  la out-of-process COM servers)? Well, you can. Sort of. The technique is really very simple. It amounts to creating a named instance of SQL Server whose purpose is to run extended procedures. Just follow these steps to set it up:

  1. Make sure your server machine has plenty of RAM.

  2. On the same machine as your primary SQL Server 2000 or later instance, install a second named SQL Server instance for the express purpose of hosting extended procedures.

  3. Register your extended procedures with this new instance, but not with the primary instance.

  4. Create system stored procedures on the primary instance that call the extended procedures on the other instance using four-part names .

  5. Code your application to call these system procedures rather than the extended procedures.

Using this technique allows you to set up an entire process just for extended procedures to run in. They are isolated from your production server by the operating system, and even if one of them is ill behaved, it's unlikely to be able to take down your primary SQL Server.

Remember that you can set the size of the MemToLeave pool using the -g command-line option on the extended procedure instance. You may wish to set it higher than the primary instance because it will house your application's extended procedures.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net