SQL Server 2000 performance issues

32960 pts.
Tags:
iSCSI
Microsoft SQL Server 2000
perfmon
SAN
SQL Server performance
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you have a processor queue then yes, that means that commands have to wait for a CPU to finish what its doing before being able to preccess the next operation.

A page life expectancy of 4200 seconds is pretty good as that means that data is being kept in RAM for 70 minutes before needing to flush that data from RAM to make room for more data. You want this number as high as possible, same goes for the buffer cache hit ratio.

I would at this point look at the disks. How long are your read and write operations taking? How much data are you pushing to the iSCSI SAN? What else is being hosted on the same physical disks that your database LUNs are on?

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Labnuke99
    I guess I didn't really ask the question - what could be causing the perceived performance issue for the users?
    0 pointsBadges:
    report
  • Labnuke99
    Additional performance counters I don't understand if they are significant or not: log flush waits/sec - average 4 log flushes wait time - average 4 on the DB and 190 average on total databases (no other application databases on this server other than the application DB)
    0 pointsBadges:
    report
  • SuperCoolMoss
    Hello, Some questions: Is the application always slow or only when performing specific functionality? Do you have the /3gb switch set in the boot.ini file? Using PERFMON what's the queue lengths of the LUNs which hold the database and log files? Regards, SCM.
    140 pointsBadges:
    report
  • Labnuke99
    How can I see the disk queue length on the iSCSI SAN? Would I need to look at that information using SAN tools? I think there is quite a bit of other data on the same LUNS. The site has their ERP data on the same SAN. What are your thoughts also about having the transaction logs on the same SAN as the databases? Thanks for all of the guidance.
    0 pointsBadges:
    report
  • Denny Cherry
    You can look at read/write data from the Windows OS by looking at the physical disk counters. You want to look at the Avg Sec / Read and Avg Sec / write as well as the queuing. You should also look at this same info on the SAN to see what it is reporting as well.
    66,185 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following