Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vgodfrey
Starting Member
1 Post |
 Posted - 2008-09-19 : 16:21:05
|
| Hello to everyone,First off, thanks for any help or patience you can offer, I'm new to the world of SQL DBA-hood (I got drafted, like many more before me) - I'll try to keep the dumb-questions to a minimum.Here's the scoop...It's a SQL2k DB running on a VMWare virtual server, it has 2 cpu's & 4GB RAM - (it's the back end for an application called SmartPath, if anyone's ever heard of it).Generally it's been running fine, I run the standard Maintnance Wizard jobs weekly with no problems.The MDF is about 8GB's and the LDF is about 7GB's.The problem: Occasionally the server will pin itself at 1oo% CPU usage, causing long wait times for the users.I'm looking for suggestions to improve performance, or at least prevent the 100% CPU usage issues. Could INDEX TUNING assist? I've used the INDEX TUNING WIZARD and had it report "no suggestions", perhaps the TRACE I used didn't have the necessary information?I'd really like some clear suggestions that I could attempt.Any help that anyone can offer will be greatly appreciated.Thank you,VannVann GodfreyTLSource AdministratorP 203.857.7524tracylocke.com |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-09-19 : 17:36:06
|
| I would suggest you start by running profiler for a while (an hour or so's good) during your busy time. Save the trace to a file and then once it's completed load it into a table on a dev system somewhere and look for the queries with the highest CPU usage.Once you've found 3-5 of the queries with the highest CPU usage, see if you can tune them. That may involve rewriting code to remove bad constructs (if you're allowed to rewrite code) or it may involve index tuning, or both. Run the queries in Query analyser against a test system and look at the execution plan to see where the bottlenecks are.Post here if you need help with any of the specifics.The index tuning wizard wasn't actually that great. Even if it does come up with suggestions, test them carefully before you implement them. There are cases where the indexes its suggested degraded performance.--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
|
|
Database Performance and Index Tuning Assistance