SQL cursor memory overhead
I have an  stored procedure that works on an table that contains xml field, and use a cursor to loop over them and run some other sp's. The rows can be many and the documents quite large. There has been some signs of the stored choking under the load. 

I'm wondering if it's possible because how cursors work, perhaps using a cursor means it keep that whole result set in memory. I mentally somehow always imagine cursors like looping over a table while perhaps it's more like selecting data from a table, storing it in memory and then looping over it.  How does cursors work?


PS. Personally I hate cursors from both a performance and ideological standpoint (SQL should be set based) and avoid them like plague. This was a case of legacy code that I now have converted to use a temp table instead, but I'm still curious DS.
SQL cursor memory overhead
===

I have an  stored procedure that works on an table that contains xml field, and use a cursor to loop over them and run some other sp's. The rows can be many and the documents quite large. There has been some signs of the stored choking under the load. 

I'm wondering if it's possible because how cursors work, perhaps using a cursor means it keep that whole result set in memory. I mentally somehow always imagine cursors like looping over a table while perhaps it's more like selecting data from a table, storing it in memory and then looping over it.  How does cursors work?


PS. Personally I hate cursors from both a performance and ideological standpoint (SQL should be set based) and avoid them like plague. This was a case of legacy code that I now have converted to use a temp table instead, but I'm still curious DS.