SQL Server Performance

SQL Server 2000 - low performance matter

Discussion in 'Performance Tuning for Hardware Configurations' started by emvipi, Nov 23, 2007.

  1. emvipi New Member

    Hello!
    I am new in this matters. I just hired in a company and they have a SQL Server based on:
    Windows 2003 Standard Edition R2
    Sql Server 2000 SP4.
    The system contains 2 dual core processors and 4 Gb amount of RAM.
    The thing is that as a configuration it looks pretty ok. But, when the users are fetching reports it runs very very slowly. Though in task manager the RAM used is cca 1.8 Gb, and the processors no more than 15%.
    I looke in sp_configure and it seems ok:
    maxdop = 0
    max server memory = 3072
    ...
    Can you please help me with some configuration so I can use it fully capacitated ?
    Thank you very much!
  2. techbabu303 New Member

    Hi,
    It would be good if you can run profiler trace to check wht kind of queries are taking more than 5 seconds and then run query plan to check if you can optimize.
    Please search the forums on exact steps since this has been discussed quite a lot here.
    Cheers
    Sat
  3. martins New Member

    Hi,
    The first thing to note is that task manager is not the best place to see how much memory and cpu SQL Server is consuming. You need to use perfmon to get accurate info regarding this, and you will see there are SQL Server specific counters. Also read the articles on this site to know which counters are the best to look at, like the cache hit ratio and disk queue length.
    The second thing I can recommend is to run a trace (using SQL Profiler), to see which queries take the longest to execute. You will then be able to troubleshoot the stored procs or queries that take a lot of time.
    Poor performance doesn't always mean that you have hardware issues. You might have to relook your indexes and even improving the way the stored procs or queries have been written in order to improve performance. There are quite a few articles and posts on this site that will point you in the right direction, so I would suggest you read up as much as you can on the subject as it is not the easiest.
    Hope this helps.
  4. ranjitjain New Member

    Tuning is not just limited to server but tuning also needs to be done from coding and database designs and other indexing objects.
    Trace all the long running queries and then look for tuning them as well. Check whether all indexes are in place and if they exists then are properly optimised and defragmented. How often you rebuild these indexes

Share This Page