SQL Server Performance

Memory on server

Discussion in 'Performance Tuning for DBAs' started by cb, Jan 22, 2003.

  1. cb New Member

    I have 5 databases of 10 gb each in size and we do have performance issues at certain times and from task manager I can see memory for SQL server 1.8gb from physical ram 2.5 gb.

    Any insight to solve the issue is helpful.
    Thanks
  2. satya Moderator

    As a first hand information capture the OS counters alongwith SQL Server counters by using PERFMON which will give insight for the issue during that time.

    Do check for what kind of activity on SQL Server during that slow performance and if its acceptable then there is no need to worry, but it is a sign that you may need to be thinking about adding RAM before it goes out of hands.

    Also check how much memory allocated to Pagefile.

    HTH


    Satya SKJ
  3. bradmcgehee New Member

    If your server is only using 1.8GB out of 2.5GB RAM, this indicates to me that your server has plenty of RAM. You can verify this by checking your buffer hit cache ratio. If it is 99% or higher (and I bet it is), then your server has enough RAM and it is not your performance bottleneck.

    As satya has suggested, your next step is to identify the cause of your poor performance. There are many possible causes, and from my website, you can see that there are hundreds of tips you can use. I suggest you first use Perforamance Monitor to help identify problems, then follow-up with Profiler. You can find many tips and articles on how to use these tools on this website.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. cb New Member

    Thanks satya and brad, I got the key to solve the problem. could you please let me know what kind of counters I need to collect.
  5. satya Moderator

  6. bradmcgehee New Member

    As satya has suggested, the url is a good place to start. Your focus as first should be on the big picture, trying to identify major bottlneck problems in the areas of CPU, I/O, Network, and Memory (although memory is probably not your problem). Once you have identified the biggest problem, then you can refine your trouble-shooting technique to find out why a particular bottleneck is being caused. For example, an I/O bottleneck could be caused by poor indexing or poorly written queries. Once you start your first analysis, if you have specific questions, we will be glad to help.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  7. cb New Member

    thanks again folks, you are great.

    I have seen in most of posts referring to profiler for slow queries, what should I do after capturing the trace and tool to get statistics for slow performance.
  8. satya Moderator

    INDEX TUNING WIZARD is a tool where you can use that PROFILER trace for a better recommendation on indexe.

    But always consider adding more memory to the server once you found memory counters are spiky.

    HTH

    Satya SKJ
  9. bradmcgehee New Member

    Once I run the Index Wizard, and get all I can from that, my next step is to look at each long running query in Query Analyzer and view its execution plan. This is not easy to learn how to do and takes some experience, but there are tips and articles on this website on how to do this.

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

Share This Page