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 |
|
IPearson
Starting Member
1 Post |
 Posted - 2007-03-28 : 05:49:20
|
| Hi,Every once in awhile we experience significant delays in the response times of all of our stored procs. This creates time-outs on our application server. There is no increase in the number of requests coming from our app server, but for some reason our stored proc calls take a lot longer to execute on the back-end, this resolves by itself within 5-20 minutes, with no error messages coming from the db. Something is bottlenecking the CPU. It is not a specific stored proc that eats up the CPU, we already looked into that possibility, but is there a tool that might help us better narrow this down or does anyone have any suggestions.Thanks. |
|
|
MohammedU
Posting Yak Master
145 Posts |
Posted - 2007-03-28 : 14:22:10
|
| Run the sql trace/profiler to see execution time, cpu time and recompiles...some time recompilation will cause this kind of issue.You want to spend money there are many tools out there to for monitoring...MohammedU |
 |
|
|
rlaubert
Yak Posting Veteran
96 Posts |
Posted - 2007-03-28 : 16:12:40
|
| I use four tools to monitor my servers and operations. First all sp and canned queries I have a baseline execution plan (SQL Query Analyzer), second I have a baseline on the server under normal load using Performance Monitor, Third Profiler to determine who, when, where, and what is using sql server and finally I run Index Tuning wizard on both traces and procedures/cann queries. These have always told me what I needed to know and I have been working with SQL since 6.0.With your current situation, I will assume you do not have a baseline. But start Performance Monitor up and record the operations when you are not having the issue. Save the perfmon settings so you can quickly restart capture when you do have a problem. I would capture information about the CPU, Memory, Physical Disk, Work Queues, SQL connections, database, memory management, processes and anything else you think may be an issue. While waiting for that stuff, get the execution plans for each of the procedures, look for things that can cause slow performance such as table scans. Do you have any sp_recompiles scheduled? This would cause performance issues while the procedures are recompiling. How out of date are the stats? Is this issue after a bulk load? Start up Profiler and see what is happening inside of SQl both under normal conditions and when you have the problem. It may lead you in the right direction.Raymond LaubertMCDBA, MCITP:Administration, MCT |
|
|
|
|
|
|
|
|
Monitoring-Tools for Stored Proc calls