July 27, 2007 at 12:41 am
HI all,
Please could you help. We have upgraded to SQL 2005 and are having some difficulty with the peformance on the server. The server is a 64 bit 8 processor (itanium) machine with 32GB of Ram.
When running a simple update query with sub select statements, the CPU shoots up to 100% usage. The IO's also then drop and it is like the server is standing still.
Any recommendations
July 27, 2007 at 1:40 am
As a first step, update the statistics and then run again that query.
You should check the wait types and find where the bottleneck is. As a general rule for sql 2005 x64, set the max and min instance memory - for your case, the recommended min 1024, max 30720; make sure sql server service has "lock pages in memory" permissions.
July 27, 2007 at 2:01 am
Thanks a mil.
Lock Pages is enabled. Have changed the min and max memory, restarted and it still reaches 100%. When running Windows Server 2003 (Datacentre edition) should one enable PAE in the boot.ini file
July 27, 2007 at 2:17 am
You should not do this; sql 2005 x64 has got already AWE enabled; this is the reason for "lock pages in memory".
July 27, 2007 at 2:24 am
Hi,
Thanks for the help
Just for a backup. The SQL server is started but the Local System. Who do I need to add into the group policy to make sure everyone has the capability of locking the memory
July 27, 2007 at 7:52 am
In local security policy (under administrative tools on the server) you should add the account that SQL is running under.(thats the only one you need to add unless your starting the service with different credentials all the time.. which doesn't make sense)
July 27, 2007 at 10:41 am
Can you post the query as well as schema and row counts of any tables and views used?
Regards,
Rubes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply