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 |
|
solai
Starting Member
6 Posts |
 Posted - 2006-05-19 : 14:01:16
|
| Hello:I am fine tuning a process that currently runs for over 27 hours.I put measurements and nailed down to the problematic storedprocedure that may have contributed to the overall slowdon.Interestingly, if I simply ran the content of the procedure on the SQL Query Analyzer, it rans pretty fast - how do we fix this? Did they compile the procedure in a wrong way. Thank you.-solai |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-19 : 14:05:32
|
| "it rans pretty fast"How fast? If we are taking a few minutes it probably isn't that Sproc that's causing the delay.I reckon you'll need to give us quite a lot more detail before we can help."I put measurements and nailed down to the problematic storedprocedure ..."Can you explain the "measurements" process pls?Kristen |
 |
|
|
solai
Starting Member
6 Posts |
Posted - 2006-05-19 : 14:19:12
|
| All measurements are simply "PRINT GETDATE()"the procedure runs for 27 minutes, but the code in it only runs for 1.2 minute. Can't understand.Thank you.-solai |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-20 : 01:51:20
|
| Can you post the code you used in the Procedure?MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-20 : 07:01:48
|
| "the procedure runs for 27 minutes, but the code in it only runs for 1.2 minute"So what sort of tasks are happening during the other 25.8 minutes?Kristen |
|
|
|
Hot Rats
Starting Member
1 Post |
Posted - 2006-05-30 : 02:36:14
|
| I think any description of this problem must consider and describe (in addition to T-SQL):1. The two execution plans that were being used: One plan for that ad-hoc QA run and the other/different plan that was in effect (i.e., cached) at the time of that test.2. The columns' data types versus the parameters' data types (i.e., http://search.msdn.microsoft.com/search/default.aspx?__VIEWSTATE=&query=%22data+type+precedence%22&siteid=0&tab=0), the available indexes and statistics (and their relative accuracy at the time when the two executions occurred)3. The effects of concurrency (i.e., blocking, http://support.microsoft.com/kb/224453/en-us). One possibility (out of hundreds to thousands) is parameter sniffing: * http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspxApart from the DDL (column definitions), such required descriptive data can be collected via pssdiag (or other tool$):* http://support.microsoft.com/kb/830232/en-usand pssdiag's trace's analyzed via read80trace:* http://support.microsoft.com/kb/887057/en-usBoth of which are very useful for all scalability tests (as are those other tool$), as long as one doesn't go overboard (such as attempting to collect the excessively chatty and usually useless Lock events).Another possibility:Applying a function to a column is going to force a table scan (thus prohibiting a desirable index seek), which may be unnoticeable under light data loads, but can nevertheless become onerous once loads are scaled up.... |
|
|
|
|
|
|
|
|
Stored procedure is Slow