| Author |
Topic |
|
markymark77
Starting Member
7 Posts |
 Posted - 2007-03-28 : 11:04:32
|
| Hi,We have recently migrated from SQL 2000 to SQL 2005, and as part of this we rebuilt all indexes and updated all statistics after setting the compatibility level to 90.I would have expected performance to be at least as good as it was on 2000, however a particular SP has gone from taking approximately 6 seconds on 2000 to 70+ seconds on 2005 when called through our application, but the exact same call using SSMS with identical parameters (as proven with SQL Profiler) takes around 200ms to complete.Also, when comparing the two calls in Profiler, the following differences in statistics are displayed:Application CallCPU 68578Reads 15712877Duration 70019SSMS CallCPU 125Reads 650Duration 223I'd be grateful if anyone's got any ideas as to why this SP should perform so badly when called through our application? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-28 : 11:10:51
|
| What kind of application?Peter LarssonHelsingborg, Sweden |
 |
|
|
markymark77
Starting Member
7 Posts |
Posted - 2007-03-28 : 11:14:19
|
| Hi Peter,It's an application we have developed using .NET 2003 with framework 1.1 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-28 : 11:23:17
|
| Desktop application then? Not a web application?Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-28 : 11:27:52
|
| Are we seeing "Parameter Sniffing" problem again?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
markymark77
Starting Member
7 Posts |
Posted - 2007-03-28 : 11:29:10
|
| Yes it is. If it helps it's been in production on a SQL 2000 Enterprise server since September without any performance issues after a tuning process we carried out during trials. |
|
|
|
markymark77
Starting Member
7 Posts |
Posted - 2007-03-28 : 11:30:38
|
| I am sorry Harsh, "Parameter Sniffing" is not a concept I am familiar with. Could you explain a little please?TIA |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-28 : 11:40:39
|
| "Parameter sniffing"....do a search here for details (using that phrase) of the problem and proposed solutions for same. |
|
|
|
markymark77
Starting Member
7 Posts |
Posted - 2007-03-28 : 12:04:32
|
| Ok, have read up a little on parameter sniffing and from what I can infer it doesn't sound like its the cause of this problem. One site mentioned that re-compiling the SP would cure the problem if it was parameter sniffing, and I have already tried this in vain.I've re-run profiler to see the xml execution plan, and this is different to that which I see in SSMS to the extent that the optimizer has chosen different indexes. |
|
|
|
markymark77
Starting Member
7 Posts |
Posted - 2007-03-29 : 05:10:43
|
| Further information on this. I didn't mention earlier that the SP is being called from Crystal Reports. However, to rule this out as the cause we have developed a test procedure that simply fires this SP with the code I've copied from Profiler, and the results are the same as when it is called from Crystal.Is there any logical reason why the optimizer would use a different (poor!) query plan when calling an SP from .NET as opposed to SSMS?TIA Guys |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-29 : 08:01:52
|
| None specific...but the "optimiser" does make strange choices sometimes.You can direct the optimiser to use a particular plan/index by using the HINT clause. It's not a solution to be used in all cases....but it's worth evaluating. |
|
|
|
markymark77
Starting Member
7 Posts |
Posted - 2007-03-29 : 08:03:47
|
| Thanks Andrew. I think we've got to the bottom of it now. It appears it's all down to parameter sniffing as using local variables has cured the problem in all procedures we've investigated so far! |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-03-29 : 08:05:08
|
| The optimiser will use a different plan for each different combination of several settings such as arithabort and ansi nulls the calling application sets. As you can imagine, whether or not NULL = NULL will affect what is considered the optimal plan. If you check the cache there are probably two execution plans, one for the client and one for SSMS. You should also be able to observe the explicit setting of these in the profiler trace. *If* you have defo recompiled the sproc on every run for both the client and SSMS and still *always* get poor performance for the .NET app then it may be that one of these setting changes is actually affecting the results that are returned (again ansi null settings can drastically change the volume of results returned).HTH |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-03-29 : 08:07:21
|
too slow  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-02 : 03:49:46
|
"It appears it's all down to parameter sniffing ..."Might be mismatch of datatypes in the WHERE clause (which using a local parameter may be "masking"  )Kristen |
|
|
|
|
SP Performance Difference