Execution Plan related

When you enter a stored procedure with CREATE PROCEDURE ..., SQL Server verifies that the code is syntactically correct, ... However, at this point SQL Server does not build any query plan, but merely stores the query text in the database
It is not until a user executes the procedure, that SQL Server creates the plan

[If SQL can't perform parameter-sniffing,] it applies a standard assumption, which for an inequality operation such as > is a 30 % hit-rate

Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application

Occasionally, I see people in the forums or the newsgroups that tell me that their stored procedure is slow, but when they run the same query outside the procedure it's fast. After a few posts in the thread, the truth is revealed: the query they are struggling with refer to variables, be that local variables or parameters. To troubleshoot the query on its own, they have replaced the variables with constants. But as we have seen, the resulting stand-alone query is quite different, and SQL Server can make more accurate estimates with constants instead of variables, and therefore arrive at a better plan. Furthermore, SQL Server does not have to consider that the constant may have a different value next time the query is run.

What always works [to troubleshoot a query] is to embed the query in sp_executesql:
EXEC sp_executesql N'-- Some query that refers to @par1', N'@par1 int', <value_of_@par1>

one possible reason that the procedure ran slow in the application was simply a matter of blocking. If you find that no matter how you run the procedure in SSMS, with or without ARITHABORT, the procedure is always fast, blocking is starting to seem a likely explanation.

...

Source:
Slow in the Application, Fast in SSMS?
www.sommarskog.se/query-plan-mysteries.html

Related article:
Analyzing the SQL Server Plan Cache
www.mssqltips.com/sqlservertip/1661/analyzing-the-sql-server-plan-cache/


Examining Query Execution Plans
http://sqlserverpedia.com/wiki/Examining_Query_Execution_Plans

SQL Server Query Execution Plan Analysis
www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx

SQLIndex (what is "bookmark lookup)
www.mibuso.com/forum/viewtopic.php?f=34&t=36790&view=previous

Clearing Cache for SQL Server Performance Testing
www.mssqltips.com/tip.asp?tip=1360