April 26, 2008 at 11:52 am
Hi All
I have got a 64 bit SQL Server 2005 running on Windows 2003, i have some CPU issues, i have checked the best practice readings, its seems to be fine expect there were some problems with the Indexing stuff, i have got a doubt, i have got 5 GB RAM allocated to SQL server on a 6GB machine, i am planning to switch on AWE, if i need to do that i need to enable Page locks on windows, on page lock i have to allocate logins, when enabled for certain logins, i can see that there are some jobs failing saying the job hasent got rights, i used SA login to complie those SP's,
Other thing is , will enabling AWE on a 64 bit help on CPU performance??
Please suggest me on this
Cheers
Suji
April 26, 2008 at 1:20 pm
You don't need to enable AWE on a 64-bit server. That feature is for a 32-bit machine, to allow it to address the excess memory (if any) installed on the server.
reference:
SQL Server 2005 Books Online (September 2007)
awe enabled Option
http://msdn2.microsoft.com/en-us/library/ms190731.aspx
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 26, 2008 at 8:24 pm
Are you running Enterprise Edition? If not, locking pages in memory won't do anything either - since Standard Edition cannot use it.
For your problem, what is the minimum memory setting?
What else is running on that server (SSIS, Reporting Services, etc...)?
If you have either SSIS and/or Reporting Services running, you need to allow enough memory for those applications in addition to reducing the min memory configuration.
Jeff
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2008 at 5:32 am
Thanks, i have got a standard edition, mimimum memory is 0 and max is 5 GB, i havent got any other services running.its only SQL server, is there any other reason, that makes CPU high??
Cheers
Sujith
April 28, 2008 at 2:10 am
Suji,
Try to use max OS Support memory. Refer the following site:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Try to alote 3 gb to sql server and rest for the other application on the server and see.
Also check for the CPU bottleneck in perfmon.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 28, 2008 at 2:21 am
Truely, you should not allocate all your memory to the SQL Server process, other important processes will hang the Server when a critical process fails to get memory due to a heavily loaded SQL Server during peak hours.
What are your OS and hardware configurations?
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 28, 2008 at 3:27 am
What makes you think this is a memory issue?
CPU utilisation is generally the result of operations - calculations, aggregates, set ordering ... I'd recommend just running SQL Profiler and checking out what queries are running.
April 28, 2008 at 4:39 am
yes you are surely right, but to utilize the CPU, your code segment has to be there in the primary memory....the hit/miss ratio also increases the CPU usage......for each miss, a memory read, a memory fetch, and a memory load CPU cycles are added, and parallel processing cannot be done....this can become significantly high if the no of code lines pre-fetched becomes low......
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 28, 2008 at 8:00 am
We have found on our 64 bit servers that setting MaxDOP to a lower value not to exceed the number of physical processors helps reduce CPU utilization. You may want to check your wait stats to see if parallelism is a problem. You may wnat to also try increasing the threshold for parallelism.
April 28, 2008 at 11:32 am
Thanks for ur posts
My server Configuration ( its a Virtual Server)
MS Windows 2003 Std Edition (64 Bit) with 4 Processers (AMD 2.81 GHz)
SQL Server 2005 (64 Bit).
I tired to allocate 16 GB RAM to windows with 5 GB to SQL , still i had the same CPU issues , so i had to reduce memory back to normal )
I think that i cant try parallelismsince i dont have Intel processers ( i might be wrong), please let me know, i have recompiled all tables and views and SP's, i have to reindex all the defragemented tables.
If i can check parallelism, please let me know how to do that
Cheers
Suji
April 28, 2008 at 1:11 pm
you really need to have a good read of BOL, hire a DBA or attend a training course!
Paralleism is a result of ( usually ) poor queries that are not optimised that produce ineficinet plans. Alter the setting to be equal to half the number of cores you have to start - it has nothing to do with intel.
std x64 does have a few memory issues see http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/06/configuring-windows-2003-x64-for-sql-server.aspx for a start, although I use x64 ent.
There are a number of critical o/s patches you need to ensure sql server works correctly, you also need to be probably at at least CU5 for sp2 on sql server.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 29, 2008 at 3:14 am
Hi Colin, i am refering to hardware parallelism, which iam not able to do on AMD, hope that wasent relevant to this discussion, i am new to this field and learning on SQL 2005. thanks for the link, i will have a read on that, keep posting
Cheers
Suji
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply