November 13, 2005 at 7:45 pm
Hi every one,
We have checked our aplication for queries and there is nothing wrong with the code.But still our response time is too long and server is responding very slow.
When i see there is still 4 GB of Ram free not been used in the process.
We have 8 GB of RAM + 4CPU +400 users
Pls help me .
Thanx
from
Killer
November 13, 2005 at 11:39 pm
Do you have AWE switched on? What's sql's memory settings? Dynamic or fixed maximum?
What's the CPU utilisation like? Disk IOs? Network card queue length?
Locking? Index usage vs table scans?
There's a number of reasons why the resonse could be poor without there been memory a bottleneck
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2005 at 3:07 pm
If you are using sql server 2000 standard edition then
memory max is 2 GB. only 2 GB of your 8 GB will be of use for sql server.
Dynamic memory setting is recommended by MS unless you have known
reason for setting otherwise.
If you are using Enterprise edition then Configuration setting should help.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
November 14, 2005 at 3:37 pm
Is your slow response something new? Is it only slow on this one specific server? From a memory perspecitve, it does not look like you have AWE enabled. With that said, you have 8 GB of RAM and SQL Server only has access to 2 GB. You need to enable AWE and add the /3GB and /PAE switchs to your boot.ini file. To enable AWE:
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
RECONFIGURE
GO
I also recommend setting your 'max server memory' value to 7 GB. By default, AWE will use up all available memory except for 128 MB, which it reserves for the OS. Setting the 'max server memory' value allows you to allocate more RAM for the OS.
SP_CONFIGURE 'max server memory', 7168
RECONFIGURE
GO
November 14, 2005 at 6:54 pm
Hi,
AWE option is enabled . Yes there are few locks on the tables but that is normal for our application.
We have enterprise edition and we have setup dynamic memory allocation.The CPU utilisation is normal.
from
Killer
November 15, 2005 at 10:55 am
One thing to look into would be your disk IOs. As your tables grow for the same queries, you may have more paging activity, where at one time your queries were running in memory, now the temp results or sorts are too big and are going out to disk.
November 16, 2005 at 11:57 pm
Yes ,We have number of tables in buffer (memory) but that is our need because these tables are used number of times most freq.
But they have only few records as they are master tables.
"The temp result is not running out of disk.
from
Killer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply