You an really only check the temp cache in use. You can get that via sp_iqconnection. Look at the Temp* output columns. But, keep in mind that these encompass both memory and disk as data will freely move between the two without the user knowing.
There is no way to put a quota on main cache and to see the usage. The only data in main cache is committed data and in-work transactions on main tables. For committed data, it is shared amongst all users. 100GB of main cache could be used for columns/indexes on tables. User1 logs in and is the only user so they technically are using 100GB of memory. But then 50 other users log in and use the same data. Since it is shared between all 51 users, there is no way to say that it is attached to a particular connection.
For in-work transactions, you can see the main table create and drop/delete space in use per connection. This, again, is not limited to just cache, though. It tracks all space in use by that connection to change data: in memory and on disk.
And if you are on IQ 16, you have the added Large Memory Accumulator (LMA) cache that is used to house the n-bit and FP dictionaries. Like main, this is a shared space and cannot be accounted for on a per user basis.
Are you looking for some sort of accounting? Or is this more to tune queries to reduce the memory footprint?
Mark
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.