April 22, 2009 at 5:51 am
Hello
i have a little problem with a sql server....
Problem is that Cpu peaks 100% and memory usage goes up until it reaches 100% even the swap file gets larger.
Yesterday memory usage on sql service was ~50gb and swapfile ~54gb cpu pending between 30%-90%.
Server data
Server have 4 dual core 2ghz cpu and 72Gb of ram.
Raid settings and file settings for database files seems ok.
~8 databases located on server, 7 small and 1 ~65gb.
95% of server usage goes to the big DB. The other db's have very low or almost no activity at all.
Memory max settings is set to 70gb.
Any hints on sql settings ?
Any hints on how to set the swapfile ?
Or any other suggestions ?
atm swapfile is set to 1.5*memory (to me it seems a bit big).
Im quite new to this type of sql server problems.
(sorry for my bad english)
April 22, 2009 at 6:01 am
Can you please give more information before I can answer your questions.
Which version of SQL Server is it running on I mean to say 64 bit or 32 bit?
what is the min and max memory configured for sql server.
What are the queries that are running in SQL Server during which CPU usage is more.
IS CPU is constantly pegging more than 70% all the time or its just a spike at times?
April 22, 2009 at 6:28 am
SQL server 2005 sp2 64bit.
Min memory 0 and max 70gb. AWE unchecked as it should...
Memory usage is strange if i reset the server it starts @ 0 and works upwards until it
reaches 100% (Sqlservice). It never seems to empty the memory.
It's like this...
monday starting reset server mem 0% cpu 0% then it work upwards and during peak hours it's very high.
When people goes home ofc it gets lower but if you look @ a graph on memory usage it never gets down, if
usage is @ 20% when peak hours is over it kind stays @20% so in a few days it's @ 100% again.
Average Cpu usage raises beside memory usage.
Regarding query, i did some report checks on long running query and it made me puke, very long select statements with multiple joins and lots of where conditions. Well first i thought this shouldn't be to big problem but then i looked in to the design and found 70 tables and only 6 pkeys and no fkeys.
My own thought is that poor db/application design are the main problem but the developers doesent wanna listen to me, so i thought on asking here for any opinions and suggestions on settings that might help....
Regards Kenneth
April 22, 2009 at 8:18 am
Hi Kenneth,
Solution to your problem is not that easy. To troubleshoot this first of all what you can do is you can capture query plans for all the queries that run during peak hours analyze them, if they are doing table scans, create proper indexes so that table scans can be avoided.
Please start doing this if possible and I think once you start working on this you will see definte improvement.
Also for memory, as max memory is set to 70 GB, once SQL Server uses that memory it will not release to OS as SQL has reached its max configured value. But I can say sql server has reserved memory but is not using that much of memory.
April 22, 2009 at 8:42 am
thx for fast awnser. I will start (when i have more time) to analyze the querys, start logging with perfmon and profiler.
Regards Kenneth
April 22, 2009 at 9:46 am
Hi Kenneth,
Sounds like you have found the source of your performance problems in that there are a number of very poorly performing queries, perhaps as you say, as a result of underlying database design issues.
I’ve seen this exact same scenario countless number of times and even adding a handful of indexes can have an incredible positive impact on the performance of a platform.
You say that the development team do not want to know but what if you were to tell them you could significantly increase the performance of their platform if they were to consider your recommendations. Why not offer to implement the required modifications yourself as a gesture. In the end, everyone involved benefits and gets props from management.
I would start by looking at and evaluating the database design/structure of your problem databases. Begin with the basics and work your way up. Does each table have primary and foreign keys defined, are they indexed etc.? Primary and foreign keys are an excellent place to start as these are very likely to be involved in a number of join statements within your poorly performing queries.
Once you have done this, take a look at the missing index DMV’s to see what SQL Server recommends you could do to improve performance.
Hope this helps but feel free to contact me directly if you need further assistance.
Cheers,
John
April 22, 2009 at 5:35 pm
April 22, 2009 at 8:51 pm
kenneth.holm (4/22/2009)
the developers doesent wanna listen to me
😀 I suppose that why We the DBA's are there to straighten them up.
Take it as a challenge, improve the database design by adding proper Foriegn keys and indexes, Use them in your join predicates and show them the results before you made the improvements and after you made the improvements.
April 22, 2009 at 11:34 pm
MANU (4/22/2009)
Check-->http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/MJ
Looks like a sweet guide.
Solving this issue seems like a neat challenge, so i told the devs that my services are available...
Big thx to all for nice good and fast replies
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply