Possible Duplicate:
sql runs fast in ssms slow in asp.net
Using SQL Server 2005, there is a sql query on a few big tables and it times out in the program. However, when I run it in Query Analyzer, it finishes in about 30 seconds.
Why the difference?
Asked
Active
Viewed 1.1k times
3
Aaron Bertrand
- 272,866
- 37
- 466
- 490
Fred
- 1,234
- 3
- 11
- 22
-
you mean Management Studio, right, not Query Analyzer? – Aaron Bertrand Sep 18 '12 at 18:48
1 Answers
9
Usually these are SET setting differences, leading to different plans. In Management Studio the procedure is probably running with the same settings as were in place when it was created.
Take a look at sys.dm_exec_sessions for your ASP.Net application and for your SSMS session. I will hazard a guess that at least one of your SET settings is different. This can contribute to different plans (ultimately this gets attributed to parameter sniffing) and the app side usually ends up worse off.
See these other questions for a lot more details:
- Stored procedure slow when called from web, fast from Management Studio
- Procedure times out from ADO.NET but not in SSMS
- Query times out when executed from web, but super-fast when executed from SSMS
- ADO .NET vs. SQL Server Management Studio - ADO performs worse
- sql runs fast in ssms slow in asp.net
And also read Erland Sommarskog's article, Slow in the Application, Fast in SSMS?
Community
- 1
- 1
Aaron Bertrand
- 272,866
- 37
- 466
- 490
-
We had this issue and verified, parameters were same in both .net and ssms. I even verified that slowness running is exactly on select command inside the procedure. running in SSMS took about 2 second (15records out of 5mil records) and in .NET took 56 seconds. rebuild indexes didn't make any difference either. I added an index to a random column and .NET exec time dropped to 200ms. removed the same index again and exec time still stayed on 200ms. I call it SQL voodoo magic. if you know why, appreciate if you could share – AaA Sep 11 '21 at 11:59
-
@AaA You had different plans based on one or more of the settings mentioned in the article, which allowed different plans to be compiled _at different times_ when stats were likely different (and I would guess the .NET app had a plan based on older, outdated stats). When you created the index (and dropped it), this invalidated existing plans, and you got new plans based on current stats, which were better. There is no voodoo or magic. :-) – Aaron Bertrand Sep 11 '21 at 12:31
-
Hmm, are these plans persisting? this issue was happening for few days we restarted both app server an sql server multiple times. since there was no change in the database since it started 2 years ago (except normal CRUD), and performance degraded overtime I doubt it had anything to do with plans. but as your suggestion, I would love to try it. how do I reset these plans to current? – AaA Sep 18 '21 at 05:01