SQL Server Performance

SQL Server 2005 consuming too much memory

Discussion in 'SQL Server 2005 General DBA Questions' started by tkisling, Jul 20, 2007.

  1. tkisling New Member

    We are seeing an issue where SQL Server is consuming a large amount of memory. The server has approx 16gb of RAM and over the course of a few days the memory will consitently be allocated. We haven't seen the db hang - but haven't let it get to that point either.

    This is the only thing running on this server. Just trying to figure out what might be causing this. Anyone have any ideas?
  2. ndinakar Member

    check out sysprocesses and see which queries are using more CPU.

    ***********************
    Dinakar Nethi
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. satya Moderator

    What is the service pack level on SQL?
    http://sqlserver-qa.net/blogs/perftune/archive/2007/06/19/memory-issues.aspx &http://sqlserver-qa.net/blogs/perft...-may-result-in-a-performance-degradation.aspx

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. tkisling New Member

    Thanks for that info, I see the memusage info returned by sysprocesses. Currently there is no load on that database yet the memory is nearly taxed out. Why would SQL Server continue to hold on to all of that RAM despite nothing happening on the database?
  5. satya Moderator

    That is by default and behaviour of SQL is like that, it will release only when Windows requires it. As long as you don't have any performance issues then you need not worry.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. tkisling New Member

    quote:Originally posted by satya

    What is the service pack level on SQL?
    http://sqlserver-qa.net/blogs/perftune/archive/2007/06/19/memory-issues.aspx &http://sqlserver-qa.net/blogs/perft...-may-result-in-a-performance-degradation.aspx

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

    This is an issue with a customer of ours - I know that they are not on SP2.
  7. tkisling New Member

    quote:Originally posted by satya

    That is by default and behaviour of SQL is like that, it will release only when Windows requires it. As long as you don't have any performance issues then you need not worry.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

    The database hasn't hung but we are seeing some connections timeout. I am trying to determine the cause and we continue to see a lot of RAM being used. Not sure if that is connected but it seemed peculiar to me.
  8. Akthar New Member

    Is this a a specific time , or when a specific process is running?

    AKTHAR
  9. satya Moderator

    For every connection memory is will be consumed or even reserved, so in this case capturing the system usage with SYSMON counters will help a lot to assess. Refer to the blogs above for further investigations

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. MohammedU New Member

    Use the following query to get the what is in your buffer pool...

    select

    count(*)as cached_pages_count,

    obj.name as objectname,

    ind.name as indexname,

    obj.index_id as indexid

    from sys.dm_os_buffer_descriptors as bd

    inner join

    (

    select object_id as objectid,

    object_name(object_id) as name,

    index_id,allocation_unit_id

    from sys.allocation_units as au

    inner join sys.partitions as p

    on au.container_id = p.hobt_id

    and (au.type = 1 or au.type = 3)

    union all

    select object_id as objectid,

    object_name(object_id) as name,

    index_id,allocation_unit_id

    from sys.allocation_units as au

    inner join sys.partitions as p

    on au.container_id = p.partition_id

    and au.type = 2

    ) as obj

    on bd.allocation_unit_id = obj.allocation_unit_id

    left outer join sys.indexes ind

    on obj.objectid = ind.object_id

    and obj.index_id = ind.index_id

    where bd.database_id = db_id()

    and bd.page_type in ('data_page', 'index_page')

    group by obj.name, ind.name, obj.index_id

    order by cached_pages_count desc








    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  11. ghemant Moderator

    Hi,

    quote:
    The database hasn't hung but we are seeing some connections timeout. I am trying to determine the cause and we continue to see a lot of RAM being used. Not sure if that is connected but it seemed peculiar to me.

    There could be several reasons for time out, you have to analyze

    * If this timeout occurs when a particular application is running
    * If timeout occurred in particular timeframe
    * If their is network congestion in particular network segment
    * If a particular query is taking more time/cpu, analyze estimated execution plan and actual execution plan
    * If their is complex query that may eat up your memory and/or cpu
    * going forward for analysis you may also use profiler trace

    HTH

    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
    http://forum.sqlknowledge.com/

    Disclaimer: This post is provided as is, for the sake of knowledge sharing only.
  12. tkisling New Member

    What is the best way to determine if locking is the issue? I can see from the results of the sysprocesses and dm_os_workers queries that there might be some locking occurring. I see many LCK_M_IX, LCK_M_X, and LCK_M_U results returned.

    What is the best way to determine what processes could potentially be locking others, causing timeouts?

    Thanks
  13. satya Moderator

    Use thishttp://sqlserver-qa.net/blogs/perft...g-list-for-the-objects-indexes-using-dmv.aspx blog and see whether you see any sessions are blocked or blocking other processes.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  14. tkisling New Member

    I found the following info regarding turning on tracing to capture deadlock info through SQL Profiler.

    dbcc traceon (1204, 3605, -1)
    go
    dbcc tracestatus(-1)
    go

    Can there be any problems with the amount of information written to the error files? This is for a customer of mine and I don't want to recommend they do something that could adversely affect their overall system.

    Also, how do you turn off that level of tracing? Does it automatically cease once SQL Profiler is turned off, or do I need to run a statement to turn off tracing?

    Many thanks.
    T Kisling
  15. satya Moderator

    In order to find the blocking using PROFILER or server side trace you need those traceflags.

    BOL states, that "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur."

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  16. tkisling New Member

    quote:Originally posted by satya

    In order to find the blocking using PROFILER or server side trace you need those traceflags.

    BOL states, that "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur."

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.


    What does that mean? Does that imply that the amount of logging captured by default should be enough? Why does Technet then suggest using the trace levels above to research deadlocks?

    Thanks
  17. MohammedU New Member

    In sql server 2005 Trace Flag 1222 introduced check BOL topic "Detecting and Ending Deadlocks"

    Trace Flag 1204 writes the information to sql error log...and you enable Trace Flag 1204 or 1222 I don't think you need to run profiler too...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  18. satya Moderator

    Don;t you think PROFILER will get you underlying slow running queries in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.com/

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  19. ghemant Moderator

Share This Page