CLR memory usage

Hi All -

We have some CLR sprocs and tvf's we run in a batch job that recently have been getting the out of memory issue. I want to increase the amount of memory allocated to the CLR using the -g startup switch but i want to make an intelligent decision on how much to allocate. What are some of the best ways that you have found to estimate how much to give the CLR?

Thanks,

Cameron

The first step in this process is to look at your CLR sprocs and TVFs and look at how they allocate memory. Are they doing things like filling datasets with potentially large amounts of data? Are they using readers, but constructing lots of objects that grow in proportion to the query size against the database? If so, there may be a fundemental design problem that needs to be addressed. If that isn't the case, then looking at how memory is used in your CLR sprocs and TVFs should begin to help you understand what new memory limits to apply. Only by understanding where and when and how much memory is allocated for your worst case workload can you begin to understand how to change the limits.