SQL Server Performance

Interested situation with database

Discussion in 'Performance Tuning for DBAs' started by Xanthus, Mar 5, 2003.

  1. Xanthus New Member

    We're current running a search engine website using SQL Server 2000. There are huge performance problems with the database maxing out on CPU. Our search spider runs about 5-10 insert/update/delete stored precedures per second. The site then uses the same database for searching. This together causes our database to max out.

    I heard that if we indexed our database, it would not help since it constantly gets written to and the data changes to much. How can we improve the performance of this kind of setup? If we tried replication, wouldnt it just pound the other database with updates as well?

    The nature of our search results needs to be updated to the minute not to the hour or to the day. What can we do?
  2. Chappy New Member

    If your tables are not indexed this could be a potential source of performance problems.

    It is true that adding indices to a table will increase the work required to insert into the table, since it has to update the index also, but this is rarely anything to worry about unless you are inserting lots of records at once - sometimes it turns out better to drop the index and readd it after the insertions - not what id necessarily recommend for you since simultaneous searches are presuambly happening.

    these stored procedures which are running 5-10 times a second: what are they doing, are they affecting lots of records? Run an SQL Profile while the CPU is maxed out - also run performance monitor and monitor the cpu and disk activity. Id really consider indexing your tables.

  3. bradmcgehee New Member

    Like Chappy suggested, indexes are one of the keys to performance success, and they will also help to reduce CPU load. While there is some hit with them, the benefits outweight the problems. Also, you need to take a careful look at your queries and see that they are properly optimized, as this is often the biggest problem when it comes to performance problems.

    What you really need to do is to a Profiler and Performance Monitor analysis of your server to identify the bottlenecks and then drill down on your specific problems. The 5-10 inserts a second you describe is very small and SQL Server can handle many, many more.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page