Memory

  • I have SQL 2005 64 bit Enterprise edition. I have set the memory to default.

    SQL server is consuming 7 GB of Ram out of 10GB of ram.

    I Did not check the 'AWE to allocate memory' (though it is 64 bit do I need to check that button on memory setting)

    What should I do to esculate the problem I have to restart the SQL but again after 1 day the memory came up to 7 GB.

    Thanks

    Nita

  • hi , how are you checking how much of RAM your sql server is consuming?

  • SQL Server is designed to use as much RAM as is available if you leave it on the default memory settings. Using 7 GB out of 10 GB is not a problem nessecarly. If you want to restrict how much memory SQL Server uses then change the memory config via sp_configure.



    A.J.
    DBA with an attitude

  • Through Task Manager and through DBCC Memorystatus

  • How much I should configure min and max so that SQL server won't utilise no more then 2 GB of memory

  • Set the minimum to whatever you like (0 would even work).

    Set the max to 2048



    A.J.
    DBA with an attitude

  • Thanks

  • Why would you put 10GB of memory in a server and restrict SQL to 2GB? Unless you have a whole load of other things running on there.



    Shamless self promotion - read my blog http://sirsql.net

  • DBA (1/8/2009)


    hi , how are you checking how much of RAM your sql server is consuming?

    use below :

    DBCC MEMORYSTATUS

    ------------------

    --Host wise

    select hostname,count(spid) as Total_Connection,sum(isnull(memusage,0)) as MemUsage from sys.sysprocesses

    group by hostname order by count(spid) desc

    ----------

    --Database wise

    select db_name(dbid) as DatabaseName,count(spid) as Total_Connection,sum(isnull(memusage,0)) as MemUsage from sys.sysprocesses

    group by db_name(dbid) order by count(spid) desc

    --------

    select

    a.name,

    connections = (select

    count(*)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid),

    blocked_users = (select

    count(*)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid and

    blocked <> 0),

    total_memory = isnull((select sum(memusage)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid),0),

    total_io = isnull((select sum(physical_io)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid),0),

    total_cpu = isnull((select sum(cpu)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid),0),

    total_waittime = isnull((select sum(waittime)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid),0),

    dbccs = isnull((select count(*)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid and

    upper(b.cmd) like '%DBCC%'),0),

    bcp_running = isnull((select count(*)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid and

    upper(b.cmd) like '%BCP%'),0),

    backup_restore_running = isnull((select count(*)

    from

    master..sysprocesses b

    where

    a.dbid = b.dbid and

    upper(b.cmd) like '%BACKUP%' or

    upper(b.cmd) like '%RESTORE%'),0)

    from

    master.dbo.sysdatabases a

    -----------------------------------

    select DB = a.instance_name,

    'DBCC Logical Scans' = a.cntr_value,

    'Transactions/sec' = (select d.cntr_value

    from

    master..sysperfinfo d

    where

    d.object_name = a.object_name and

    d.instance_name = a.instance_name and

    d.counter_name = 'Transactions/sec'),

    'Active Transactions' = (select case when i.cntr_value < 0 then 0

    else i.cntr_value end

    from

    master..sysperfinfo i

    where

    i.object_name = a.object_name and

    i.instance_name = a.instance_name and

    i.counter_name = 'Active Transactions'),

    'Bulk Copy Rows' = (select b.cntr_value

    from

    master..sysperfinfo b

    where

    b.object_name = a.object_name and

    b.instance_name = a.instance_name and

    b.counter_name = 'Bulk Copy Rows/sec'),

    'Bulk Copy Throughput'= (select c.cntr_value

    from

    master..sysperfinfo c

    where

    c.object_name = a.object_name and

    c.instance_name = a.instance_name and

    c.counter_name = 'Bulk Copy Throughput/sec'),

    'Log Cache Reads' = (select e.cntr_value

    from

    master..sysperfinfo e

    where

    e.object_name = a.object_name and

    e.instance_name = a.instance_name and

    e.counter_name = 'Log Cache Reads/sec'),

    'Log Flushes' = (select f.cntr_value

    from

    master..sysperfinfo f

    where

    f.object_name = a.object_name and

    f.instance_name = a.instance_name and

    f.counter_name = 'Log Flushes/sec'),

    'Log Growths' = (select g.cntr_value

    from

    master..sysperfinfo g

    where

    g.object_name = a.object_name and

    g.instance_name = a.instance_name and

    g.counter_name = 'Log Growths'),

    'Log Shrinks' = (select h.cntr_value

    from

    master..sysperfinfo h

    where

    h.object_name = a.object_name and

    h.instance_name = a.instance_name and

    h.counter_name = 'Log Shrinks')

    from

    master..sysperfinfo a

    where

    a.object_name like '%Databases%'

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • If its Dedicated Server, then leave 2 GB to OS & rest can be made use by SQL Server.

  • AWE is not required, not available, on a 64bit system. It was included as a workaround to solve the maximum 2GB address space available to 32bit processes.

    To limit your SQL Server, in your SQL Server Instance properties, goto the Memory tab and enter the maximum amount of RAM you wish to use in KB.

    If you want to ensure that SQL Server will manage the memory it is using and not respond to Windows requests for paging, enable the Lock Pages in Memory privilege for the service account that is running the SQL Server Database Engine service.

    --
    Andrew Hatfield

  • How do you gauge the memusage in sysprocesses? how many pages in KB?

  • Please post your question in a new thread. Thank you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

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