Re: ms sql server grabbing 5Gb. Is this good nomal?
- From: Raphael Gomes <ralgomes@xxxxxxxxx>
- Date: Thu, 19 Mar 2009 14:50:44 -0700 (PDT)
On 19 mar, 17:53, Jeroen Mostert <jmost...@xxxxxxxxx> wrote:
Raphael Gomes wrote:
So I have a .NET site with a few rather large tables (the three main
ones at about 1 million records each). The hosting setup consists of
some Web Servers running IIS 6, being served by another box running MS
SQL Server 2005.
The web servers run smoothly enough, but even without much use (this
site has such a multi-server setup because it has spikes of use near
holidays) but the SQL Server is coughing up and dying about once a
day.
The guys at the hosting company show me the logs showing that the SQL
server is under heavy use (processor at almost +90%, 5Gb of RAM used).
I'm willing to bet good money that you're either missing critical indexes or
issuing inherently inefficient queries ("SELECT * FROM hugetable") or both.
Heavy processor and memory use are typical of table scans, and scanning a
table of a million records is... inadvisable.
I see... So, if I understand correctly these, I should filter the
selects ('SELECT fields I actually will use' instead 'SELECT *') and,
if I need to search a field (which I do) which is not the PK, it
should be an index.
I'm running SQL profiler trying to find out if there are some rogue
query causing trouble (I used linq for this project) but so far, I
haven't seen good benchmarks for sql server performance.
Check for queries which take an inordinate amount of time (anything over 10
seconds that's issued more than once almost certainly needs tuning) and
queries which take far greater amounts of logical reads than others. Focus
on optimizing these first.
Good advice! So far, all the queries run in a couple of seconds, but
some have about 200x more reads (as seen on SQL Server Profiler) than
others. I'm talking about 7780 reads for some selects versus 3, for
selects in the same table.
Are these numbers "normal" for a setup like this? (single sql server
serving multiple web servers, tables of about 1 million records each - so
far being used for simple selects, inserts and updates).
What's "normal" also depends on your hardware and your actual data needs --
what is "simple" to you is not necessarily simple for the server. However,
if your SQL Server consistently eats 90% CPU you're almost certainly doing
*something* wrong -- either the server is way underpowered or your database
design is wrong, with the latter being a lot more likely.
Probably, I did the mistake of using lots of settings out of the box
and then scalability struck back, badly. :-P
Now, all this said, this has nothing do to with C# so far, .NET and LINQ or
no. A far better newsgroup for questions like this would be
microsoft.public.sqlserver.server. But before you ask questions there,
searching the web for tips on optimizing SQL Server performance is likely to
be even more fruitful.
Indeed! Group found and added. I'll ask there, now.
Thanks for you answers. They really helped.
--
Raphael Gomes
.
- Follow-Ups:
- References:
- ms sql server grabbing 5Gb. Is this good nomal?
- From: Raphael Gomes
- Re: ms sql server grabbing 5Gb. Is this good nomal?
- From: Jeroen Mostert
- ms sql server grabbing 5Gb. Is this good nomal?
- Prev by Date: Debug a web service from an asp.net website application
- Next by Date: Re: How to encrypt/decrypt serialized data
- Previous by thread: Re: ms sql server grabbing 5Gb. Is this good nomal?
- Next by thread: Re: ms sql server grabbing 5Gb. Is this good nomal?
- Index(es):
Relevant Pages
|
