connection memory usage

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    How to I determine the total memory usage for all of my database user connections? I realise that sql*server will grab "minimum query memory" when the end-user runs DML, but I am having some trouble locating the pool of memory for end-user connections and the total consumed as a point in time measure.

    I am currently using SQL*Probe, and tells me that each connection uses approx 40Kb. Where does this information come from? and is the connection memory pool part of a large memory structure?

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Andy Warren

    SSC Guru

    Points: 119655

    I've seen that 40k figure documented a couple times, never thought to figure out where it came from. Not sure where to look just yet.

    Could you give us some background on why you need to know this? Inquiring minds...

    Andy

  • ckempste

    SSCoach

    Points: 17983

    Andy

    Why not?? 🙂 i have a interest in understand the sqlserver kernal and how its going about allocating and using its memory structures, this is very handy when clients ask me and allows me to better predict growth requirements. I currently have a client that has around 110 concurrent users and wanted to know how memory was being allocated, even for idle connections.

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Andy Warren

    SSC Guru

    Points: 119655

    I give you credit for trying to get to the bottom of it, but at some point you have to call it a black box and be done with it!

    Cynically speaking, if your client is counting RAM for connections...they don't have enough!

    Andy

  • Sean Burke

    SSCommitted

    Points: 1504

    It's good to know the dirty secrets of the application; however, you can get 1GB of ram for under $200. www.pricewatch.com

  • ckempste

    SSCoach

    Points: 17983

    Hi guys

    Well ill tell you where all is came from....

    I am currently using SQL*Probe (great tool btw)... and they have a graph showing total user memory at a point in time. For the life of me I cant work out how they are getting it and exactly what impact it has on memory usage...i know ram is cheap etc etc.. but in massive web-based apps, I would like with some sort of confidence to better predict (esp historically).. the usage (high usage may even mean crap code!).

    Anyhow, ive asked them how they came up with the figure and will update the list from there.. 🙂

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    Here is the query to get current connection memory usage as a point in time reading. From testing it doesnt seem to measure tempdb usage for the connection though.

    select counter_name, cntr_value, object_name from sysperfinfo where

    counter_name like 'Connection Memory%'


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    Some more info I got from a training CD. I have yet to confirm this..

    SQL*Server - v7.x

    User(connect) 24Kb

    Open DB 2880 bytes

    Open object 276 bytes

    Lock 96 bytes

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Colleen Rossman

    Mr or Mrs. 500

    Points: 523

    Andy Warren - Monday, August 27, 2001 6:45 PM

    I give you credit for trying to get to the bottom of it, but at some point you have to call it a black box and be done with it! Cynically speaking, if your client is counting RAM for connections...they don't have enough!Andy

    Dear ArrogantAndy:  Your replies are  not at all useful.  Fortunately others posted  their memory investigation queries without the ego attached.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply