SQL Server Performance


Discussion in 'General DBA Questions' started by ddupuis, Nov 7, 2007.

  1. ddupuis New Member

    Our production database server has 16GB of memory. It is running Windows 2003 Server Enterprise and SQL Server 2000 Enterprise. In the boot.ini we have the /PAE option. In SQL Server we have AWE enabled, dynamic memory managemement, 0MB min, and 14336MB maximum. The database server's only purpose is to run SQL Server.
    So I have three questions based on this information.
    1. Have we configured our server correctly for using AWE?
    2. Did we allocate SQL Server too much/too little memory (14336MB)?
    3. How would I tell if SQL Server actually using the memory we gave it? Are there some specific counters I should look at? Quest Performance Analysis is not much help as it simply shows that around 15400MB of memory are in use. If you look at task manager it is the same thing. I cache hit ratio since this server has been put in has averaged above 97%.
  2. Greg Larsen New Member

    Here is a query that will return some memory counter information:select
    * from sys.dm_os_performance_counters
    where counter_name = 'Total Server Memory (KB)'
    or counter_name = 'Target Server Memory (KB)'
    The 'Total Server Memory (KB)' will show you how much SQL Server is using in the buffer cache, not the total amount of memory SQL Server is actually using.
  3. satya Moderator

  4. Greg Larsen New Member

    I guess I should have scrolled my screen a little to the right to see that SQL Server 2000 was being used. Any way this query will work in SQL Server 2000 and 2005 for the matter:select
    * from master.dbo.sysperfinfo
    where counter_name = 'Total Server Memory (KB)'
    or counter_name = 'Target Server Memory(KB)'
  5. Luis Martin Moderator

    Or, Performance Monitor, SQL Memory Manager, counters: Total Server Memory and Target Server Memory.
    That is just in case you want to monitor graphically, because what Gred wrote must work.
  6. ddupuis New Member

    It works but all I see is 14436 for both items which is the upper limit I specified for SQL Server.
  7. Greg Larsen New Member

    That means SQL Server has consumed up to the max. If you stop and restart SQL Server the total number should be less than the target.
  8. Greg Larsen New Member

    I should have added that over time should see you total memory grow and eventually it might reach the max. SQL Server only release memory if it detects that the system is under memory pressure.
  9. ddupuis New Member

    I ran the statement in my test envrironment, then restarted the SQL Server service, then ran it again. Here are the results:
    SQLServer:Memory Manager Target Server Memory(KB) 10493904 65536
    SQLServer:Memory Manager Total Server Memory (KB) 10493904 65536
    SQLServer:Memory Manager Target Server Memory(KB) 10492912 65536
    SQLServer:Memory Manager Total Server Memory (KB) 10492912 65536
    It is the same in all of my envrionments.
  10. satya Moderator

    Are you having any performance issues lately?
    Are you getting any memory based alerts?
  11. ddupuis New Member

    [quote user="satya"]
    Are you having any performance issues lately?
    Are you getting any memory based alerts?
    I am not having in performance problems or alerts. I just wan to find out if SQL Server is effectively using the 14GB of memory it has been allocated.
  12. Greg Larsen New Member

    Based on those numbers looks like you are using 10 GB.
  13. ddupuis New Member

    10GB is what have set as the upper limit on my test server. How could it be using 10GB worth of memory right after a SQL Server restart?
  14. Greg Larsen New Member

    Now that is a very good question. I would check the minimum server memory and verify it truly is set to 0 (zero).
  15. satya Moderator

    Is this a 64 bit platform for windows & SQL?
  16. ddupuis New Member

    [quote user="Greg Larsen"]
    Now that is a very good question. I would check the minimum server memory and verify it truly is set to 0 (zero).
    Output from sp_configure:
    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    affinity mask -2147483648 2147483647 0 0
    allow updates 0 1 0 0
    awe enabled 0 1 1 1
    c2 audit mode 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    Cross DB Ownership Chaining 0 1 0 0
    cursor threshold -1 2147483647 -1 -1
    default full-text language 0 2147483647 1033 1033
    default language 0 9999 0 0
    fill factor (%) 0 100 0 0
    index create memory (KB) 704 2147483647 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2147483647 0 0
    max degree of parallelism 0 32 0 0
    max server memory (MB) 4 2147483647 14336 14336
    max text repl size (B) 0 2147483647 65536 65536
    max worker threads 32 32767 255 255
    media retention 0 365 0 0
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 0 0
    nested triggers 0 1 0 0
    network packet size (B) 512 32767 4096 4096
    open objects 0 2147483647 0 0
    priority boost 0 1 0 0
    query governor cost limit 0 2147483647 0 0
    query wait (s) -1 2147483647 -1 -1
    recovery interval (min) 0 32767 0 0
    remote access 0 1 1 1
    remote login timeout (s) 0 2147483647 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2147483647 600 600
    scan for startup procs 0 1 1 1
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    two digit year cutoff 1753 9999 2049 2049
    user connections 0 32767 0 0
    user options 0 32767 0 0
    This is not a 64 bit install.
  17. satya Moderator

    Also check under Server properties to see what is setup.
  18. Greg Larsen New Member

    looks like you have not set the minimum server memory. Based on what you have provided I'm guessing SQL gets to the max memory very quick after reboot. I have heard of this happening.
  19. ddupuis New Member

    I just found the following statement in BOL:
    Important Instances of SQL Server 2000 running in Address Windowing Extensions (AWE) memory mode do allocate all the full amount of memory specified in max server memory on server startup. For more information about AWE memory, see Managing AWE Memory.
    I guess that would explain it.

Share This Page