Running SQL Server 2000, SP4 on Windows Server 2003 R2. 4GB RAM; 3GHz dual-processor, dual-core. The databases and logs are on an ISCSI SAN. This is the original implementation design. Since the beginning the users have said the system does not perform as well as they would like.
The database application is a manufacturing information system. It has product information in the form of pictures, PDF's for work instructions, quality control info and such. There is not really a very large number of users (<50) and they are all located on the local LAN. I have read some of MrDenny's excellent SQL tips and have been watching the server performance using perfmon over the past couple of days and have some questions about specific stats. What do they mean and above what value should we be concerned and address a problem?
%processor time for the SQL process matches the total processor % time - so nothing else really seems to be hitting this server. The average total processor utilization% is about 58% over an 18 hour period (54% user and 4% privileged). With 23% of the time between 50-60%, 15% between 60-70%, 15% between 70-80%, and 12% above 80%. Is the application processor-bound?
Another consideration is the Processor Queue length. Over 20% of the time the processor queue length is above 4. Doesn't this mean that the system is waiting for each processor/core to complete a task and instructions are building up in the queue?
What is the typical value for the SQL buffer manager page life expectancy? On this system, the majority of the time is spent under 4200 secs. Does this mean that a lot of disk reads are happening? The thing about this though is the buffer cache hit ratio is above 95% for the majority (98%) of the time.
It also seems like the memory pages/sec is low and page file usage is low. So, it doesn't seem to be a memory constraint issue either.
Thanks for your assistance on this puzzling issue.