Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2007
    Posts
    2,051

    Load on MS SQL server

    I have a server with MS SQL 2005 server as the Database server for shared hosting. I am not running anything on that server except MS SQL 2005 and MySQL 5.x. Lately, I am experiencing around 20-50% CPU load and over 2 GB memory being consumed by SQL server alone. I am not able to identify which database or query is the culprit. Can anybody suggest a way to find it?
    Prashant T.

    Don't run after Success. Run after Excellence and Success will soon follow.

  2. #2
    Join Date
    Nov 2007
    Location
    West Palm Beach, FL
    Posts
    275
    Prashant,

    MS SQL will attempt to load as much of the databases as it can in memory (if it's available) which is a good thing. If you have 10 200MB DB's getting accessed, then you'll use 2GB memory from SQL server alone. SQL is doing it's job and queries/requests will be accessed directly from memory vs. accessing the disk if possible. As available memory decreases, SQL will do it's best to manage the buffer retaining data accessed most frequently.

    What you want to look at is perfmon counters to determine the health of SQL:


    SQLServer:Buffer Manager

    •• Buffer cache hit ratio: This shows how often the data you request is
    already in memory. If this is below 90 percent, you can probably benefit
    from additional memory. Ideal values are above 99 percent.

    •• Lazy writes/sec: This shows how often the lazy writer thread is writing
    dirty pages to disk. Values greater than 20 indicate that more memory
    could help performance.

    •• Page life expectancy: This indicates how long pages are staying in
    cache, in seconds. Values less than about 350 would be one of the
    strongest indications that you need more memory.


    SQLServer:Memory Manager

    •• Target Server Memory (KB): This indicates how much memory is
    available for SQL Server.

    •• Total Server Memory (KB): This indicates how much memory SQL
    Server is currently using. If Total Memory is well under Target Memory,
    that’’s an indication that you probably have enough memory. However,
    you shouldn’’t use these counters to determine whether you don’’t have
    enough memory. In particular, if they’’re equal or close to equal, that by
    itself does not mean that you need more memory.

    In terms of what SQL requests/queries are consuming the most resources you really just have to do traces to found out. The problem with a trace is that unless the query is running right then you aren't going to catch it.

    Something like WhoIsActive can help you with this:

    http://sqlblog.com/tags/Who+is+Active/default.aspx


    Ultimately, the best tools are going to be ones that constantly monitor the SQL activity and will give you both real time reporting and historical reporting. There are two awesome tools for this, but they are both very expensive. If you have a few shared SQL servers they are are definitely worth it though. Even one license (moved between servers as you need it) would greatly help.

    SQL Sentry (Performance Advisor): http://sqlsentry.net/performance-adv...e-overview.asp

    Confio Ignite: http://www.confio.com/English/Produc...SQL_Server.php
    Confio IgniteFree: http://www.ignitefree.com/

    I've used SQL Sentry quite a bit and it's proved invaluable in determining 'problem' databases and who's consuming resources. Ignite looks very good as well. They also have a free version I would install that should help you as well. You'll miss some of the features, but it will probably give you a lot of insight into what is transacting and by who.

    Also, make sure you're optimizing your clients DB's by keeping the indexes and statistics up to date. This is often overlooked and can greatly reduce resources and improve performance. I run this SQL script weekly on all servers:

    http://ola.hallengren.com/


    There are many excellent, free SQL tools listed here:

    http://sqlblog.com/blogs/aaron_bertr...ql-server.aspx



    Good luck -

    Wayne
    Last edited by AI-Wayne; 03-20-2011 at 09:34 AM.
    Applied Innovations (www.appliedi.net)
    Microsoft Gold Certified Hosting Partner, ASP.net Featured Host.
    Specializing in Windows Hosting since 1999.

  3. #3
    Quote Originally Posted by prashant1979 View Post
    I am not able to identify which database or query is the culprit. Can anybody suggest a way to find it?
    You can use SQL server profiler to find out which databases use too much CPU resource.
    PremiumReseller.com Hyper-V SSD VPS USA London Singapore
    Reseller Hosting Cpanel PURE SSD CloudLinux Softaculous
    Windows Reseller Asp.NET 4.5 MSSQL SmarterMail Enterprise

Similar Threads

  1. High SQL load - supposedly...
    By chillax in forum Web Hosting Lounge
    Replies: 0
    Last Post: 04-11-2006, 01:40 PM
  2. Trying to load a backup SQL file into the table
    By BlakIce in forum Programming Discussion
    Replies: 5
    Last Post: 03-23-2005, 05:40 AM
  3. High Load, SQL connection
    By filuren in forum Hosting Security and Technology
    Replies: 15
    Last Post: 12-27-2004, 09:44 PM
  4. Replies: 0
    Last Post: 08-17-2004, 11:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •