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 |
|
anolis
Starting Member
14 Posts |
 Posted - 2004-04-08 : 16:59:56
|
| Hi!The case:server: 4 cpu's, 2gigabytes memorydatabasesize: 2 gigabytes (SQL-Server 2000)transactions: 3 per second (24 hours a day), most of them select and inserts.The database is on 1 filegroup.I optimized the indexes with the index tuning wizard (with a trace file)The database contains many stored procedures, that where created a long time ago.Statistics are generated, the indexes are rebuild every night.The problem: the cpu's are on 100% almost continuously.Question: Will recompiling the stored procedures do any good?Anyone? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-04-08 : 19:16:22
|
| I doubt it.What is the setup of your I/O subsystem?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-08 : 22:57:55
|
| And have you run Performance/System Monitor to determine if you have a hardware bottleneck? If you haven't, I would suggest running it with the CPU counters mentioned at sql-server-performance.com. You might have reached the limit of your hardware.Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-08 : 23:57:21
|
| Is this only running SQL Server???Because this isn't very many transactions: 3 per second (24 hours a day).What are the 4 cpu's?You do really need to run profiler and performance monitor. Run the profiler and select Stored Procedures(StmtCompleted) and RPC(StmtCompleted) Look at everything at first, then if you see anything big, filter out items. You can look at things with a big impact on your system by putting filters on the Reads/Writes/Duration to start with.Do you see anything running out of the ordinary?Also go to task manager and see what's taking up the most CPU. Is it strictly SQL Server?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-04-09 : 01:02:42
|
| I doubt you have an I/O bottleneck if your system is at 100% cpu utilization. Usually, an I/O bottleneck throttles the CPU because it cannot get the data it needs for processing.You need to break out SQL profiler and generate a profiler trace. For example, I would be looking for the following:1. SQL that takes more than 1000 milliseconds to complete2. SQL that reads more than 5000 8K blocks3. SQL that writes more than 1000 8K blocks4. SQL that takes more than 3000 CPUYou should also look to see if you have deadlock conditions occuring.http://www.sql-server-performance.com is a good resource for information on using profiler and other performance issues.-ec |
|
|
|
anolis
Starting Member
14 Posts |
Posted - 2004-05-04 : 13:13:19
|
| Thanks for all the replies!I took some while for me to react... sorry about that.Anyway, the problem was in the application that uses the database.Its solved now, but I learned some more about finetuning thanks to your advices.Thanks again!M.C.A.D. for .NET |
|
|
|
|
|
|
|
|
100% CPU