SQL Server Performance

SQL 2005 64 bit - sp_configure

Discussion in 'SQL Server 2005 General DBA Questions' started by chetanjain04, May 9, 2007.

  1. chetanjain04 Member

    Hello All,

    I have a SQL 2k5 DB 64 bit Ent Edition on win 2k3 R2 x64 Enterprise edition.

    1. I saw that the max worker threads setting is 0 by default. Should I change this to 255 or keep default.

    2. Should AWE be enabled since the server has 32 GB of RAM and 8 processors. I think it is not required since it is 64 bit OS.

    Are there any new parameters that I need to set or use the defaults only.


    Best Regards,

    Chetan
  2. satya Moderator

    You only need to consider setting max server memory or such configuration settings if you expect load on the machine to be memory bound. In this case Max worker threads setting does affect how much memory server will require under heavy concurrent load. Having on x64 platform each SQL Server thread can consume 2MB of physical memory.

    So the main question is do you have any performance issues currently on this system?
    Are you expecting any sudden growth of usage of SQL Server in future?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.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.
  3. chetanjain04 Member

    Actually I am troubleshooting the foll. issue:

    The SQL server is not utilizing available memory. It is just taking about 150 MB of memory when observed through Task manager. I checked the memory related counters which show that the Buffer Manager target value is much higher then what is shown in total. There is only one query executing on the server currently which is copying a 60 GB table data from a SQL 2000 64 bit server using foll type of query:

    insert into [SQL2005 64 bit server].dbname.dbo.table
    select * from [SQL2000 64 bit server].dbname.dbo.table

    Is there anything configuration setting I need to take care of before executing this query. Why does SQL server not utilize memory of the server. The database sizes are ok.. no autogrowth or blocking is observed.


    Best Regards,

    Chetan

  4. thomas New Member

    I strongly recommend enabling the Lock Pages In Memory right:-

    http://msdn2.microsoft.com/en-US/library/ms190730.aspx

    This is the 64-bit equivalent of AWE. Without it, I have had problems similar to yours with 64-bit servers. This right only applies to Enterprise Edition SQL Server 2005. If you apply it to Std Edition and below it is ignored.
  5. joechang New Member

    do not rely on task manager for large memory usage
    the 32-bit versions did not report > 3-4GB correctly

    use the Performance Counters for SQL memory or something like that

    a single query to insert 60GB is not a good idea
    break it up into batchs of 2K-100K rows each
  6. MohammedU New Member

  7. chetanjain04 Member

    Hi,

    I do have the lock pages in memory privelege granted to SQL server. It is evident in Errorlog. Also, the AWE is not enabled. I checked the foll.

    select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Buffer Manager'

    SQLServer:Buffer Manager Total pages 64768
    SQLServer:Buffer Manager Target pages 3701160
    SQLServer:Buffer Manager Free pages 744



    Why is there such a big difference in the total and target values? Also, total server memory utilization in Task Manager is about 2 GB only.



    Best Regards,

    Chetan
  8. MohammedU New Member

    I believe you need to check "SQLServer:Memory ManagerTarget pages (Total amount of dynamic memory the server is currently consuming)" and "SQLServer:Memory ManagerFree pages (Total amount of dynamic memory the server is willing to consume)" counters to check the sql memory utilization...

    As Joe mentioned do not rely on Task Manager info...
    What is your max server memory setting?



    MohammedU.
    Moderator
    SQL-Server-Performance.com

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

    You can get information on physical memory by using
    SELECT
    (Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
    FROM
    sys.dm_os_sys_info
    .. in addition to the DMV such as : Sys.dm_os_memory_objects

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.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. thomas New Member

    quote:Originally posted by chetanjain04

    Hi,

    I do have the lock pages in memory privelege granted to SQL server. It is evident in Errorlog. Also, the AWE is not enabled. I checked the foll.

    select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Buffer Manager'

    SQLServer:Buffer Manager Total pages 64768
    SQLServer:Buffer Manager Target pages 3701160
    SQLServer:Buffer Manager Free pages 744



    Why is there such a big difference in the total and target values? Also, total server memory utilization in Task Manager is about 2 GB only.

    Best Regards,

    Chetan

    As Joe said, ignore Task Manager memory values for large amounts of RAM.

    Are you running queries that force SQL Server to use a large amount of RAM? You need to do this to get it to take the memory. A handy way is to run DBCC CHECKDB on a large database.
  11. satya Moderator

    Chetan
    You mentioned that there are no performance issues with the current configuration, but have you checked the execution of those queries with PERFMON, that is best method to assess the configuration. Alsohttp://www.sql-server-performance.com/gv_stress_test_lessons_3.asp fyi in order to simulate the stress test if you can allow on this live server.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.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.
  12. thomas New Member

    And is your Buffer Cache Hit Ratio healthy?
  13. chetanjain04 Member

    Hi,

    I think I have got the solution.

    First I changed the min and max server memory to 20480 and 28672 respectively

    Then I saw that the server is not even reaching 20 GB. So for testing I executed a select statement on a table with millions of rows. The memory started increasing sharply. This gave the idea that this is not a memory related issue.

    Hence, I checked up the network side. The network admin says that the bandwith at the switch end is very low. We have increased the same.

    Will let u know after executing the query once again.

    Best Regards,

    Chetan
  14. MohammedU New Member

    In sql server 2005 all configured memory will not be taken by sql as max server memory configured... it will be taken as needed....

    MohammedU.
    Moderator
    SQL-Server-Performance.com

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

    It is like that since SQL 2000 days, as referred using SYSMON is only best tool to get more information onthe resources usage and Task manager is only high level tool to monitor, do not assume all are correct over there.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.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. chetanjain04 Member

    Hi,


    I understand Task Manager is not the ver best for monitoring. However, just to update you I am also referring the sys.dm_os_performance_counters and perfmon of Windows.

    Anyways, the problem was there at Network End switch end. After increasing the bandwidth to 100 MBPS, the query has completed in about 1 hour which was expected.

    Thanks for your assistance.


    Regards,

    Chetan

Share This Page