Re: SQL Server 2000 Hardware Recommendations?
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 07/09/04
- Next message: Timothy Ross: "Re: Recommendations for where to keep the data and logs"
- Previous message: Timothy Ross: "Re: Recommendations for where to keep the data and logs"
- In reply to: Lorax: "Re: SQL Server 2000 Hardware Recommendations?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 9 Jul 2004 13:38:33 -0400
I don't know of a comprehensive written resource that helps with the code
portion but here are some links that may get you started in determining what
the bottlenecks are:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
-- Andrew J. Kelly SQL MVP "Lorax" <folkfest@comcast.net> wrote in message news:8973f217.0407090739.4e21383@posting.google.com... > Thanks to the both of you for your input. We're using adhoc sql calls, > but the compilations/sec is very low, and the cache hit ratio is > nearly 100% (99.7%, I believe off the top of my head). So what you are > both saying has the ring of truth--that we really need to focus in on > how our SQL queries are structured. Unfortunately we don't have a lot > of experience in the area of optimizing SQL. Aside from obviously > searching the web for information, can you recommend any resources in > this area? > > Thanks again for your valuable input. > > Gary > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:<e72qCFRZEHA.3228@TK2MSFTNGP12.phx.gbl>... > > That's hard to say what is the cause but it soulds like you have some very > > poorly optimized sql code. Are you using a lot of cursors? Run a profiler > > trace and see what is taking up all the resources especially cpu. What is > > your cache hit ratio? Are you using stored procedures or adhoc sql calls? > > Check your compilations per second counters as well. > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Lorax" <folkfest@comcast.net> wrote in message > > news:8973f217.0407080849.434055df@posting.google.com... > > > I'm on the IS team of a medium-sized non-profit with international > > > reach. We're trying to make some decisions regarding our Web server > > > and database server as we expand our web site to have more dynamic > > > content. Currently the database server houses all data pertinent to > > > the organization (membership data, events, products, etc) in one > > > database (~2.2 GB) as well as the web site content in a separate > > > database (~40 MB). The web site pulls from both databases but hits the > > > content database more often. > > > > > > In > a nutshell, our database server appears to be struggling during > > > performance testing of the new Web site. We are trying to determine > > > whether we simply need new hardware, or if there are things we can do > > > to help MS SQL make better use of the resources we have. The hardware > > > is a COMPAQ ML370, 1266mhz Pentium III, 1gb RAM, RAID 5 with 3 HD > > > (10,000rpm??) and a COMPAQ Smart Array 5i SCSI controller. The OS is > > > Windows 2000 (standard) running Microsoft SQL 2000, SP 3a. The Web > > > server is a 2.8ghz Pentium IV with 2.5gb RAM, RAID 5 with 3 HD (15,000 > > > rpm??) running Windows 2000 standard and IIS 5.0. While stress testing > > > our web site under a moderate load (simulating approximately 20 > > > simultaneous users), the database server processor tends to max out > > > and stay that way for the duration of the test. Memory and disk access > > > appear to remain fairly stable -- there isn't a lot of paging going > > > on, and the disk queue doesn't escalate much if any. The Web server > > > shows spikes in processor use, but appears to be coping well. However, > > > under a heavy load, a sql-heavy page can take as long as 90 seconds to > > > load! We've been assuming that the network is not the issue, as the > > > servers are communicating over a gigibit backbone and while we've > > > identified aspects of the ASP code that we can optimize, the database > > > server seems to be a large part of the problem. > > > > > > We've reviewed our SQL configuration settings, and they appear to > > > align with the best practices, which in our case are the default > > > settings for SQL 2000. We have rebuilt our indexes, and have > > > defragmented the hard disks on both the database and Web servers. > > > This, along with changes to the structure of the Web pages themselves, > > > has led to improvements, but the processor on the database server > > > seems to be groaning under the strain, and pages are still taking an > > > unacceptable amount of time to load. > > > > > > What else should we be looking at? Are there steps we could take to > > > minimize the load generated by client/server and Web-related traffic, > > > or specific performance counters that would help us to identify the > > > problem? Do we just need to look at getting some new hardware? If new > > > hardware is unavoidable, is there anyone running a similar environment > > > who could suggest what minimum requirements we should be looking for? > > > Any suggestions would be much appreciated!
- Next message: Timothy Ross: "Re: Recommendations for where to keep the data and logs"
- Previous message: Timothy Ross: "Re: Recommendations for where to keep the data and logs"
- In reply to: Lorax: "Re: SQL Server 2000 Hardware Recommendations?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
