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 |
|
chadbryant5
Starting Member
32 Posts |
 Posted - 2005-11-22 : 11:50:45
|
| We have an asp.net C# application that loops through a collection of about 10 items. For each item, a stored procedure gets called which does a lot of stuff for that item. The stored proc really should be broken down into many procs so instead of one call from the C# application ,there are more like 8 or 9. This would make it much easier to debug for me since I'm more of a C# developer than a SQl Server developer/DBA. I could tell at which piece of that process the timeout error occurs. However at present, I don't have the time to rework that code, so I need to find a way to determine where in the stored proc am I running into problems so that I can fine tune a query or rewrite it or whatever needs to be done. I just don't have a good way to debug the stored proc to figure out exactly where it is getting to before the timeout occurs.Any ideas would be greatly appreciated!!!!!ThanksChad |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-11-22 : 12:56:59
|
| Use Sql Profiler to trace the sql being executed. You can filter on execution time to locate the long-running script.Also, take the the stored procedure exec statement out of the app and put it into Query Analyzer. You can insert some print statements in the stored procedure code to see how far it gets before timing out.Nathan Skerl |
 |
|
|
chadbryant5
Starting Member
32 Posts |
Posted - 2005-11-22 : 14:20:09
|
| Thanks.I used profiler but it just showed the call to the stored proc and that is the last I saw...I didn't see anything related to the individual T-SQL statements being executed inside the stored proc. Is there a way to see more detail about what is going on inside of a call to the stored proc from within profiler?Thanks. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-22 : 15:47:38
|
| If you setup the proper events in the Profiler you should be able to see what you are looking for. Click on the events tab and move over all of the Store Procedure events and all of the TSQL events. You can also capture the command you have now in the profiler (exec sp_whatever param1, param2) and paste into the Query Analyzer and do a show Estimated Execution Plan, and it will show you its guess of what it will do to process the stored procedure and where the bottleneck would be. If the stored procedure creates #TEMP tables, then you will be out of luck because it can't estimate anything since those doesn't exist yet. However, if you turn the right events on to trace in the profiler you can see where it ends up locking up.If you go to your Enterprise Manager and lookup the stored procedure that is being referenced and just paste in here, we might be able to give some suggestions as to what could be causing such problems.You can also change the Connection Timeout limit on the server to 0 (unlimited) so that the stored procedure will be able to run until completion in the short term until you resolve the issue. The Query Analyzer will default to 0 as a timeout limit, so that you could paste the command in there and turn on Show Execution Plan (instead of Estimated) if the stored proc does use #TEMP tables, and then run it and see where the bottlenecks are, which of the 10 parts takes the most time. |
|
|
|
chadbryant5
Starting Member
32 Posts |
Posted - 2005-11-22 : 16:17:02
|
| Thanks for the tips. I will add all the sp events and tsql events in profiler and see if that helps. I can also run the sp in QA, but the problem is that the C# code does something like this:1. Create dynamic snapshot table from a datawarehouse to just hold data needed for the analysis (what our users are creating) being created.2. Get back a list of Analysis categories3. For each category call a data build sp (the one with the problem, and yes it builds a temp table).4. if any exceptions are thrown do not commit, rollback all of the above.So I can capture the sp call in profiler to run step # 3 above but step #1 is has not run so it won't work. I could do step 1 too I guess from QA so it is done and committed and then do step 3 in QA.Let me try all of this and let you know the results. Thanks again!!Chad |
|
|
|
chadbryant5
Starting Member
32 Posts |
Posted - 2005-11-22 : 17:38:23
|
| Thanks, I figured out my problem. Profiler, with the right events added, gave me an abundance of info!Thanks!! |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-22 : 18:12:36
|
| Wooo-hooo. Now you are cooking. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
timeout problem, how to debug?