SQL Server 2005 Enterprise Edition SP3 Performance

585 pts.
Tags:
SQL
SQL performance
SQL Server
SQL Server 2005
SQL Server 2005 SP3
SQL Server Enterprise Edition
We have SQL SERVER 2005 SP3 Enterprise 32-bit running on a windows server 2003 Enterprise 32-bit with 32GB of RAM and 8 dual core processors. Our CPU, RAM, and I/O are always through the roof and the application is always running slow. We use AWE for buffer cache the maximum memory is 28GB, the PAGEIOLATCH_SH is always high and the procedure cache is always around 700mB. Durring production hours we have over 6000 connections to sql server (500 users).

1. What is wrong here?

2. Do you think changing the "Parameterization" database option to forced would help?

Please take a look at the both images for the PAGEIOLATCH_SH and the Procedure Cache. Thank you for your help.

Procedure Cache

SQL Wait Stats



Software/Hardware used:
SQL SERVER 2005 Enterprise 32-bit, Windows SERVER 2003 Enterprise 32-bit

Answer Wiki

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

based on the second screenshot you’ve got a lot of database tuning that needs to be done. This will require making index changes to improve your query time. This will reduce the SOS_SCHEDULER_YIELD and will probably reduce the disk IO access.

Discuss This Question: 7  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
  • Sibir1us
    Yes, Denny is right: the best approach is to start looking into which queries are the most resource intensive and tune them. You can start by looking at Profiler and by sorting the queries by Reads, Writes and so on. Then look into missing indexes, look into your disk configuration as well.
    20 pointsBadges:
    report
  • Rick Martinez
    We have our database in a SAN that has 16 drives and is a RAID 10 so only 8 drives are used to store data files. All databases are spread out into diferrent drives in the array even log files. Are we doing something wrong here?
    585 pointsBadges:
    report
  • Kccrosser
    What Mr Denny is saying is that your database probably needs some indexes added. Imagine a 1 Million row table with no indexes. Now do a simple "select ... from table where columnx='value'". With no indexes on the where clause column(s), that query will require the database engine to physically read EVERY row of the table every time the query is executed. That will result in huge disk, memory, and paging overhead. Now create an index on the column. Thereafter, each query will have to look at the index (generally a logN function, so maybe 10 reads of index pages) and then only physically access a couple of records from the main table. Result = disk reads drop by about 10000:1, paging vanishes, and memory is free for other queries. As Sibir1us notes, you can use common Profiler reports (or other tools) to look at your queries that are being run and see which ones are the most resource-intensive. Start with the ones with the most disk reads and work down from there. If you can take a query that is averaging say 100,000 disk reads and cut that down to 1-10 reads, you will find a lot of system performance numbers will get better fast.
    3,830 pointsBadges:
    report
  • Rick Martinez
    Thank you for the information Kccrosser. Thank you all for your help I think I know where to start now.
    585 pointsBadges:
    report
  • SSNewbie
    If you haven't already done so, install the SQL Server 2008 R2 Management Studio (free in the Express edition) and use the new-in-SS2008 Activity Monitor to detect the most intensive queries. It's easier than Profiler and if you don't have any other tools to help this will get you considerable help in seeing what's going on real-time and it's filterable and columns can be sorted. I use it frequently - hope you find it a help.
    55 pointsBadges:
    report
  • Rick Martinez
    This is a SQL SERVER 2005 database would the activity monitor work on that database?
    585 pointsBadges:
    report
  • Hlx
    Yes - the activity monitor will work against 2005 serversDBs.
    690 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