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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-10-10 : 07:50:09
|
| Richard writes "I have a really interesting question.. We have built and application which is a Marketplace in asp.net C# and MS SQL 2000. There is a stored procedure that when my asp.net calls it using a sql datareader it takes over 11 seconds. The stored procedure call is exec pr_Spotlight @Category = '6' as indicated by the SQL Profiler.. The output from the profiler is below as you can see it took 11513ms and had 2,604,663 reads.RPC:Completed exec pr_Spotlight @Category = '6' .Net SqlClient Data Provider iako_wp 11406 2604663 0 11513 0 63 2005-10-07 21:23:19.390 -----If I run the exact same argument from the SQL Query analyzer exec pr_Spotlight @Category = '6' it runs noticibly faster see below:Table 'ItemCategories'. Scan count 4, logical reads 5794, physical reads 0, read-ahead reads 0.Table 'Items'. Scan count 4, logical reads 93666, physical reads 0, read-ahead reads 0.SQL Server Execution Times: CPU time = 7985 ms, elapsed time = 3570 ms.I tried it several times to ensure it was not a first hit compile situation and the same results occur everytime.. Can you tell me why?Thank you..Richard Ettingerrichard@iids.comShould I post this to the forums or just here?" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-10-10 : 08:39:33
|
| Parameter sniffing?....search here for an explanation of the term and it's effects and suggested solutions to avoid it. In effect you're being hit with a bad execution plan. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-10-10 : 08:55:09
|
| Andrew,Since the search has been feebly at best lately, and since it timed out, I'd be very curious as well.I'll go google the term.....But a simple description would be fine.I've seen various causes that make for a bad plan though....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2006-10-12 : 13:35:02
|
| I was dealing with a similar issue. Here is something that helped.http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/7821072440eefb0/aec98a7789621cf9%23aec98a7789621cf9- Eric |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-12 : 13:59:42
|
| I don't think this is parameter sniffing case!Parameter sniffing makes bad plan when an SP is executed for the first time with the parameter value which is rarely used. So the execution plan is based on the parameter value provided which may not be optimal for other values which are frequently supplied.But in Richard case, he sends same parameter value either from ASP.Net or SQL QA.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-10-12 : 14:40:08
|
| Is @Category a numeric data type or a string type (INT vs VARCHAR)?Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-12 : 15:33:55
|
| Can you also post the SQL Profiler info for when you run it from QA. The numbers reported by SQL Profiler and stats io are notoriously different so it would be interesting to see just how different the two SQL Profiler sets of stats are. |
|
|
|
|
|
|
|
|
Tough Performace question