SQL Server Performance Forum – Threads Archive
SQL 2005 64 bit – sp_configure
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
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.
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
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.
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
Yes, you need to do Lock Pages In Memory for 64 bit too…but you don’t need to enable AWE for 64 bit… Check the following Slavo blog…
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx http://support.microsoft.com/default.aspx/kb/319942
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
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
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.
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.
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
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.
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.
And is your Buffer Cache Hit Ratio healthy?
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
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.
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.
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
]]>