November 4, 2005 at 10:17 am
Newbie question...
Under that server manager --> properties --> etc...
The allowcation for memeory is set to manual and 2024...
The users are complaining about slow performance.
Will changing this setting to dynamic, or upping it have any effect? Good or bad? The server has 4gb installed
Thanks,
November 4, 2005 at 12:54 pm
What is your Edition: Standard will use 2 GB max anyway. Enterprise can use more.
Go to Management ->Current Activity window in Enterprise Manager and see in the Process Info what process or processes eat your resources.
Regards,Yelena Varsha
November 4, 2005 at 1:15 pm
It's standard edition...
I guess that means the software is the reason for the speed issue.
Thank you for the help.
Curtis
November 4, 2005 at 4:02 pm
Regarding performance
Look in the system event log for any hardware failure events.
Next, use performance monitor and look at these counters:
Network - Bandwidth vs bytes/sec - bandwidth should be much greater.
Network - Packet Outbound Errors and Packet Received Errors. Both should be zero otherwise you may have a bad NIC.
Counter - Physical Disk - Avg Disk Que Length, %Busy, %Read and %Write (this does NOT apply if the disks are on a SAN)
Counter - SQL Server:Buffer Manager -> Buffer Cache Hit Ratio
The % of pages that were found in the buffer pool without having to read from disk.
Counter - SQLServer:Access Methods - Full Scans/sec
- Number of unrestricted full scans of either base table or full index.
Counter - SQLServer:Locks ->Average Wait Time (ms)
- Number of lock requests that could not be satisfied immediately and required the caller to wait before being granted the lock.
For Buffer Cache Hit Ratio
. above 95% is excellent
. 85% to 95% is good
. Below 85% is a problem
Full Scans/sec - The closer to zero the better and should be non-zero only when running infrequent queries such as for reports.
Lock Average Wait Time should be close to zero.
For Physical Disk - Avg Disk Que Length, %Disk Time, %Read and %Write
if %Disk Time is > 70%, the io request are exceeding the disk thruput capacity.
If Avg Disk Que Length > 2 times the number of disks, the disks cannot keep up with the requests.
Also run: SELECT * from ::fn_virtualfilestats(default,default) to look at IoStallMS ( wait time). If there is a lot of wait time on the transaction log files, insure that this file is on its own disk with RAID-1. If there are multiple user databases, insure that each transaction log is on it own RAID-1 disk.
if there is a lot of wait time on the tempdb data or log, these need to be on their own disks.
Causes for high resource utilization:
1. Poorly written SQL
2. Missing indicies
After researching and solving SQL and indicies, if the problems still exist, more disks are needed.
There may be a particular table that needs to be on its own disk or tables and indicies should be on seperate disks.
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply