Thread: Load on MS SQL server
03-20-2011, 05:30 AM #1Eternal Learner
- Join Date
- Jul 2007
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.
03-20-2011, 09:25 AM #2Web Hosting Guru
- Join Date
- Nov 2007
- West Palm Beach, FL
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:
•• 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.
•• 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:
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:
There are many excellent, free SQL tools listed here:
Good luck -
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.
03-21-2011, 12:15 AM #3PremiumReseller.com Hyper-V SSD VPS USA London Singapore
Reseller Hosting Cpanel PURE SSD CloudLinux Softaculous
Windows Reseller Asp.NET 4.5 MSSQL 2012 SmarterMail Enterprise
By chillax in forum Web Hosting LoungeReplies: 0Last Post: 04-11-2006, 01:40 PM
By BlakIce in forum Programming DiscussionReplies: 5Last Post: 03-23-2005, 05:40 AM
By filuren in forum Hosting Security and TechnologyReplies: 15Last Post: 12-27-2004, 09:44 PM
By 1-800-HOSTING in forum Dedicated Hosting OffersReplies: 0Last Post: 08-17-2004, 11:52 AM